⚙️ How-To DuckDB & analytic SQL Getting started DuckDB for Spatial Data · step 11 of 20 Updated

How to Move Data Between DuckDB and GeoPandas

Problem statement

DuckDB is the better engine for the heavy part — the join, the aggregate, the scan over millions of rows. GeoPandas is the better environment for everything after that: plotting, .explore(), contextily, the shapely API, and the rest of the Python geospatial ecosystem.

So most real workflows use both, and the boundary between them needs to be cheap and lossless. Two things routinely go wrong:

  • The geometry arrives as bytes. A DuckDB GEOMETRY column comes into pandas as an opaque object, not as a GeoSeries.
  • The CRS is lost. DuckDB tracks CRS on the column type and drops it as soon as anything is computed, so a GeoDataFrame built from a query result has crs=None unless you set it.

Both are one line to fix, and both are silent when you do not.

Quick answer

Convert to WKB in SQL, load it in GeoPandas, and set the CRS explicitly:

import duckdb
import geopandas as gpd
from shapely import from_wkb

con = duckdb.connect()
con.execute("install spatial; load spatial;")

frame = con.execute("""
    select name, admin, st_aswkb(geom) as geometry
    from st_read('provinces.shp')
    where admin = 'United States of America'
""").df()

gdf = gpd.GeoDataFrame(
    frame,
    geometry=from_wkb(frame["geometry"]),
    crs="EPSG:4326",              # DuckDB will not tell you this — you must
)

Going the other way is the mirror image:

con.execute("create table areas as select * from gdf_with_wkb")

with the geometry converted to WKB in pandas first.

Flow from files through DuckDB and a WKB conversion into GeoPandas.
Each crossing costs a serialisation, so there should be exactly one.

Step-by-step solution

1. Use WKB as the interchange format

Well-Known Binary is compact, exact and understood by both sides. ST_AsWKB in DuckDB produces it; shapely.from_wkb reads it, vectorised over a whole column.

The alternatives are worse: WKT is text and larger, GeoJSON is text and larger still, and passing DuckDB's internal geometry type into pandas gives you something that is not a shapely object.

2. Set the CRS yourself, every time

DuckDB attaches the source CRS to the column type when reading a file:

select typeof(geom) from st_read('provinces.shp') limit 1;
-- GEOMETRY('EPSG:4326')

but that annotation does not travel through ST_AsWKB, and ST_Transform strips it from its own result. So the CRS is knowledge you hold, not something the pipeline carries.

Write it down as a constant next to the query. A GeoDataFrame with crs=None will silently refuse to reproject and silently mis-join.

3. Prefer Arrow for large results

.df() builds a pandas DataFrame; .arrow() returns an Arrow table with no intermediate copy. For results of millions of rows the difference is measurable, and GeoPandas reads from Arrow directly:

table = con.execute(sql).arrow()
frame = table.to_pandas()

Compare the transfer costs for a million two-column rows: JSON is 31.77 MB, Arrow with zstd 11.53 MB, raw float32 8.0 MB. The same ordering applies inside a process — Arrow moves columns, pandas rebuilds them.

4. Go the other way with a registered DataFrame

DuckDB can query a pandas DataFrame in the local scope by name, which makes pushing data in trivial:

places = gdf.to_wkb()                       # geometry column becomes bytes
result = con.execute("""
    select country, count(*) from places group by 1
""").df()

For geometry, convert first: gdf.to_wkb() returns a DataFrame whose geometry column holds WKB, which DuckDB reads with ST_GeomFromWKB.

5. Keep the split at the right place

The division that works:

  • DuckDB — reading files, filtering, joining, aggregating, anything over a million rows.
  • GeoPandas — the result, once it is small enough to hold comfortably: plotting, interactive maps, shapely operations on a few thousand features, export to formats GDAL writes well.

Measured, the heavy end justifies itself: 13,464,017 points joined to 4,596 polygons took 61.8 s and 305 MB in DuckDB against 117.5 s and 4,739 MB in GeoPandas. The light end justifies itself too: on 7,342 points GeoPandas was faster, 0.104 s against 0.168 s.

6. Do not round-trip more than once

Each crossing costs a serialisation. A pipeline that alternates — query, convert, operate, convert back, query — spends most of its time in WKB.

Decide where the boundary is, do all the heavy work on one side, cross once.

Grid comparing WKB, WKT, GeoJSON and the native geometry type as interchange formats.
shapely.from_wkb over a whole column is a single C-level loop.

Code examples

Example 1 — the two conversions, as functions 

import duckdb
import geopandas as gpd
import pandas as pd
from shapely import from_wkb


def duckdb_to_geodataframe(con, sql, crs, geometry_column="geom"):
    """Run a query and return a GeoDataFrame with the CRS you specify."""
    frame = con.execute(sql).df()
    if geometry_column not in frame.columns:
        raise KeyError(
            f"no column {geometry_column!r} in the result; "
            f"select st_aswkb({geometry_column}) as {geometry_column}")
    geometry = from_wkb(frame[geometry_column])
    frame = frame.drop(columns=[geometry_column])
    return gpd.GeoDataFrame(frame, geometry=geometry, crs=crs)


def geodataframe_to_duckdb(con, gdf, table_name, geometry_column="geometry"):
    """Register a GeoDataFrame as a DuckDB table with a real geometry column."""
    staged = gdf.to_wkb()                                  # geometry → bytes
    con.register("_staging", staged)
    con.execute(f"""
        create or replace table {table_name} as
        select * exclude {geometry_column},
               st_geomfromwkb({geometry_column}) as geom
        from _staging
    """)
    con.unregister("_staging")
    rows = con.execute(f"select count(*) from {table_name}").fetchone()[0]
    print(f"{table_name}: {rows:,} rows  (CRS {gdf.crs} — record it, "
          f"DuckDB will not keep it)")

Example 2 — the heavy-then-light pattern 

def heavy_then_light(points_parquet, polygons_path, crs="EPSG:4326", top=20):
    """DuckDB does the millions of rows; GeoPandas gets the summary."""
    con = duckdb.connect()
    con.execute("install spatial; load spatial;")
    con.execute("set enable_progress_bar = false")

    sql = f"""
        select a.name,
               count(*)               as points,
               st_aswkb(any_value(a.geom)) as geom
        from read_parquet('{points_parquet}') p
        join st_read('{polygons_path}') a
          on st_intersects(st_point(p.lon, p.lat), a.geom)
        group by a.name
        order by points desc
        limit {top}
    """
    gdf = duckdb_to_geodataframe(con, sql, crs=crs)
    print(f"{len(gdf)} areas crossed the boundary into GeoPandas")
    return gdf

any_value(a.geom) keeps one geometry per group without a second join. The whole point of the shape is that a 13-million-row input becomes a 20-row GeoDataFrame before anything is materialised in Python.

Example 3 — a round-trip test that catches silent loss 

def assert_roundtrip(con, gdf, crs=None):
    """Geometry and CRS should survive a trip through DuckDB unchanged."""
    crs = crs or gdf.crs
    geodataframe_to_duckdb(con, gdf, "_roundtrip")
    back = duckdb_to_geodataframe(
        con, "select * exclude geom, st_aswkb(geom) as geom from _roundtrip", crs=crs)

    assert len(back) == len(gdf), f"row count changed: {len(gdf)} → {len(back)}"
    assert back.crs == gdf.crs, f"CRS changed: {gdf.crs} → {back.crs}"

    same = gdf.geometry.reset_index(drop=True).geom_equals(
        back.geometry.reset_index(drop=True))
    assert same.all(), f"{(~same).sum()} geometries differ after the round trip"
    print(f"round trip clean: {len(gdf):,} features, CRS {crs}")

Run this once when you set the boundary up. WKB is lossless, so a failure means the CRS assumption is wrong or a geometry was invalid to begin with.

Explanation

Why WKB and not the native type

DuckDB's GEOMETRY is an internal representation optimised for its own operations. When it crosses into pandas it becomes an object pandas cannot interpret, and shapely cannot read it either.

WKB is the lingua franca: a documented binary encoding that both sides implement natively and vectorised. shapely.from_wkb on a whole column is a single C-level loop, which is why the conversion is fast enough not to think about.

Why the CRS has to be an explicit constant

DuckDB's CRS tracking is a type annotation, and annotations are lost by most operations — including the ST_AsWKB that gets the data out. GeoPandas needs a real CRS object to reproject, to join safely and to plot with a basemap.

The consequence is that the CRS must live in your code, next to the query, as a constant. That is not as bad as it sounds: a pipeline should know what CRS it works in, and writing it down is a documentation improvement as much as a technical necessity.

Why the direction of the split matters

Both engines can do everything. The reason to put the heavy work in DuckDB is memory: it streams, and GeoPandas materialises.

Measured on the global join, GeoPandas peaked at 4,739 MB against DuckDB's 305 MB — a 15.5× difference on identical output. Below the memory ceiling that is a nicety; at the ceiling it is the difference between an answer and a crash.

And the reverse holds at small sizes: on 7,342 points against 4,596 polygons, GeoPandas' in-memory index finished in 0.104 s against DuckDB's 0.168 s, because the query planning and structure building dominate.

Why one crossing is the target

Serialising a million geometries to WKB and back is not free, and a pipeline that alternates between the two libraries does it repeatedly.

The design that avoids it: express the whole heavy stage as one query — joins, filters, aggregates and all — and cross once with a result small enough that the conversion cost is irrelevant. That is also the stage where SQL is at its most readable, because the whole transformation is in one place.

Bar chart of transfer sizes for a million coordinate pairs in four formats.
Use .arrow() rather than .df() above about a million rows.

Edge cases or notes

  • st_aswkb() in the select list, or the geometry arrives unusable.
  • crs= is not optional. A GeoDataFrame with crs=None will not reproject and will join wrongly.
  • gdf.to_wkb() returns a plain DataFrame with bytes in the geometry column, ready for con.register.
  • .arrow() avoids a copy and is worth it above about a million rows.
  • Empty and null geometries survive WKB but need handling on both sides.
  • Mixed geometry types are fine in DuckDB and awkward in some GeoPandas exports.
  • Register temporary frames and unregister them, or the connection holds references.
  • Do not put a WKB column in a Parquet file and call it GeoParquet — that needs the geo metadata key, which DuckDB's native writer does add.

FAQ

How do I get a GeoDataFrame out of a DuckDB query?

Select st_aswkb(geom), read the result with .df(), and build the GeoDataFrame with shapely.from_wkb and an explicit crs=.

Why is my geometry column full of bytes?

Because WKB is bytes. Convert it with shapely.from_wkb, which is vectorised over the whole column.

Why does my GeoDataFrame have no CRS?

DuckDB tracks CRS on the column type and drops it in most operations, including ST_AsWKB. Set crs= explicitly from a constant in your code.

Should I use .df() or .arrow()?

.arrow() above about a million rows — it avoids an intermediate copy. Below that the difference is not worth the extra line.

How do I push a GeoDataFrame into DuckDB?

gdf.to_wkb(), register the resulting DataFrame, then st_geomfromwkb() in a create table as select.

Where should the boundary between the two be?

After the heavy stage. DuckDB streams a 13.5-million-point join in 305 MB where GeoPandas needs 4,739 MB; cross once, with a result small enough that the conversion does not matter.