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.

Frequently Asked Questions #

Do I need to run INSTALL every time? #

No — INSTALL writes the extension to a local directory and is permanent, while LOAD is per session and must run on every new connection. That asymmetry is behind most reports of spatial functions vanishing between sessions: the install is fine, the load was forgotten.

Where should I put my session settings? #

In an init file the CLI reads on startup, so interactive sessions are configured identically to scripted ones. Relying on typing them each time guarantees that the one session where you forget is the one that fills a disk or gets OOM-killed. In Python the equivalent is a small helper that opens the connection and applies the settings before returning it.

Should I work in an in-memory or file-backed database? #

File-backed for anything you want to keep, including indexes — an R-tree built in an in-memory database is rebuilt from scratch on every run, which for a large table is minutes each time. In-memory is right for a scratch session over external Parquet files, where nothing is meant to survive.

Can I query files without loading them into a table? #

Yes, and it is often the right thing to do — read_parquet and st_read are table functions, so a query can read directly from a file or a glob without a CREATE TABLE step. Loading pays off when you want an index, when the same data is scanned many times in one session, or when the source is remote and you want to fetch it once.

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.