Streaming Overture Maps Data with DuckDB

Overture Maps publishes global buildings, places, and transportation as GeoParquet on public cloud storage — datasets far too large to download whole. This guide uses DuckDB to stream only the rows inside a bounding box straight from the remote files into a GeoDataFrame, for anyone who needs authoritative global features for one city without ingesting the planet. It sits under Cloud-Native Geospatial Formats in Geospatial Data Ingestion & Processing Workflows.

Predicate pushdown against remote Overture GeoParquet Overture GeoParquet sits on S3 as a stack of row groups, each carrying bounding-box statistics. DuckDB's httpfs and spatial extensions compare the query bbox against those stats and issue HTTP range requests for only the row groups that overlap the area of interest; the non-matching groups are skipped entirely. The small matching slice is decoded into a WGS84 GeoDataFrame, so only a fraction of the bytes ever travel. Push the filter to the data: only matching row groups travel Remote GeoParquet · S3 row groups carry bbox statistics row group 01 · skipped row group 02 · skipped row group 03 · overlaps AOI row group 04 · overlaps AOI row group 05 · skipped row group 06 · skipped HTTP range requests DuckDB httpfs remote range reads spatial · bbox filter row-group skipping decode WKB GeoDataFrame AOI only EPSG:4326 a fraction of the bytes
Row-group bounding-box statistics let DuckDB range-request only the slices of the remote GeoParquet that overlap the area of interest.

Why This Approach / What Goes Wrong

The Overture building layer is hundreds of gigabytes of GeoParquet. Downloading it to filter locally is absurd; the cloud-native move is to push the filter to the data. DuckDB with the httpfs and spatial extensions reads the remote Parquet over HTTP range requests and, thanks to row-group bounding-box statistics, skips the vast majority of the file — so a city-sized query transfers a small fraction of the bytes. This is the same predicate-pushdown mechanism behind querying GeoParquet with DuckDB Spatial, pointed at a remote URL instead of a local file.

How an Overture release is laid out. A release is a hive-partitioned directory tree — release/<version>/theme=<theme>/type=<type>/*.parquet — where each type holds tens to hundreds of part files of a few hundred megabytes each. The themes (addresses, base, buildings, divisions, places, transportation) are versioned together, and the version string is baked into the path; there is no "latest" alias. Pinning that version is the only way a query written last quarter still returns the same rows, because Overture renames themes and columns between releases. The glob is resolved by an object-store listing, so a query against theme=buildings/type=building/* costs one LIST plus one footer read per part file before any data moves.

Why the filter must touch bbox, not geometry. Parquet prunes row groups using the per-column min/max statistics in the file footer. Geometry is a WKB blob, and the min/max of a binary column is a lexicographic byte comparison — spatially meaningless, so no row group can ever be excluded by inspecting it. Overture works around this by materialising each feature's envelope as four ordinary floating-point columns inside a bbox struct. Those columns do carry useful min/max statistics, so a predicate over bbox.xmin is answerable from the footer alone and DuckDB discards non-overlapping groups before fetching a single byte of geometry. GeoParquet 1.1 standardises the same trick as a declared covering column, but a reader only exploits it when the writer declared it — with Overture you get the behaviour by naming the struct fields yourself.

The failure modes follow from that: a forgotten extension, a bounding-box filter on the wrong column, a BETWEEN corner test that silently under-selects, a stale field name after a release bump, and trying to materialize a continent-scale result in Python because the spatial predicate was too loose.

Symptom, root cause, and fix for three streamed-query failures Three rows map a symptom to its root cause and a one-line fix. A row count in the millions means the filter tested the geometry column instead of the bbox struct, so no row group could be skipped; filter on bbox.xmin instead. A no registered file system error means the httpfs extension was not loaded in this session; load it every session. Stalling or throttled reads mean the client is reading across regions; set the s3 region to match the bucket. Three ways a streamed Overture query goes wrong Symptom Root cause One-line fix Row count in the millions Filter tested geometry, not the bbox struct — no group skipping Filter the bbox struct: bbox.xmin BETWEEN x0 AND x1 DuckDB raises no registered file system The httpfs extension was not loaded in this session Load it every session: con.load_extension('httpfs') Reads stall or throttle The client is reading across regions from the bucket Match the bucket region: SET s3_region='us-west-2' The first two defeat pushdown: a predicate that cannot be answered from row-group statistics forces every group to be read.
Each failure mode has a single diagnostic signature, so the symptom alone tells you which of the three to fix.

Prerequisites

pip install "duckdb>=1.0" "geopandas>=0.14" "shapely>=2.0"

DuckDB 0.10 and later autoload known extensions when autoload_known_extensions is on, so httpfs often works without a line of setup — but spatial is a community-adjacent extension whose autoload behaviour changed across releases, and CI images frequently run with autoloading disabled. Explicit INSTALL/LOAD is the portable form and costs nothing when the extension is already resident. If you are behind a proxy or on an air-gapped runner, pre-fetch the extension binaries once with duckdb -c "INSTALL httpfs; INSTALL spatial;" into the image; the extension directory (~/.duckdb/extensions/<version>/<platform>/) is version-and-platform specific, so it must be rebuilt whenever you bump DuckDB.

Step-by-Step Implementation

1. Connect, load the extensions, and set the session budget. The region setting is the one that decides whether reads run at line rate or crawl; the memory and spill settings decide whether an over-broad query fails fast or thrashes.

import duckdb

con = duckdb.connect()
for ext in ("httpfs", "spatial"):
    con.install_extension(ext)
    con.load_extension(ext)

con.execute("""
    SET s3_region='us-west-2';               -- Overture's bucket region
    SET enable_http_metadata_cache=true;     -- reuse Parquet footers across queries
    SET memory_limit='8GB';                  -- fail loudly instead of swapping
    SET temp_directory='/tmp/duckdb_spill';  -- spill target once memory_limit is hit
    SET threads=4;                           -- each thread opens its own HTTP connection
""")

More threads is not always faster here. Every worker holds an open connection and its own read-ahead buffer, so on a laptop link eight threads mostly buy contention and a higher chance of the object store throttling you; four is a good starting point, and only worth raising when the client sits in the same cloud region as the bucket.

2. Inspect the release schema before you write the query. DESCRIBE reads one file's footer — kilobytes — and tells you exactly what the column paths are in this release rather than in a blog post from two releases ago.

release = "s3://overturemaps-us-west-2/release/2024-09-18.0"
buildings_glob = f"{release}/theme=buildings/type=building/*"

schema = con.sql(f"DESCRIBE SELECT * FROM read_parquet('{buildings_glob}')").df()
print(schema[["column_name", "column_type"]].head(8).to_string(index=False))
# column_name                                          column_type
#          id                                              VARCHAR
#        bbox   STRUCT(xmin FLOAT, xmax FLOAT, ymin FLOAT, ymax FLOAT)
#    geometry                                             GEOMETRY
#       names          STRUCT(primary VARCHAR, common MAP(...), ...)
#      height                                               DOUBLE

Two schema changes bite people repeatedly. The bbox struct fields were named minx/maxx/miny/maxy in earlier releases and xmin/xmax/ymin/ymax in later ones, so a filter copied from an older recipe fails with Referenced column "xmin" not found. And the administrative theme was renamed from theme=admins to theme=divisions, with type=locality becoming type=division and type=division_area — a path that simply returns zero files rather than an error, which is why an empty result should always send you back to DESCRIBE before you touch the predicate.

3. Filter on the bbox struct for row-group pushdown. Use a tight area of interest so the result stays city-sized, and express the test as a true envelope overlap rather than a corner containment.

# Berlin-Mitte bounding box in EPSG:4326 (lon/lat, WGS84 axis order)
xmin, ymin, xmax, ymax = 13.36, 52.50, 13.43, 52.54

query = f"""
SELECT id, names.primary AS name, height, ST_AsWKB(geometry) AS wkb
FROM read_parquet('{buildings_glob}', filename=true, hive_partitioning=1)
WHERE bbox.xmin <= {xmax} AND bbox.xmax >= {xmin}
  AND bbox.ymin <= {ymax} AND bbox.ymax >= {ymin}
"""
buildings_df = con.sql(query).df()

The four-way inequality matters. The shorter bbox.xmin BETWEEN xmin AND xmax form tests whether the feature's lower-left corner falls inside the window, so any building that straddles the western or southern edge of the area of interest is dropped — a quiet under-selection that shows up much later as a hole along one side of the study area. The four comparisons above are the standard rectangle-overlap test, and because each one is still a plain inequality on a numeric column, DuckDB can answer all four from row-group statistics and skip exactly as aggressively.

Column chunks inside one Overture row group A single Parquet row group drawn as five column chunks sized in proportion to their bytes: id at 0.4 megabytes, names.primary at 1.1, height at 0.2, the four bbox doubles at 0.6, and the geometry column of well-known binary polygons at 58 megabytes, roughly ninety-four percent of the group. Step one reads only the footer statistics for the bbox columns, a few kilobytes, to decide whether the group is opened. Step two decodes the geometry column only for groups that survive that test, so most groups never leave the bucket. Inside one row group: what the bbox filter actually reads id 0.4 MB names.primary 1.1 MB height 0.2 MB bbox xmin ymin xmax ymax 0.6 MB 1 geometry WKB polygons — 94% of the row group 58 MB 2 1 · read the footer stats for bbox kilobytes decide whether the group opens 2 · decode geometry only if it survives most groups never leave the bucket Column chunks are independent, so testing four doubles never touches the WKB blob beside them.
Filtering on the bbox struct works because those four numeric columns are stored — and summarised — separately from the geometry blob they describe.

4. Rebuild a GeoDataFrame with an explicit CRS. Overture geometry is stored in WGS84, so tag it as EPSG:4326 — see Coordinate Systems with PyProj for why the CRS must travel with the geometry.

import geopandas as gpd

buildings = gpd.GeoDataFrame(
    buildings_df.drop(columns="wkb"),
    geometry=gpd.GeoSeries.from_wkb(buildings_df["wkb"]),
    crs="EPSG:4326",     # Overture geometry is WGS84 lon/lat
)

5. Clip to a real administrative boundary instead of a rectangle. A rectangle is the right pushdown filter, but rarely the right analytical extent. Pull the boundary polygon from the divisions theme with the same bbox trick, then intersect locally — the rectangle does the cheap byte-level pruning, the polygon does the exact selection.

divisions_glob = f"{release}/theme=divisions/type=division_area/*"

boundary_df = con.sql(f"""
    SELECT names.primary AS name, ST_AsWKB(geometry) AS wkb
    FROM read_parquet('{divisions_glob}')
    WHERE subtype = 'locality'
      AND bbox.xmin <= {xmax} AND bbox.xmax >= {xmin}
      AND bbox.ymin <= {ymax} AND bbox.ymax >= {ymin}
""").df()

boundaries = gpd.GeoDataFrame(
    boundary_df.drop(columns="wkb"),
    geometry=gpd.GeoSeries.from_wkb(boundary_df["wkb"]),
    crs="EPSG:4326",
)
mitte = boundaries.loc[boundaries["name"] == "Mitte", "geometry"].union_all()
buildings_in_mitte = buildings[buildings.intersects(mitte)]

union_all() is the GeoPandas 1.0 spelling; on geopandas<1.0 the method is unary_union, and calling the wrong one raises AttributeError rather than misbehaving. The same boundary-membership pattern generalises to reverse geocoding points to administrative boundaries when you need the containing division per feature rather than a single clip.

6. Stream straight to disk when the area of interest is larger than memory. A metropolitan-region query can return several million rows, and con.sql(...).df() materialises all of them in Python before you can write anything. COPY … TO keeps the whole result inside DuckDB's streaming pipeline, so peak memory tracks one batch rather than the full answer.

con.execute("SET preserve_insertion_order=false")   # lets the writer flush batches early

con.execute(f"""
    COPY (
        SELECT id, names.primary AS name, height, geometry
        FROM read_parquet('{buildings_glob}')
        WHERE bbox.xmin <= 13.76 AND bbox.xmax >= 13.09
          AND bbox.ymin <= 52.68 AND bbox.ymax >= 52.34
    ) TO 'berlin_buildings.parquet'
    (FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 100000)
""")

preserve_insertion_order=false is the single most effective setting for large exports — with ordering preserved DuckDB must buffer results to reassemble the original row sequence, which for a multi-gigabyte extract means holding most of it at once. Row order is meaningless for a spatial extract, so give it up.

7. Persist a small area of interest as GeoParquet for repeated analysis so you only pay the network cost once.

buildings_in_mitte.to_parquet("berlin_mitte_buildings.parquet", compression="zstd")

Verification

Confirm only the AOI came back and the geometry decoded correctly.

print("Buildings fetched:", len(buildings))     # Buildings fetched: 9241
assert buildings.crs.to_epsg() == 4326

minx, miny, maxx, maxy = buildings.total_bounds
assert minx < 13.43 and maxx > 13.36, "No overlap with the AOI — check the bbox filter"
assert buildings.geometry.is_valid.mean() > 0.99
print(f"Extent: {minx:.3f},{miny:.3f} → {maxx:.3f},{maxy:.3f}")
# Extent: 13.359,52.499 → 13.431,52.541

The extent runs marginally outside the requested rectangle, and that is correct: the filter selects features whose envelope overlaps the window, so a building straddling the edge comes back whole. An extent that matches the request to three decimals is the suspicious case — it usually means a BETWEEN corner test clipped the edges away.

To prove the pushdown actually happened rather than assuming it, profile the query and compare rows scanned against rows returned:

con.execute("SET enable_profiling='query_tree'")
con.execute("SET profiling_output='/tmp/overture_profile.txt'")
con.sql(query).df()
con.execute("SET enable_profiling='no_output'")
# In the profile, the PARQUET_SCAN node reports the row count that survived
# row-group pruning. On a working bbox filter that number is in the tens of
# thousands; if it matches the full theme's row count, no group was skipped.

If the row count is in the millions, the bbox filter didn't apply — verify the column path and that you queried the bbox struct, not the geometry.

Edge Cases & Debugging

Frequently Asked Questions

Should I stream from Overture on every run, or extract once and cache? Cache, unless the pipeline genuinely needs the newest release. A streamed query re-reads footers, re-lists the prefix, and re-transfers the matching row groups every time; a local GeoParquet extract of one city is usually tens of megabytes and reads in milliseconds. Stream when you are exploring, when the area of interest changes per run, or when you want the extract itself to be reproducible from a pinned release string. Cache once the area of interest is fixed.

How large an area of interest can this handle before it stops scaling? A city fits comfortably in memory; a metropolitan region needs the COPY … TO streaming form; a country is where the model breaks down, because the bbox filter stops pruning — once your rectangle overlaps most row groups there is nothing left to skip and you are simply downloading the theme through a SQL engine. At that point pull the whole theme once with a proper transfer tool, land it as partitioned GeoParquet, and run local queries against it, or move the workload to a partitioned engine as described in Scaling with Dask-GeoPandas.

Why not use ST_Intersects on the geometry column and skip the bbox entirely? Because a spatial predicate on a WKB blob cannot be answered from Parquet footer statistics, so every row group is fetched and decoded before the predicate runs. ST_Intersects is the right tool for the exact test, but it must come second: prune with the numeric bbox columns, refine with the geometry predicate. That ordering is what turns a several-hundred-gigabyte scan into a few tens of megabytes of transfer, and it is why the combined pattern here differs from the local-file recipe in Querying GeoParquet with DuckDB Spatial.

Should I pin the release version or always take the newest one? Pin it. The version is part of the path, so an unpinned query is not merely non-reproducible — it will eventually 404 or silently change schema when Overture retires or restructures a theme. Treat the release string as a dependency: record it alongside the code, bump it deliberately, and re-run DESCRIBE plus your verification assertions when you do.

How do I join Overture features to my own data without downloading either whole? Register the local file as a DuckDB table or read it in the same query — read_parquet accepts a mixed list of local and remote paths — and let the engine join across them. Keep the remote side filtered by bbox first so the join input is already small; joining a pruned city extract to a local parcels table runs in-process with no intermediate materialisation. The join semantics themselves are the ordinary ones covered in Spatial Joins & Merging.

Can I read the Azure mirror instead of S3? Yes, and it is worth doing if your compute runs on Azure — cross-cloud reads pay both latency and egress. Install and load the azure extension instead of configuring s3_region, then point read_parquet at the az:// container path for the same release. The query body, including the bbox predicate, is identical; only the storage prefix and the extension change.

Related