Setting Up DuckDB Spatial CLI

A spatial CLI session that loads the extension implicitly and runs against the default in-memory catalog is non-deterministic by construction; this page hardens that bootstrap into a reproducible baseline, and is the session-setup deep dive behind the in-memory vs disk storage reference within DuckDB Spatial architecture and fundamentals.

Root-Cause Analysis: Why an Unconfigured Session Fails Silently #

The failure modes specific to bootstrapping a spatial CLI session are not query bugs — they are environment and routing defects that surface only once a real geometry workload runs. They fall into five distinct causes, each with its own signal and fix:

• Extension resolution / ABI mismatch. LOAD spatial can fail with Extension 'spatial' not found, or load a binary compiled against a different core version, producing Symbol not found or silent fallbacks to a legacy geometry path. The extension is resolved at runtime over HTTPS from the registry, so any proxy, air-gapped host, or read-only ~/.duckdb/extensions/ breaks it.
• Implicit in-memory catalog. A bare duckdb invocation opens the :memory: catalog. There is no write-ahead log, nothing survives the process, and the working set has no on-disk anchor — so the first large spatial join inflates geometry arrays straight into the heap with nowhere to spill.
• Unbounded memory ceiling. Defaults infer memory_limit from total RAM and threads from logical cores, so the same query OOM-kills on one machine and streams on another. Geometry-expanding operators (ST_Buffer, ST_Union, ST_Intersection) materialize transient arrays far larger than the source column, and without a ceiling the engine never switches to external (spill-to-disk) execution.
• Missing spatial index. DuckDB Spatial never auto-indexes geometry columns. Until an R-tree index exists and the planner can use it, every predicate degrades toward an $O(N \times M)$ scan.
• Unanchored CRS. DuckDB stores no inline SRID per geometry. A session that ingests two layers in different coordinate systems will run CRS transformations silently wrong — predicates return empty or absurd results with no error.

The common shape: none of these throw at session start. They throw, or quietly mislead, only under load. A hardened bootstrap converts every one of them into an explicit, checkable step.

Deterministic Configuration #

Treat the bootstrap as code, not as interactive typing. The minimal session below is everything the patterns on this page depend on — extension load with verification, an explicit on-disk catalog, and a pinned memory model.

# Bootstrap a verified, persistent spatial session from one CLI invocation.
# Opening a file path (not :memory:) gives a WAL-backed catalog that survives restarts.
duckdb /data/warehouse/spatial_analytics.duckdb -c "
  INSTALL spatial;
  LOAD spatial;
  -- Fail loudly at startup rather than at first ST_ call:
  SELECT extension_name, installed, loaded
  FROM duckdb_extensions() WHERE extension_name = 'spatial';
"

-- Pin the memory model so behaviour is identical across machines.
SET memory_limit = '8GB';   -- Hard ceiling, not a hint: near this bound the engine
                            -- switches to external hash-join/merge-sort spilling
                            -- instead of OOM-killing the process.
SET threads = 4;            -- Each in-flight spatial pipeline holds its own buffers,
                            -- so higher thread counts RAISE peak RAM and spill risk —
                            -- trade throughput against headroom deliberately.
SET temp_directory = '/mnt/nvme/duckdb_spatial_scratch'; -- Spills land here; once a query
                            -- spills, scratch I/O is the latency floor, so HDD paths
                            -- erase the columnar advantage entirely. Use NVMe.
SET preserve_insertion_order = false; -- Geometry row order is rarely meaningful; dropping
                            -- it lets multi-file scans reorder row groups and buffer less.

For environments where the registry is unreachable (corporate proxy stripping TLS, or an air-gapped host), pre-stage the compiled binary and force local resolution instead of fighting the network:

-- Air-gapped / restricted: load a pre-staged binary by absolute path.
LOAD '/opt/duckdb/ext/spatial.duckdb_extension';
SELECT * FROM duckdb_extensions()
WHERE extension_name = 'spatial' AND installed = true;

If the catalog must persist for an already-open in-memory session, ATTACH a file and switch into it rather than restarting:

ATTACH '/data/warehouse/spatial_analytics.duckdb' AS warehouse; -- file-backed = WAL + spill anchor
USE warehouse;                                                  -- subsequent DDL lands on disk

Optimized Execution Pattern #

The behavioural change that matters most at bootstrap is moving from an implicit, unindexed, registry-dependent session to an explicit one. The contrast below ingests the same data and runs the same join; only the setup differs.

Before — implicit and fragile:

-- Bare session: :memory: catalog, no ceiling, no index, no verification.
duckdb;                                              -- volatile in-memory catalog
LOAD spatial;                                        -- silent if registry is reachable, fails otherwise
CREATE TABLE parcels AS SELECT * FROM st_read('/data/raw/parcels.geojson');
SELECT a.parcel_id, b.zone_name
FROM parcels a JOIN zoning_zones b
  ON ST_Intersects(a.geom, b.geom);                  -- O(N×M): no MBR pre-filter, no index

After — explicit, verified, index-routed:

-- Persistent catalog + pinned memory (from the block above), then:
-- GeoParquet decodes columnar with native geo metadata — far cheaper than the
-- GDAL/OGR GeoJSON path for large inputs (see GeoParquet parsing).
CREATE TABLE parcels AS
SELECT * FROM read_parquet('/data/raw/parcels.parquet');

-- Build the R-tree so the planner can route the join through bbox pruning.
CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom);

-- The && bbox operator runs first (cheap, index-assisted); ST_Intersects
-- only evaluates the survivors — turning O(N×M) into a tractable workload.
SELECT a.parcel_id, b.zone_name
FROM parcels a JOIN zoning_zones b
  ON a.geom && b.geom AND ST_Intersects(a.geom, b.geom);

The key diff is twofold: the source moves from a CPU-bound GeoJSON ingestion (which degrades non-linearly past ~500 MB) to a columnar GeoParquet read, and the join gains an explicit && bounding-box predicate that the R-tree can serve before any exact topology runs. If GeoJSON is unavoidable, project only the columns you need at read time and materialize geometries as the native GEOMETRY type — the trade-offs against carrying raw WKB are covered under ST_Geometry vs WKB storage.

Diagnostic Queries & Plan Validation #

A bootstrap is only correct if you can prove the engine is doing what you configured. Confirm the index is actually used with EXPLAIN ANALYZE:

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

Read the plan for these signals:

• RTREE_INDEX_SCAN (or an index-assisted scan) on parcels — the index is live. A plain SEQ_SCAN feeding the join means the planner ignored it: confirm the predicate uses a supported operator (&&, ST_Intersects, ST_Within, ST_DWithin) and that SET disabled_optimizers is empty.
• Estimated vs actual row counts within ~10× — large drift means stale statistics; run ANALYZE parcels; so the planner costs the join correctly.
• No EXTERNAL / temp-file activity for a query you expected to stream — if it appears, the working set is breaching memory_limit and the engine is spilling rather than streaming.

One-liner monitoring queries to keep open during heavy ingestion or joins:

SELECT * FROM duckdb_memory();           -- live block allocation vs the ceiling
SELECT * FROM duckdb_temporary_files();  -- non-empty/growing during a plain SELECT = thrashing
SELECT * FROM duckdb_extensions() WHERE extension_name = 'spatial'; -- loaded == true?

As a working threshold: if the resident working set exceeds roughly 60% of memory_limit, expect I/O-bound execution and budget for spill. Raster-flattened pixel tables hit this far sooner than vector data — the raster-specific limits are isolated in memory limits for large raster data.

Geometry Validation & Fallback Routing #

A clean session still produces wrong answers if the geometries are invalid or the coordinate system is unanchored. Guard both at ingestion, before any index build or join.

-- Validate first: self-intersections and unclosed rings break topology predicates
-- and corrupt the R-tree bounding boxes built from them.
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid,
       count(*)                                      AS total
FROM parcels;

-- Repair in place, then (re)build the index on clean geometry.
UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);
DROP INDEX IF EXISTS idx_parcels_geom;
CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom);

Because there is no inline SRID, detect a likely CRS mismatch by range-checking coordinates before trusting any predicate:

-- Geographic data must fall within ±180 / ±90; anything else is projected
-- (e.g. metres) and must be transformed before joining a lon/lat layer.
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;

-- Normalize explicitly when source metadata is absent (known UTM 33N -> Web Mercator):
UPDATE parcels SET geom = ST_Transform(geom, 'EPSG:32633', 'EPSG:3857');

When a bootstrap run hits OOM despite the ceiling, fall back to chunked execution rather than raising memory_limit blindly: ingest and index partition-by-partition, or pre-aggregate to a coarse grid so each materialized intermediate stays small. Open the database file in read-only mode for downstream analytical consumers so a runaway query on one connection cannot corrupt the catalog:

-- Read-only attach for analytical consumers; writers use a separate service account.
ATTACH '/data/prod.duckdb' AS prod (READ_ONLY);

DuckDB is a single-process embedded engine with no row-level security, so isolation is filesystem-shaped: mount the .duckdb file read-only for consumers, restrict write access to one ingestion account, and put temp_directory on a dedicated volume — the same discipline the raster pipelines reuse when they build on this baseline.

Related #

See also:

• Memory limits for large raster data — the raster-specific spill threshold that builds directly on this hardened session.
• GeoParquet parsing in DuckDB Spatial — the columnar ingestion path the optimized pattern routes to.

Up: In-Memory vs Disk Storage in DuckDB · DuckDB Spatial Architecture & Fundamentals

---

External Reference Standards: Coordinate-transformation parameters follow the official PROJ documentation; geometry interchange and metadata validation follow the OGC GeoParquet specification.