Section: Query Engines & Cloud Analytics for GeoParquet 16 min read

DuckDB Spatial Extension for GeoParquet

Q: Can DuckDB write GeoParquet, not just read it?

Yes. The spatial extension bundles GDAL, so COPY ... TO with FORMAT GDAL and the Parquet driver writes valid GeoParquet, and a plain COPY ... TO with FORMAT PARQUET writes WKB geometry that other GeoParquet readers accept. For round-trips within DuckDB, writing WKB plus a bbox column preserves the predicate-pushdown behaviour on subsequent reads.

DuckDB turns a single process — a laptop, a notebook kernel, or one cloud VM — into a complete spatial SQL engine that reads GeoParquet in place, with no server to run and no data to load first. For a GIS engineer this is the shortest path from a file in object storage to an answer: install two extensions, point read_parquet at a path, and write ST_ predicates against the geometry. It is the workhorse of the broader Query Engines & Cloud Analytics for GeoParquet topic, and the one most teams reach for first because the feedback loop is measured in seconds.

The subtlety that separates a fast DuckDB query from a slow one is the same as on any in-place engine: DuckDB only wins when it reads a small slice of the data. That depends on the file being written with per-column statistics and a covering bounding-box column, and on your SQL filtering the bbox struct so DuckDB can skip row groups from the Parquet footer before it decodes a single geometry. This guide walks the full path: installing the spatial and httpfs extensions, reading local and remote GeoParquet, wiring predicate pushdown, joining and aggregating, and exporting results.

Prerequisites

DuckDB 0.10+ (CLI, Python duckdb>=0.10, or Node bindings) with network access to fetch extensions on first INSTALL
The spatial extension, which bundles GEOS and GDAL for ST_ functions and format read/write
The httpfs extension for reading GeoParquet over s3://, gs://, or https:// without downloading files first
GeoParquet 1.0 or 1.1 files carrying WKB geometry; the covering-bbox column (GeoParquet 1.1) is what enables spatial row-group skipping
A single declared CRS per dataset, recorded in the GeoParquet geo metadata — DuckDB will not re-project inside a predicate
Familiarity with Parquet internals: row groups, column chunks, and how column pruning in geospatial Parquet reduces bytes read

Architectural Foundations

DuckDB is a vectorized, columnar execution engine that treats a Parquet file — local or remote — as a scannable table. The spatial extension adds a GEOMETRY type and roughly four hundred ST_ functions backed by GEOS, plus a GDAL-backed reader/writer for the wider format zoo. Crucially, the spatial extension does not change how DuckDB reads Parquet: geometry lands as ordinary WKB binary in a column, and skipping is driven by the numeric statistics DuckDB already understands.

That is why the covering-bbox column matters so much. A GeoParquet 1.1 writer emits a bbox struct — {xmin, ymin, xmax, ymax} — alongside the geometry, and those four numeric sub-columns carry Parquet min/max statistics per row group. When your query filters on bbox.xmin <= query_max_x AND bbox.xmax >= query_min_x (and likewise for y), DuckDB compares the query window to each row group’s footer statistics and skips groups that cannot overlap — all before any WKB is decoded. The expensive ST_Intersects call then runs only on the rows that survive. Files written Hilbert-sorted make this dramatic, because spatially adjacent features share row groups and a small window touches few of them.

Step-by-Step Workflow

1. Install and Load the Extensions

INSTALL downloads and caches an extension binary once per machine; LOAD activates it for the current connection and must run in every new session. Load both spatial (for ST_ functions and GDAL) and httpfs (for remote reads) up front.

python

# duckdb>=0.10  (Python 3.10+)
import duckdb

con = duckdb.connect()  # in-memory; pass a path for a persistent catalog
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("INSTALL httpfs; LOAD httpfs;")

# Confirm the spatial extension is active
version = con.execute("SELECT library_version FROM pragma_version()").fetchone()
print(f"DuckDB {version[0]} with spatial + httpfs loaded")

# duckdb>=0.10  (Python 3.10+)
import duckdb

con = duckdb.connect()  # in-memory; pass a path for a persistent catalog
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("INSTALL httpfs; LOAD httpfs;")

# Confirm the spatial extension is active
version = con.execute("SELECT library_version FROM pragma_version()").fetchone()
print(f"DuckDB {version[0]} with spatial + httpfs loaded")

In the CLI the same two INSTALL; LOAD; pairs apply. For repeatable pipelines, run INSTALL in your image build and keep only LOAD in the hot path so a query never blocks on a network fetch.

2. Read Local and Remote GeoParquet

read_parquet accepts a local path, a glob, or an object-storage URI. The geometry column arrives as WKB binary; wrap it with ST_GeomFromWKB to obtain a GEOMETRY value that the ST_ functions accept.

python

# duckdb>=0.10
# Local single file
con.execute("""
    SELECT id, name, ST_GeomFromWKB(geometry) AS geom
    FROM read_parquet('parcels.parquet')
    LIMIT 5
""").fetchall()

# Remote glob over a partitioned dataset (see the S3 reference page)
con.execute("""
    SELECT COUNT(*) AS n
    FROM read_parquet(
        's3://bucket/parcels/region=eu/*.parquet',
        hive_partitioning => true
    )
""").fetchone()

# duckdb>=0.10
# Local single file
con.execute("""
    SELECT id, name, ST_GeomFromWKB(geometry) AS geom
    FROM read_parquet('parcels.parquet')
    LIMIT 5
""").fetchall()

# Remote glob over a partitioned dataset (see the S3 reference page)
con.execute("""
    SELECT COUNT(*) AS n
    FROM read_parquet(
        's3://bucket/parcels/region=eu/*.parquet',
        hive_partitioning => true
    )
""").fetchone()

Projecting an explicit column list — id, name, geometry rather than * — is the first lever on bytes read: DuckDB fetches only those column chunks. This is the same column pruning benefit that makes columnar storage worthwhile, and it applies even to unsorted files.

3. Push Spatial Predicates Down to Row-Group Statistics

A pure ST_Intersects filter is correct but forces DuckDB to decode every geometry. Add a numeric bbox predicate so DuckDB can skip row groups from footer statistics first, then let ST_Intersects refine the survivors.

python

# duckdb>=0.10 — bbox filter gates the exact ST_ predicate
min_x, min_y, max_x, max_y = 2.2, 48.8, 2.5, 48.9  # Paris window, EPSG:4326

rows = con.execute("""
    SELECT id, name
    FROM read_parquet('parcels.parquet')
    WHERE bbox.xmin <= ? AND bbox.xmax >= ?
      AND bbox.ymin <= ? AND bbox.ymax >= ?
      AND ST_Intersects(
            ST_GeomFromWKB(geometry),
            ST_MakeEnvelope(?, ?, ?, ?)
          )
""", [max_x, min_x, max_y, min_y, min_x, min_y, max_x, max_y]).fetchall()

# duckdb>=0.10 — bbox filter gates the exact ST_ predicate
min_x, min_y, max_x, max_y = 2.2, 48.8, 2.5, 48.9  # Paris window, EPSG:4326

rows = con.execute("""
    SELECT id, name
    FROM read_parquet('parcels.parquet')
    WHERE bbox.xmin <= ? AND bbox.xmax >= ?
      AND bbox.ymin <= ? AND bbox.ymax >= ?
      AND ST_Intersects(
            ST_GeomFromWKB(geometry),
            ST_MakeEnvelope(?, ?, ?, ?)
          )
""", [max_x, min_x, max_y, min_y, min_x, min_y, max_x, max_y]).fetchall()

The four bbox.* comparisons implement the standard rectangle-overlap test. Because those columns carry Parquet min/max statistics, DuckDB prunes non-overlapping row groups before decoding — the difference between reading 3 of 40 groups and reading all 40. This behaviour depends on row group sizing: groups sized 32–64 MB give fine skip granularity for selective spatial queries.

4. Join and Aggregate Across GeoParquet Tables

Spatial joins combine two GeoParquet sources on a geometric predicate. Keep a numeric bbox pre-filter on both sides where possible so the join input is already reduced.

python

# duckdb>=0.10 — count parcels per administrative region by containment
result = con.execute("""
    SELECT r.region_name, COUNT(*) AS parcel_count
    FROM read_parquet('parcels.parquet') AS p
    JOIN read_parquet('regions.parquet') AS r
      ON ST_Within(
           ST_GeomFromWKB(p.geometry),
           ST_GeomFromWKB(r.geometry)
         )
    GROUP BY r.region_name
    ORDER BY parcel_count DESC
""").fetchall()

# duckdb>=0.10 — count parcels per administrative region by containment
result = con.execute("""
    SELECT r.region_name, COUNT(*) AS parcel_count
    FROM read_parquet('parcels.parquet') AS p
    JOIN read_parquet('regions.parquet') AS r
      ON ST_Within(
           ST_GeomFromWKB(p.geometry),
           ST_GeomFromWKB(r.geometry)
         )
    GROUP BY r.region_name
    ORDER BY parcel_count DESC
""").fetchall()

For large joins, DuckDB benefits when both inputs share a compatible spatial partitioning so overlapping extents are co-located; the spatial partitioning you apply upstream directly reduces the number of candidate pairs the join must evaluate.

5. Export Filtered Results

Write results back to GeoParquet (for downstream engines) or GeoJSON (for a web client) using COPY. The GDAL driver produces spec-compliant GeoParquet; a plain Parquet copy of WKB geometry is accepted by most GeoParquet readers.

python

# duckdb>=0.10 — export a filtered subset to GeoParquet via GDAL
con.execute("""
    COPY (
        SELECT id, name, ST_GeomFromWKB(geometry) AS geom
        FROM read_parquet('parcels.parquet')
        WHERE bbox.xmin <= 2.5 AND bbox.xmax >= 2.2
          AND bbox.ymin <= 48.9 AND bbox.ymax >= 48.8
    )
    TO 'paris_parcels.parquet'
    WITH (FORMAT GDAL, DRIVER 'Parquet', LAYER_CREATION_OPTIONS 'GEOMETRY_ENCODING=WKB')
""")

# duckdb>=0.10 — export a filtered subset to GeoParquet via GDAL
con.execute("""
    COPY (
        SELECT id, name, ST_GeomFromWKB(geometry) AS geom
        FROM read_parquet('parcels.parquet')
        WHERE bbox.xmin <= 2.5 AND bbox.xmax >= 2.2
          AND bbox.ymin <= 48.9 AND bbox.ymax >= 48.8
    )
    TO 'paris_parcels.parquet'
    WITH (FORMAT GDAL, DRIVER 'Parquet', LAYER_CREATION_OPTIONS 'GEOMETRY_ENCODING=WKB')
""")

Production-Ready Implementation

The class below wraps a DuckDB connection for repeated GeoParquet queries: it loads extensions once, configures S3 access from the environment, exposes a typed bbox query method, and reports the rows returned. It is the reusable core you would drop into a service or notebook helper.

python

# duckdb>=0.10, python>=3.10
from __future__ import annotations

import os
from dataclasses import dataclass

import duckdb


@dataclass(frozen=True)
class Window:
    """Query bounding box in the dataset's declared CRS."""
    min_x: float
    min_y: float
    max_x: float
    max_y: float


class GeoParquetReader:
    """A DuckDB-backed reader for GeoParquet with bbox pushdown."""

    def __init__(self, *, aws_region: str | None = None) -> None:
        self._con = duckdb.connect()
        self._con.execute("INSTALL spatial; LOAD spatial;")
        self._con.execute("INSTALL httpfs; LOAD httpfs;")
        self._con.execute("SET enable_object_cache=true;")
        if aws_region:
            if "AWS_ACCESS_KEY_ID" not in os.environ:
                raise RuntimeError("AWS_ACCESS_KEY_ID not set for remote reads")
            self._con.execute(f"SET s3_region='{aws_region}';")
            self._con.execute("SET s3_access_key_id=getenv('AWS_ACCESS_KEY_ID');")
            self._con.execute(
                "SET s3_secret_access_key=getenv('AWS_SECRET_ACCESS_KEY');"
            )

    def bbox_query(
        self,
        source: str,
        window: Window,
        *,
        columns: tuple[str, ...] = ("id", "geometry"),
        hive: bool = False,
    ) -> list[tuple]:
        """Return rows whose geometry intersects the window.

        The numeric bbox predicate prunes row groups from footer
        statistics before the exact ST_Intersects test runs.
        """
        projection = ", ".join(columns)
        hive_opt = ", hive_partitioning => true" if hive else ""
        sql = f"""
            SELECT {projection}
            FROM read_parquet(?{hive_opt})
            WHERE bbox.xmin <= ? AND bbox.xmax >= ?
              AND bbox.ymin <= ? AND bbox.ymax >= ?
              AND ST_Intersects(
                    ST_GeomFromWKB(geometry),
                    ST_MakeEnvelope(?, ?, ?, ?)
                  )
        """
        params = [
            source,
            window.max_x, window.min_x,
            window.max_y, window.min_y,
            window.min_x, window.min_y, window.max_x, window.max_y,
        ]
        try:
            return self._con.execute(sql, params).fetchall()
        except duckdb.IOException as exc:
            raise RuntimeError(f"read failed for {source}: {exc}") from exc

    def close(self) -> None:
        self._con.close()


if __name__ == "__main__":
    reader = GeoParquetReader(aws_region="eu-west-1")
    try:
        win = Window(2.2, 48.8, 2.5, 48.9)
        out = reader.bbox_query(
            "s3://bucket/parcels/*.parquet",
            win,
            columns=("id", "name", "geometry"),
        )
        print(f"{len(out)} features in window")
    finally:
        reader.close()

# duckdb>=0.10, python>=3.10
from __future__ import annotations

import os
from dataclasses import dataclass

import duckdb


@dataclass(frozen=True)
class Window:
    """Query bounding box in the dataset's declared CRS."""
    min_x: float
    min_y: float
    max_x: float
    max_y: float


class GeoParquetReader:
    """A DuckDB-backed reader for GeoParquet with bbox pushdown."""

    def __init__(self, *, aws_region: str | None = None) -> None:
        self._con = duckdb.connect()
        self._con.execute("INSTALL spatial; LOAD spatial;")
        self._con.execute("INSTALL httpfs; LOAD httpfs;")
        self._con.execute("SET enable_object_cache=true;")
        if aws_region:
            if "AWS_ACCESS_KEY_ID" not in os.environ:
                raise RuntimeError("AWS_ACCESS_KEY_ID not set for remote reads")
            self._con.execute(f"SET s3_region='{aws_region}';")
            self._con.execute("SET s3_access_key_id=getenv('AWS_ACCESS_KEY_ID');")
            self._con.execute(
                "SET s3_secret_access_key=getenv('AWS_SECRET_ACCESS_KEY');"
            )

    def bbox_query(
        self,
        source: str,
        window: Window,
        *,
        columns: tuple[str, ...] = ("id", "geometry"),
        hive: bool = False,
    ) -> list[tuple]:
        """Return rows whose geometry intersects the window.

        The numeric bbox predicate prunes row groups from footer
        statistics before the exact ST_Intersects test runs.
        """
        projection = ", ".join(columns)
        hive_opt = ", hive_partitioning => true" if hive else ""
        sql = f"""
            SELECT {projection}
            FROM read_parquet(?{hive_opt})
            WHERE bbox.xmin <= ? AND bbox.xmax >= ?
              AND bbox.ymin <= ? AND bbox.ymax >= ?
              AND ST_Intersects(
                    ST_GeomFromWKB(geometry),
                    ST_MakeEnvelope(?, ?, ?, ?)
                  )
        """
        params = [
            source,
            window.max_x, window.min_x,
            window.max_y, window.min_y,
            window.min_x, window.min_y, window.max_x, window.max_y,
        ]
        try:
            return self._con.execute(sql, params).fetchall()
        except duckdb.IOException as exc:
            raise RuntimeError(f"read failed for {source}: {exc}") from exc

    def close(self) -> None:
        self._con.close()


if __name__ == "__main__":
    reader = GeoParquetReader(aws_region="eu-west-1")
    try:
        win = Window(2.2, 48.8, 2.5, 48.9)
        out = reader.bbox_query(
            "s3://bucket/parcels/*.parquet",
            win,
            columns=("id", "name", "geometry"),
        )
        print(f"{len(out)} features in window")
    finally:
        reader.close()

Reference: DuckDB Spatial Read Patterns

Pattern	Function / Clause	When It Skips Data	Primary Use Case
Narrow projection	`SELECT id, geometry`	Reads only named column chunks	Any query; always apply
Bbox row-group skip	`WHERE bbox.xmin <= ? ...`	Prunes groups from footer stats	Selective spatial windows
Exact spatial test	`ST_Intersects`, `ST_Within`	No skip; runs on survivors	Precise geometry filtering
Partition prune	`hive_partitioning => true` + path predicate	Drops whole directories	Region/time-sliced datasets
Object cache	`SET enable_object_cache=true`	Reuses fetched footers/chunks	Repeated queries on same files
Distance filter	`ST_DWithin`	No skip without bbox pre-filter	Proximity search; pair with bbox

Failure Modes and Gotchas

Anti-pattern	Symptom	Fix
Only `ST_Intersects`, no bbox filter	Full decode of every geometry; query scans whole file	Add `bbox.xmin/xmax/ymin/ymax` predicates so DuckDB skips row groups first
`SELECT *` on wide tables	Slow remote reads; large bytes transferred	Project an explicit minimal column list
Forgetting `LOAD` in a new session	`Catalog Error: function ST_... does not exist`	Run `LOAD spatial;` at the top of every connection
Mixed CRS across files	Wrong join/distance results; garbage bbox stats	Normalize to one CRS upstream; DuckDB will not re-project in a predicate
Reading files without bbox column	No spatial skipping; every group read	Re-export with a covering bbox column (GeoParquet 1.1 writer)
Unpinned extension version	Pushdown behaviour changes silently on upgrade	Pin DuckDB and re-benchmark bytes read after any version bump

Frequently Asked Questions

Do I need to install the spatial extension every time I start DuckDB?

INSTALL downloads the extension binary once and caches it on disk; you do not need to run it again on the same machine. LOAD, however, must run in every new connection because extensions are loaded per session. A common pattern is to call INSTALL spatial once during environment setup and LOAD spatial at the top of each script or notebook.

Does DuckDB read the GeoParquet geometry column natively?

DuckDB reads the geometry column as WKB binary from the Parquet file, then you wrap it with ST_GeomFromWKB to get a GEOMETRY value the ST_ functions understand. Recent spatial-extension versions can also read GeoParquet metadata directly with the st_read_parquet helper, but the explicit ST_GeomFromWKB pattern is the most portable across versions and works on any Parquet file carrying WKB geometry.

How do I make DuckDB skip row groups on a spatial filter?

Filter on the covering bbox struct columns (bbox.xmin, bbox.xmax, bbox.ymin, bbox.ymax) that GeoParquet 1.1 writers emit, not only on ST_Intersects. Those numeric columns carry Parquet min/max statistics, so DuckDB evaluates them against the footer and skips non-overlapping row groups before decoding any geometry. The ST_ predicate then runs only on the surviving rows for exact results.

Can DuckDB write GeoParquet, not just read it?

Yes. The spatial extension bundles GDAL, so COPY … TO with FORMAT GDAL and the Parquet driver writes valid GeoParquet, and a plain COPY … TO with FORMAT PARQUET writes WKB geometry that other GeoParquet readers accept. For round-trips within DuckDB, writing WKB plus a bbox column preserves the predicate-pushdown behaviour on subsequent reads.

Querying GeoParquet in S3 with DuckDB — httpfs config, glob patterns, and minimizing bytes scanned on remote data
AWS Athena Spatial Queries on GeoParquet — the serverless alternative when you want no engine to run
Trino & Presto GeoParquet Connector — scaling the same SQL to a shared cluster
Row Group Sizing Strategies for Parquet — sizing that governs how finely DuckDB can skip
Spatial Partitioning with Quadtree Indexes — upstream layout that makes bbox skipping effective

← Back to Query Engines & Cloud Analytics for GeoParquet

Continue exploring

Querying GeoParquet in S3 with DuckDB Read article →

#DuckDB Spatial Extension for GeoParquet

#Prerequisites

#Architectural Foundations

#Step-by-Step Workflow

#1. Install and Load the Extensions

#2. Read Local and Remote GeoParquet

#3. Push Spatial Predicates Down to Row-Group Statistics

#4. Join and Aggregate Across GeoParquet Tables

#5. Export Filtered Results

#Production-Ready Implementation

#Reference: DuckDB Spatial Read Patterns

#Failure Modes and Gotchas

#Frequently Asked Questions

#Related

Continue exploring

DuckDB Spatial Extension for GeoParquet

Prerequisites

Architectural Foundations

Step-by-Step Workflow

1. Install and Load the Extensions

2. Read Local and Remote GeoParquet

3. Push Spatial Predicates Down to Row-Group Statistics

4. Join and Aggregate Across GeoParquet Tables

5. Export Filtered Results

Production-Ready Implementation

Reference: DuckDB Spatial Read Patterns

Failure Modes and Gotchas

Frequently Asked Questions

Related