Zero-Copy Arrow Handoff to GeoPandas

The fastest way to turn a DuckDB Spatial result into a GeoDataFrame is to keep geometry as WKB bytes across Apache Arrow and rebuild it once with GeoSeries.from_wkb, never letting a coordinate touch a Python string. This walkthrough sits under Arrow interop patterns for DuckDB Spatial and isolates one bottleneck: the WKT round-trip that con.df() quietly forces on geometry, and how a WKB-over-Arrow path removes it — plus the one thing WKB does not carry, the CRS, which you must re-attach by hand.

Root-Cause Analysis of the Handoff Bottleneck #

The slow path is seductive because it looks clean: con.df() returns a pandas DataFrame, and geometry appears as a column you can hand to GeoPandas. The cost is hidden in how that column is produced. Three distinct failure modes make the naive handoff slow:

• WKT stringification. To land geometry in a pandas object column, the common pattern projects ST_AsText(geom), so every geometry is serialized to a Well-Known Text string in the engine, allocated as a Python str, then re-parsed by GeoPandas. That is two full serialization passes and a per-row Python object for millions of rows — text is roughly double the bytes of WKB and far slower to parse.
• Per-row object materialization. con.df() with a geometry column materializes each geometry as a boxed Python object, so the “vectorized” DuckDB result decays into a Python list the moment it crosses. The columnar buffer that made the query fast is gone.
• A redundant pandas copy. Even when geometry is handled correctly, routing through con.df() builds a pandas frame that GeoPandas then copies again to attach its geometry accessor. The Arrow path lets GeoPandas consume the buffer directly.

The fix for all three is the same: project geometry as WKB binary (ST_AsWKB), move it as an Arrow binary column, and let GeoPandas rebuild the whole column in one vectorized call. The encoding-boundary reasoning behind that choice is covered in the parent Arrow interop patterns guide.

Deterministic Configuration #

This page needs only enough session setup to make the export reproducible: a memory ceiling so the Arrow table and DuckDB’s buffers coexist, and threads pinned so the WKB encode parallelizes predictably.

import duckdb

con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

# Cap DuckDB's working set BELOW total RAM. The Arrow export allocates a second
# buffer on top, so headroom here is what keeps a large fetch from OOM-killing Python.
con.execute("SET memory_limit = '6GB';")

# Pin to physical cores so the ST_AsWKB projection encodes in parallel; hyperthread
# oversubscription just contends for memory bandwidth during the encode.
con.execute("SET threads = 8;")

# Drop insertion-order guarantees so the scan and WKB projection run out of order;
# re-impose determinism with an ORDER BY on the final result if row order matters.
con.execute("SET preserve_insertion_order = false;")

Optimized Execution Pattern #

The behavioral change is entirely in how geometry leaves the engine. The before form stringifies to WKT and round-trips through pandas objects; the after form keeps geometry as WKB bytes over Arrow and rebuilds it vectorized.

# BEFORE — WKT round-trip: geometry serialized to text, boxed as Python str,
# re-parsed by GeoPandas. Two serialization passes + per-row objects.
import geopandas as gpd
from shapely import wkt

df = con.execute("""
    SELECT id, name, ST_AsText(geom) AS geom_wkt   -- text: ~2x bytes, slow parse
    FROM parcels
""").df()
df["geometry"] = df["geom_wkt"].apply(wkt.loads)    # per-row Python call
gdf = gpd.GeoDataFrame(df, geometry="geometry", crs="EPSG:3857")

# AFTER — WKB over Arrow: geometry stays binary across the C Data Interface and is
# rebuilt in ONE vectorized call. No text, no per-row apply, no boxed objects.
import geopandas as gpd
from geopandas import GeoSeries

tbl = con.execute("""
    SELECT id, name, ST_AsWKB(geom) AS geom_wkb     -- binary: compact, fast decode
    FROM parcels
""").arrow()                                         # zero-copy pyarrow.Table

# Rebuild every geometry at once; from_wkb is vectorized over the whole column.
geometry = GeoSeries.from_wkb(tbl.column("geom_wkb").to_pandas(), crs="EPSG:3857")
gdf = gpd.GeoDataFrame(
    tbl.drop(["geom_wkb"]).to_pandas(),              # attribute columns, one copy
    geometry=geometry,
)

The annotated difference: the apply(wkt.loads) in the before path is a Python function call per row, and ST_AsText is a heavier kernel than ST_AsWKB that also produces larger output. The after path replaces both with a single from_wkb over the contiguous Arrow buffer, and geometry never becomes a Python object until GeoPandas’ own optimized constructor runs. On a several-million-row parcel layer this is routinely several times faster and roughly halves peak memory, because WKB is both smaller on the wire and free of the intermediate string allocations.

Re-attaching the CRS explicitly #

Arrow WKB carries geometry bytes and nothing else — no SRID, no PROJ string. If you omit crs= on from_wkb, the GeoDataFrame lands with crs=None, and the first reprojection or distance call is silently in the wrong units. Capture the SRID from the engine and pass it through:

# Read the source SRID from DuckDB so the label matches the actual coordinates.
srid = con.execute("SELECT DISTINCT ST_SRID(geom) FROM parcels").fetchone()[0]

geometry = GeoSeries.from_wkb(tbl.column("geom_wkb").to_pandas(), crs=f"EPSG:{srid}")

Because the CRS is set at construction rather than inferred, the result is deterministic. The wider set of drift symptoms — a None CRS, a mismatched axis order, or a silent EPSG mislabel — and their fixes are catalogued in fixing CRS drift in GeoDataFrame conversion.

Diagnostic Queries & Plan Validation #

Confirm the export is a single vectorized encode, not a per-row fallback, before trusting a benchmark:

-- The ST_AsWKB projection should be one vectorized PROJECTION over a TABLE_SCAN.
EXPLAIN ANALYZE
SELECT id, ST_AsWKB(geom) AS geom_wkb FROM parcels;

Read three things from the output:

• No ROW_EXECUTION. A row-at-a-time marker on the projection means the WKB encode is bypassing SIMD kernels — usually a scalar Python UDF or an implicit GEOMETRY → VARCHAR cast in the select list. Remove it so ST_AsWKB runs as a vectorized kernel.
• ST_AsWKB, not ST_AsText. If the plan shows ST_AsText you are still on the slow path; text output is larger and parses slower on the Python side regardless of how the transfer is wired.
• Projection width. The scan should read only the columns the query names. A SELECT * that drags unused wide columns across Arrow inflates memory before geometry is even rebuilt.

Measure copies and memory on the Python side directly rather than guessing:

import tracemalloc

tracemalloc.start()
tbl = con.execute("SELECT id, ST_AsWKB(geom) AS geom_wkb FROM parcels").arrow()
geometry = GeoSeries.from_wkb(tbl.column("geom_wkb").to_pandas())
_, peak = tracemalloc.get_traced_memory()
tracemalloc.stop()
print(f"peak Python heap during rebuild: {peak / 1e6:.0f} MB")

A peak far above the raw WKB byte size means an extra materialization slipped in — most often an accidental .df() before from_wkb, or copying the attribute columns twice. The plan-capture discipline for wiring this check into CI carries over from the converting DuckDB queries to a GeoDataFrame efficiently guide.

Geometry Validation & Fallback Routing #

WKB will faithfully encode an invalid geometry — a self-intersecting ring crosses the boundary intact and only errors later inside GeoPandas. Gate the export so invalid geometry is repaired or quarantined before it becomes bytes:

-- Repair in the engine so only valid geometry reaches from_wkb.
SELECT id, ST_AsWKB(ST_MakeValid(geom)) AS geom_wkb
FROM parcels
WHERE ST_IsValid(geom) OR ST_IsValid(ST_MakeValid(geom));

Null geometry is the other edge: ST_AsWKB(NULL) becomes a null Arrow slot, and from_wkb yields a missing geometry. That is usually acceptable, but decide explicitly — filter nulls out at the boundary, or substitute an empty geometry, rather than letting a None propagate into a spatial operation downstream. Treating the null-handling and validity gates as part of the SQL projection, rather than as post-hoc GeoPandas cleanup, keeps the entire repair vectorized inside the engine and out of per-row Python.

When a single fetch is too large for the Python heap even as WKB, do not raise memory_limit until the export fits — stream instead. Fetching one batch at a time and rebuilding each shard keeps peak memory flat regardless of total row count:

# Chunked fallback: rebuild geometry shard-by-shard so peak memory stays bounded.
reader = con.execute(
    "SELECT id, ST_AsWKB(geom) AS geom_wkb FROM parcels"
).fetch_record_batch(rows_per_batch=100_000)

for batch in reader:
    geoms = GeoSeries.from_wkb(batch.column("geom_wkb").to_pandas(), crs="EPSG:3857")
    handle(geoms)   # write a shard, append to a store, buffer downstream

This is the same WKB-over-Arrow handoff applied one batch at a time; the streaming reader backpressures DuckDB so a result larger than RAM never materializes whole.

If even the per-batch rebuild spikes on a dense polygon layer, the culprit is almost always geometry width rather than row count — a single multipolygon can carry megabytes of coordinates. Size rows_per_batch by bytes, dropping it an order of magnitude for wide geometry so each shard’s WKB stays within the per-iteration ceiling. The concatenation of shards back into one GeoDataFrame, when the pipeline needs a single frame, is cheaper done once at the end with pandas.concat on already-rebuilt GeoSeries than by re-parsing, keeping the whole path free of any WKT round-trip end to end.

Related #

See also

• DuckDB-to-GeoPandas sync — the full transfer boundary this narrow handoff is one path through, including converting DuckDB queries to a GeoDataFrame efficiently.

Up: Arrow interop patterns for DuckDB Spatial · Python & DuckDB Integration Workflows