DuckDB Spatial Analytical SQL for GIS

Setting Up DuckDB Spatial CLI

CLI Initialization & Extension Bootstrap

Deterministic deployment of the DuckDB Spatial extension requires explicit installation and environment isolation. The CLI operates as an embedded analytical engine; implicit loading can trigger silent fallbacks to legacy geometry parsers or mismatched ABI boundaries.

# Install and verify the spatial extension
duckdb -c "INSTALL spatial; LOAD spatial; SELECT * FROM duckdb_extensions() WHERE extension_name = 'spatial';"

Diagnostic & Fallback Routing: If LOAD spatial throws Extension 'spatial' not found, execute the following isolation checks:

  1. Verify write permissions on ~/.duckdb/extensions/ (or $DUCKDB_EXTENSION_DIR).
  2. Confirm outbound HTTPS access to the DuckDB extension registry. Corporate proxies often strip TLS headers required for binary verification.
  3. For air-gapped or restricted environments, pre-stage the compiled binary and force local resolution:
LOAD '/path/to/spatial.duckdb_extension';
SELECT * FROM duckdb_extensions() WHERE extension_name = 'spatial' AND installed = true;

Storage Topology & Memory Boundaries

The default :memory: catalog is unsuitable for production spatial workloads due to unbounded geometry serialization and lack of WAL persistence. Route explicitly to disk and enforce strict memory ceilings to prevent OOM during large-scale spatial joins.

-- Open a persistent database (CLI flag: duckdb /data/warehouse/spatial_analytics.duckdb)
-- Or via ATTACH from within an existing session:
ATTACH '/data/warehouse/spatial_analytics.duckdb' AS warehouse;
USE warehouse;

-- Apply resource limits (use SET for runtime configuration)
SET memory_limit = '8GB';
SET threads = 4;
SET temp_directory = '/tmp/duckdb_spatial_scratch';

Geometry columns are materialized as binary blobs with inline WKB headers, bypassing traditional row-store overhead. Understanding how the engine manages these allocations is critical when scaling past single-node limits. Refer to DuckDB Spatial Architecture & Fundamentals for detailed memory layout specifications. When processing large tile sets or raster-adjacent vector data, explicitly manage the In-Memory vs Disk Storage boundary by monitoring spill-to-disk thresholds:

-- Diagnostic: Track memory allocation and temp file growth
SELECT * FROM duckdb_memory();
SELECT * FROM duckdb_temporary_files();

Vector Ingestion: GeoJSON & GeoParquet Parsing

GeoJSON ingestion via st_read() uses the GDAL/OGR reader to parse features and convert geometry to the GEOMETRY type. This path is CPU-bound and degrades non-linearly beyond ~500MB files. For high-throughput pipelines, enforce streaming ingestion or migrate to columnar formats.

-- GeoJSON ingestion via st_read (well-formed GeoJSON files)
CREATE TABLE parcels AS
SELECT geom, properties->>'parcel_id' AS parcel_id
FROM st_read('/data/raw/parcels.geojson');

-- Manual extraction for non-standard JSON layouts
CREATE TABLE parcels_manual AS
SELECT
    st_geomfromgeojson(json_extract(json_col, '$.geometry')::VARCHAR) AS geom,
    json_extract(json_col, '$.properties.parcel_id')::VARCHAR AS parcel_id
FROM read_json_auto('/data/raw/parcels.json', columns={'json_col': 'JSON'});

GeoParquet parsing leverages native columnar decoding. DuckDB Spatial automatically detects the geo metadata extension and maps geometry columns without explicit casting. If ingestion fails with Invalid geometry type, the source file likely contains non-standard WKB variants or missing CRS headers. Validate geometry integrity:

SELECT
    ST_AsWKB(geom) AS wkb_blob,
    ST_IsValid(geom) AS is_valid
FROM read_parquet('/data/raw/parcels.parquet')
LIMIT 10;

Spatial Indexing Internals & Query Optimization

DuckDB Spatial supports persistent R-tree indexes on GEOMETRY columns, which the query planner uses to accelerate spatial joins and range queries.

CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom);

Validate index utilization and execution plans using EXPLAIN ANALYZE. If the planner defaults to a sequential scan, verify that query predicates use supported spatial operators (ST_Intersects, ST_Within, ST_DWithin) and that bounding box filters precede expensive geometric evaluations.

EXPLAIN ANALYZE
SELECT a.parcel_id, b.zone_name
FROM parcels a
JOIN zoning_zones b ON ST_Intersects(a.geom, b.geom)
WHERE a.geom && ST_MakeEnvelope(-122.5, 37.7, -122.3, 37.9);

Fallback Routing for Index Misses:

  • Ensure the optimizer is active (check that SET disabled_optimizers is empty).
  • Verify column statistics: ANALYZE parcels;
  • If spatial join remains unindexed, materialize a pre-filtered CTE using coordinate range predicates on ST_XMin, ST_XMax, ST_YMin, ST_YMax to force range pruning before geometric evaluation.

CRS Mapping & Transformations

Coordinate Reference System (CRS) drift is the primary cause of silent spatial misalignment. DuckDB Spatial relies on the PROJ library for transformation pipelines. DuckDB stores no inline SRID per geometry; track each layer’s CRS explicitly and confirm both sides of a join share the same CRS before executing spatial predicates.

-- Detect likely CRS mismatch by checking coordinate ranges
-- (geographic lon/lat must fall within ±180/±90)
SELECT
    count(*) FILTER (WHERE ST_XMin(geom) BETWEEN -180 AND 180
                         AND ST_YMin(geom) BETWEEN -90 AND 90) AS looks_geographic,
    count(*) AS total
FROM parcels;

Force explicit CRS normalization when source metadata is absent:

-- If the source data is known to be in EPSG:32633 but has no inline SRID,
-- transform to the working CRS explicitly.
UPDATE parcels
SET geom = ST_Transform(geom, 'EPSG:32633', 'EPSG:3857');

CRS Drift Troubleshooting:

  • Invalid projection errors indicate malformed EPSG codes or missing PROJ data directories. Set the PROJ_DATA environment variable to the correct path.
  • For authoritative transformation parameters, consult the PROJ Coordinate Transformation documentation.
  • Validate output geometry validity post-transformation: SELECT count(*) FROM parcels WHERE NOT ST_IsValid(geom);

Enterprise Deployment & Security Controls

DuckDB operates as a single-process embedded engine. Enterprise isolation relies on filesystem permissions, connection settings, and catalog access controls.

-- Open the database file in read-only mode for analytical consumers
-- (pass the READ_ONLY flag to ATTACH, or use the CLI flag --readonly)
ATTACH '/data/prod.duckdb' AS prod (READ_ONLY);

Security & Access Routing:

  1. Mount the .duckdb file on a read-only NFS/EBS volume for downstream consumers.
  2. Restrict write access to a dedicated ingestion service account. DuckDB does not implement row-level security; enforce data partitioning at the file level.
  3. Audit spatial queries via connection-level logging. Route CLI invocations through a wrapper script that captures EXPLAIN output and execution duration for performance baselining.
  4. For compliance with geospatial data standards, validate output against the OGC GeoParquet Specification before external distribution.