Querying Partitioned GeoParquet with DuckDB Spatial

DuckDB reads a directory of Hive-partitioned GeoParquet files as one table, prunes partitions from the path, skips row groups from their statistics, and projects only the columns you name — then writes NDJSON on stdout that Tippecanoe consumes directly. For a partitioned tile build this replaces an extraction script, an intermediate file, and most of the memory.

When to Use This

When the source is a partitioned GeoParquet dataset — region=.../year=.../part-*.parquet — and each tile build needs a slice of it. That is the common shape for anything derived from a data warehouse, and it is exactly the shape a partitioned tile build wants to consume.

For a single unpartitioned file, pyarrow with a column projection is simpler and just as fast.

Specification Detail

The three pruning mechanisms compose, and their order of effectiveness is the order above: skipping a directory is free, skipping a row group costs reading statistics, and skipping a column costs nothing once the projection is declared.

Production Command

-- extract.sql
INSTALL spatial; LOAD spatial;

COPY (
  SELECT
    'Feature'                                  AS type,
    json(ST_AsGeoJSON(geometry))               AS geometry,
    json_object(
      'name',    name,
      'highway', highway,
      'lanes',   CAST(lanes AS INTEGER)
    )                                          AS properties
  FROM read_parquet('s3://warehouse/roads/**/*.parquet', hive_partitioning = true)
  WHERE country = 'GB'                          -- partition pruning
    AND bbox.xmin < 0.34 AND bbox.xmax > -0.52  -- row-group skipping
    AND bbox.ymin < 51.70 AND bbox.ymax > 51.27
    AND geometry IS NOT NULL
) TO '/dev/stdout' (FORMAT JSON, ARRAY false);

duckdb -c ".read extract.sql" \
  | tippecanoe -o build/roads.mbtiles -l roads -Z5 -z14 --force \
      --drop-densest-as-needed --simplification=8 -

FORMAT JSON, ARRAY false is what produces newline-delimited objects rather than a JSON array — exactly the NDJSON shape Tippecanoe reads on stdin. An array would be a single enormous value and Tippecanoe would reject it.

The bbox predicates use the bounding-box columns GeoParquet 1.1 encourages writers to emit. When they are absent, ST_Intersects still works and does not skip row groups, so the scan reads everything and filters afterwards:

-- Correct, but no row-group skipping without bbox columns
WHERE ST_Intersects(geometry, ST_MakeEnvelope(-0.52, 51.27, 0.34, 51.70))

Driving a Partitioned Build

Because the extract is a query, generating one per tile partition is a loop rather than a script:

while IFS=$'\t' read -r NAME W S E N; do
  duckdb -c "
    INSTALL spatial; LOAD spatial;
    COPY (
      SELECT 'Feature' AS type,
             json(ST_AsGeoJSON(geometry)) AS geometry,
             json_object('name', name, 'highway', highway) AS properties
      FROM read_parquet('warehouse/roads/**/*.parquet', hive_partitioning = true)
      WHERE bbox.xmin < $E AND bbox.xmax > $W
        AND bbox.ymin < $N AND bbox.ymax > $S
    ) TO '/dev/stdout' (FORMAT JSON, ARRAY false);" \
  | tippecanoe -o "build/$NAME.mbtiles" -l roads -Z5 -z14 --force --quiet -
done < partitions.tsv

Each partition’s scan reads only the row groups its bounding box touches, which on a spatially ordered dataset is a small fraction of the file — and that is what makes a per-partition extract cheap enough to run on every build.

Interaction Effects

With CRS. DuckDB’s spatial extension does not reproject implicitly. If the dataset is not in EPSG:4326, wrap the geometry in ST_Transform with an explicit source and target, or Tippecanoe receives coordinates it will interpret as degrees.

With file ordering. Row-group skipping is only effective when the data is spatially clustered — see bbox spatial filtering for how much ordering is worth.

With attribute types. json_object produces the properties object, and its values carry DuckDB’s types. A NULL becomes JSON null, which Tippecanoe stores as an absent attribute — usually what you want, and worth knowing when a style expects the key to exist.

With memory. DuckDB streams the COPY output rather than materialising the result, so peak memory is bounded by the row groups in flight rather than by the result size. This is what lets a multi-hundred-gigabyte source feed a build on a modest machine.

Performance Impact

Measured against a 340-column, 210 GB partitioned road dataset on local NVMe:

The memory column is as interesting as the time: projection reduces peak RAM by more than it reduces bytes read, because the columns not projected are never decompressed into memory at all.

Common Mistakes

Emitting a JSON array. Without ARRAY false the output is one giant array and Tippecanoe rejects it with a FeatureCollection error.

Assuming ST_Intersects prunes row groups. It filters correctly and reads everything. Use the bbox columns for the coarse filter and a spatial predicate only for exactness if needed.

Forgetting LOAD spatial. INSTALL is persistent, LOAD is per session, and a script that installs but does not load fails on the first ST_ call.

Leaving the geometry column out of the projection. Easy to do when listing attributes, and it produces a stream of features with no geometry that Tippecanoe accepts and tiles as nothing.

FAQ

Does DuckDB read GeoParquet metadata?

The spatial extension understands the geo metadata for reading geometry columns. It does not reproject based on the declared CRS, so confirm the CRS separately and transform explicitly.

Can it read directly from object storage?

Yes, with the httpfs extension, and partition pruning works against remote paths too — which means a build machine never has to hold the dataset locally.

Is this faster than pyarrow?

For partitioned datasets with predicates, usually yes, because the SQL layer pushes filters into the scan automatically. For a single file with a simple column projection the two are comparable.

What about very wide attribute tables?

Project deliberately. The saving in this page’s measurements comes mostly from projection, and a SELECT * on a 340-column table undoes it entirely.

Related

• GeoParquet Input Processing — the parent topic and the full ingest chain.
• Bbox Spatial Filtering of GeoParquet Inputs — why file ordering decides how much the predicate saves.
• Column Projection Pushdown for GeoParquet — the mechanism behind the memory reduction.
• Streaming GeoParquet to Tippecanoe stdin as NDJSON — the output shape this query produces.