DuckDB Spatial Analytical SQL for GIS

GeoParquet vs Shapefile Performance: Root-Cause Analysis & Optimization

The performance divergence between GeoParquet and Shapefile formats is not a marginal optimization; it is a structural consequence of storage layout, serialization overhead, and query engine integration. For data engineers and platform teams migrating legacy GIS pipelines to analytical SQL engines, understanding the root-cause bottlenecks in Shapefile I/O versus GeoParquet columnar execution is mandatory for predictable throughput and deterministic latency.

I/O Architecture & Storage Layout

Shapefiles operate as a row-oriented, multi-file composite (.shp, .shx, .dbf, .prj). Each geometry read requires synchronized disk seeks across three separate file descriptors, forcing the OS page cache to thrash under concurrent workloads. GeoParquet eliminates this fragmentation by storing geometries in a single columnar binary file with dictionary-encoded attributes and RLE-compressed bounding boxes.

GeoParquet leverages memory-mapped I/O and aggressive column pruning, allowing the execution engine to load only the geometry and filtered attribute columns into the execution buffer. This reduces peak memory footprint by 60–85% on datasets exceeding 10M rows compared to full Shapefile scans, because row-based Shapefile deserialization cannot be lazily materialized.

Serialization & Parsing Overhead

The Shapefile specification lacks native type safety and relies on C-struct binary offsets that require runtime validation. Every vertex coordinate must be unpacked sequentially, and attribute strings are parsed from fixed-width .dbf records. In contrast, GeoParquet Parsing utilizes Apache Arrow’s zero-copy deserialization pipeline. Geometries are stored as Well-Known Binary (WKB) in a dedicated column, bypassing text-based serialization entirely.

When benchmarking against GeoJSON ingestion (which suffers from JSON tokenization overhead and dynamic type resolution), GeoParquet achieves 8–12× faster ingestion rates in typical workloads. The root cause of Shapefile parsing latency is the mandatory .shx index traversal: the engine must reconstruct the spatial offset table in-memory before executing any WHERE clause, whereas GeoParquet embeds row-group statistics directly in the Parquet footer.

Coordinate Reference Systems & Metadata Integrity

Shapefiles delegate CRS definition to an external .prj file, which is frequently missing, malformed, or mismatched during ETL handoffs. This causes silent coordinate misalignment scenarios where spatial joins produce topologically invalid results due to implicit datum shifts. GeoParquet enforces strict OGC CRS metadata in the Parquet schema (geo extension key), embedding WKT or EPSG codes directly alongside the geometry column. DuckDB’s CRS Mapping & Transformations pipeline reads this metadata at query planning time, enabling automatic on-the-fly reprojection without intermediate file staging. Adherence to the OGC GeoParquet Specification v1.1.0 guarantees schema-level validation during write operations.

Spatial Indexing Internals & Query Execution

Shapefiles contain no native spatial index within the binary payload. Spatial predicates (ST_Intersects, ST_Contains) trigger full sequential scans or require external .qix generation, which decays rapidly under high-concurrency reads. GeoParquet implements predicate pushdown via row-group level min/max bounding box statistics. During query compilation, the optimizer evaluates spatial predicates against embedded statistics, skipping irrelevant row groups entirely. This architecture is detailed in DuckDB Spatial Architecture & Fundamentals, where the vectorized execution model aligns columnar reads with SIMD-accelerated geometry operations.

To enforce optimal execution plans, configure the following session parameters:

SET threads = 8;
SET memory_limit = '16GB';
SET preserve_insertion_order = false;
SET enable_http_metadata_cache = true;

Incident Resolution & Diagnostic Workflows

When performance degradation or ingestion failures occur, isolate the bottleneck using deterministic diagnostic queries and fallback routing.

1. I/O Wait & Memory Pressure Diagnostics

EXPLAIN ANALYZE
SELECT id, ST_Area(geometry)
FROM read_parquet('s3://bucket/data/*.parquet')
WHERE ST_Intersects(geometry, ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));

Inspect the EXPLAIN output for ParquetScan vs SeqScan. If ParquetScan shows filter_pushdown: false, verify that the spatial predicate is in the WHERE clause and that the file contains valid geo metadata.

2. Shapefile Fallback Configuration If a GeoParquet footer is corrupted or schema drift occurs, route ingestion to a Shapefile fallback using st_read:

-- Explicit Shapefile fallback using GDAL/OGR reader
CREATE TABLE legacy_fallback AS
SELECT * FROM st_read('/data/legacy.shp');

-- Validate geometry integrity post-ingress
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_count
FROM legacy_fallback;

3. Metadata Validation Query

SELECT column_name, type
FROM parquet_schema('data.parquet')
WHERE column_name = 'geometry';

-- Inspect row-group statistics for the geometry column
SELECT row_group_id, row_group_num_rows
FROM parquet_metadata('data.parquet')
LIMIT 10;

When row group statistics are missing, regenerate the Parquet file with a compliant writer (e.g., pyarrow with GeoParquet metadata).

Enterprise Deployment & Access Control

Production pipelines require deterministic partitioning and strict access boundaries. GeoParquet supports partition pruning via directory structure (/year=2024/month=01/data.parquet), which integrates natively with cloud object storage lifecycle policies.

DuckDB has no SQL GRANT/role system; enforce access at the boundary — mount the database read-only (ATTACH '...' (READ_ONLY)), restrict writes to a dedicated ingestion account, and partition tenants into separate database files. For audit compliance, log all spatial predicate evaluations and track row-group skip rates to validate index utilization across the fleet.