geomermaids · Querying Parquetry

Recipes for the open datasets on parquetry.geomermaids.com: OpenStreetMap for North America, GMWID world infrastructures, FAO GAUL, Corine Land Cover and FEMA flood zones. Every file is a plain URL. No account, no key, no download step: DuckDB reads only the row groups a query needs. Most examples use the OpenStreetMap snapshots; the same patterns work on every dataset.

1. One region, one theme, one URL

When you know the region and theme, name the file. DuckDB streams it over HTTPS with range requests.

Setup

Load the extensions once per session. The url() macro only keeps the examples short; a plain string URL works the same.

INSTALL httpfs; LOAD httpfs;
INSTALL spatial; LOAD spatial;

-- Coordinates are (longitude, latitude): tell the spatial extension, so
-- ST_Distance_Sphere and friends read the axes in that order.
SET geometry_always_xy = true;

CREATE OR REPLACE MACRO url(state, theme) AS
  'https://parquetry.geomermaids.com/osm/latest/country=' ||
  split_part(state, '-', 1) || '/state=' || state || '/' || theme || '.parquet';

-- The same thing without the macro:
SELECT count(*) FROM read_parquet(
  'https://parquetry.geomermaids.com/osm/latest/country=US/state=US-NY/buildings.parquet');

Buildings in a bounding box

A rectangle around Midtown Manhattan. The bbox.* clause is what makes it fast: its plain float statistics let any engine skip row groups before reading them. Each bbox is rounded outward to float32, so that clause alone returns a slight superset; st_intersects_extent makes the test exact. On engines without DuckDB's spatial functions (Spark, Trino, polars), keep the bbox.* clause and drop the last line.

SELECT count(*) AS buildings
FROM read_parquet(url('US-NY', 'buildings'))
WHERE bbox.xmin <= -73.98 AND bbox.xmax >= -73.99   -- prunes row groups
  AND bbox.ymin <= 40.76  AND bbox.ymax >= 40.75
  AND st_intersects_extent(geometry, ST_MakeEnvelope(-73.99, 40.75, -73.98, 40.76));

Tallest residential towers in a neighbourhood

Four columns out of twenty, and a spatial filter at the same time:

SELECT name, building, levels, height
FROM read_parquet(url('US-NY', 'buildings'))
WHERE building = 'residential'
  AND levels > 30
  AND bbox.xmin <= -73.95 AND bbox.xmax >= -73.99
  AND bbox.ymin <= 40.79  AND bbox.ymax >= 40.76
  AND st_intersects_extent(geometry, ST_MakeEnvelope(-73.99, 40.76, -73.95, 40.79))
ORDER BY levels DESC
LIMIT 10;

Restaurants and the buildings they sit in

A spatial join between two theme files. A bbox filter on each side keeps both reads to downtown Boston before ST_Contains runs:

SELECT b.name AS building, p.name AS restaurant, p.cuisine
FROM read_parquet(url('US-MA', 'buildings')) b
JOIN read_parquet(url('US-MA', 'pois')) p
  ON ST_Contains(b.geometry, p.geometry)
WHERE p.amenity = 'restaurant'
  AND b.name IS NOT NULL
  AND b.bbox.xmin <= -71.05 AND b.bbox.xmax >= -71.07
  AND b.bbox.ymin <= 42.36  AND b.bbox.ymax >= 42.34
  AND p.bbox.xmin <= -71.05 AND p.bbox.xmax >= -71.07
  AND p.bbox.ymin <= 42.36  AND p.bbox.ymax >= 42.34
LIMIT 10;

Nearest features to a point

Amenities within 500 m of Times Square. The bbox prefilter (about 1 km, deliberately looser than the radius) prunes row groups; ST_Distance_Sphere gives the exact cutoff in metres but, used alone, would force a full scan.

WITH origin AS (SELECT ST_Point(-73.9857, 40.7484) AS pt)
SELECT p.amenity, p.name,
       ST_Distance_Sphere(p.geometry, o.pt) AS dist_m
FROM read_parquet(url('US-NY', 'pois')) p, origin o
WHERE p.bbox.xmin <= -73.9757 AND p.bbox.xmax >= -73.9957   -- prunes row groups
  AND p.bbox.ymin <= 40.7584 AND p.bbox.ymax >= 40.7384
  AND ST_Distance_Sphere(p.geometry, o.pt) < 500            -- exact, metres
ORDER BY dist_m
LIMIT 15;

Several states at once

Pass a list of URLs and group by the state_iso column every file carries:

SELECT state_iso, count(*) AS pois
FROM read_parquet([
  url('US-NY', 'pois'), url('US-MA', 'pois'),
  url('US-CT', 'pois'), url('US-RI', 'pois')
])
GROUP BY state_iso
ORDER BY pois DESC;

GMWID: the high-voltage grid of a state

GMWID, the GeoMermaids World Infrastructures Dataset, covers the whole world in one file per layer, with voltages already parsed into kV and line lengths in km. Every row carries its country and state (a slug of the state's name), and the rows are Hilbert-sorted, so filtering on them skips most of the file:

SELECT voltage_kv, count(*) AS lines, round(sum(length_km)) AS km
FROM read_parquet('https://parquetry.geomermaids.com/gmwid/latest/power_line.parquet')
WHERE country = 'US' AND state = 'florida'
  AND lifecycle = 'active' AND voltage_kv >= 100
GROUP BY ALL
ORDER BY voltage_kv DESC;

2. Queries across the whole catalogue

"Every airport in North America" needs a wildcard across files, and a wildcard needs a directory listing, which plain HTTPS cannot give. A small read-only S3-compatible endpoint, s3.geomermaids.com, serves the same bucket with just enough of the S3 API (ListObjectsV2 and ranged GetObject) for DuckDB to expand globs. Anonymous, no signing.

Setup

Empty keys make httpfs send unsigned requests. Once per session:

INSTALL httpfs; LOAD httpfs;
INSTALL spatial; LOAD spatial;

SET s3_endpoint = 's3.geomermaids.com';
SET s3_url_style = 'path';
SET s3_use_ssl = true;
SET s3_access_key_id = ''; SET s3_secret_access_key = '';

Every aeroway feature in North America

About 100 files in one query. Row counts live in the Parquet footer, so DuckDB answers without reading a single data page:

SELECT count(*) AS features
FROM read_parquet('s3://parquetry/osm/latest/country=*/state=*/aeroways.parquet');

Top 10 states and provinces for airports

Wildcard the path, group by state_iso: the column is inside every file, no path parsing.

SELECT state_iso, count(*) AS aerodromes
FROM read_parquet('s3://parquetry/osm/latest/country=*/state=*/aeroways.parquet')
WHERE aeroway = 'aerodrome'
GROUP BY state_iso
ORDER BY aerodromes DESC
LIMIT 10;

One country only

Pin a path segment to a literal and the glob prunes at list time. Only Canadian files are read:

SELECT state_iso, count(*) AS stations
FROM read_parquet('s3://parquetry/osm/latest/country=CA/state=*/public_transport.parquet')
WHERE railway = 'station'
GROUP BY state_iso
ORDER BY stations DESC;

Wind turbines across the continent

The power theme is small, so a continent-wide scan is cheap:

SELECT country, count(*) AS turbines
FROM read_parquet('s3://parquetry/osm/latest/country=*/state=*/power.parquet')
WHERE power = 'generator'
  AND tags['generator:source'] = 'wind'
GROUP BY country
ORDER BY turbines DESC;

3. Outside DuckDB

GeoPandas

import geopandas as gpd

gdf = gpd.read_parquet(
    "https://parquetry.geomermaids.com/osm/latest/country=US/state=US-RI/buildings.parquet"
)
print(f"{len(gdf):,} Rhode Island buildings")
print(gdf[["building", "name", "height"]].head())

QGIS

QGIS 3.28 LTR or newer (GDAL 3.5 or newer with the Parquet driver). In Data Source Manager → Vector, prefix the URL with /vsicurl/ so GDAL streams it with range requests instead of downloading the whole file. QGIS opens one file at a time; for catalogue-wide questions, use DuckDB.

/vsicurl/https://parquetry.geomermaids.com/osm/latest/country=US/state=US-NY/buildings.parquet

4. Ask an AI agent

The parquetry skill packs these recipes, the layout of every dataset and the method for combining them, so an AI agent can answer a spatial question itself: What is the predominant land use in Hérault? How many buildings in Eastern Massachusetts are in the 100-year floodplain? It chooses the files, aligns the coordinate systems, runs the query, and states its definitions, coverage gaps, data dates and credits. In Claude Code:

/plugin marketplace add gsueur/parquetry-skills
/plugin install parquetry@geomermaids

The skill is plain Markdown with queries tested weekly against the live data, so any agent that can run DuckDB can use it.

Next

What each dataset holds, its URL layout and its licence are on geoparquet.geomermaids.com. How the files are written for fast reads is in the GeoParquet Writing cookbook. Need another region or a custom schema? Get in touch.