DuckDB Spatial Analytical SQL for GIS

DuckDB vs Sedona for Distributed Spatial Joins

A distributed spatial join pays for cluster startup, a spatial partitioning pass and a geometry shuffle before it evaluates a single predicate, and on any dataset a single machine can hold, that fixed overhead is the whole comparison. This walkthrough, part of the engine comparisons reference, sets out where the crossover between the two actually sits, which signals genuinely call for a cluster, and how to check the cheaper option properly before committing to the expensive one.

Root-Cause Analysis: why the crossover is higher than it feels

Teams reach for a distributed engine earlier than the data justifies, and five things explain it.

  • “Large” is calibrated to old hardware. A hundred gigabytes required a cluster when a large machine had thirty-two gigabytes of memory. A machine with a terabyte of memory and fast local storage is now an ordinary rental, and the threshold moved with it.
  • A slow job is assumed to need more machines. Most slow spatial jobs are missing an index or a two-stage predicate, and adding executors to a nested-loop join distributes the quadratic rather than removing it.
  • The fixed overhead is invisible in a benchmark. Cluster startup, partitioning and shuffle are frequently excluded from published timings, which makes the distributed engine look better at small sizes than it is.
  • Spatial partitioning is itself expensive. Two geometries can only be compared on the same executor, so the inputs have to be partitioned spatially first — a shuffle of large payloads that has no single-node equivalent.
  • The operational cost is discounted. A cluster is the largest ongoing commitment of any engine discussed on this site, and it is a commitment made for the peak workload rather than the median one.

The distinguishing question is whether the data fits on one machine, and the honest answer is usually yes.

What a distributed join pays before it computes Cluster startup, spatial partitioning, and a geometry shuffle all precede the predicate; on a single node none of them exist. STAGE DISTRIBUTED SINGLE NODE cluster startup seconds to tens of seconds milliseconds spatial partitioning pass a shuffle in its own right not needed moving geometry over the network large payloads, real time not needed the join predicate itself parallel across executors parallel across cores

The first three rows are the price of admission, and they are paid whatever the data size.

Deterministic Configuration 

-- Before concluding a single node cannot do it, configure it as though it
-- were meant to. The defaults are conservative for a shared laptop.
SET memory_limit = '400GB';                    -- on a machine that has it
SET threads = 32;
SET temp_directory = '/nvme/duckdb_spill';     -- local NVMe, not network storage
SET max_temp_directory_size = '3TB';
SET preserve_insertion_order = false;          -- lets large joins stream

Optimized Execution Pattern

The single-node attempt that decides the question has to be a fair one, which means a partitioned, spilling, two-stage join rather than a naive one.

-- ANTI-PATTERN: the naive attempt that "proves" a single node cannot cope.
-- No index, no envelope stage, everything resident — this fails on data a
-- properly written query handles comfortably.
SELECT a.id, b.id FROM huge_a a JOIN huge_b b ON ST_Intersects(a.geom, b.geom);
-- PATTERN: partition the work, use the envelope stage, and let it spill.
-- This is the query that decides whether a cluster is actually needed.
SET memory_limit = '400GB';
CREATE OR REPLACE TABLE matches AS
SELECT a.id AS a_id, b.id AS b_id
FROM read_parquet('s3://lake/a/**/*.parquet', hive_partitioning = true) a
JOIN read_parquet('s3://lake/b/**/*.parquet', hive_partitioning = true) b
  ON a.region = b.region                 -- partition-aligned, so the join is per region
 AND a.geom && b.geom                    -- envelope stage
WHERE ST_Intersects(a.geom, b.geom);     -- exact, on survivors

The a.region = b.region equality is doing the same job a distributed engine’s spatial partitioning does, without the shuffle: it restricts the join to pairs within the same partition, which is correct whenever the partitioning is coarser than any genuine match can span. Where matches can cross a partition boundary, the fix is to widen the partition rather than to distribute.

Three real signals for a cluster, and three false ones Exceeding the largest machine, an existing Spark pipeline, and Spark as the platform are real; "large" data, a slow job, and an availability requirement are not. SIGNAL GENUINE? WHY data exceeds the largest machine yes the only hard constraint already inside a Spark pipeline yes moving out costs more than it saves Spark is the platform yes a second engine is a cost “the data is large” (tens of GB) no that fits on one machine now the job takes hours no check for a missing index first high availability is required no run the single-node job twice

Only the first three survive scrutiny, and the first is the only hard one.

Checking the cheaper option properly

The comparison that matters is not against a naive single-node query but against a well-written one, and the difference between those two is routinely larger than the difference between a laptop and a cluster. Three things have to be true before a single-node attempt counts as evidence: an index or a partition-aligned key exists, the predicate is two-stage, and a spill target is configured on fast local storage. A query missing any of them is measuring the missing piece.

The second check is on the distributed side, and it is easily forgotten: measure the fixed overhead on a trivial query. Running SELECT count(*) on the cluster gives the floor below which no query can go, and comparing that against the single-node total for the real query frequently ends the discussion before any tuning happens.

Where the crossover actually sits Single node wins to about 100 GB, the two are comparable near a terabyte, and above a few terabytes only the cluster completes. DATASET SINGLE NODE (DuckDB) CLUSTER (Sedona) ~10 GB <1 min — wins comfortably most of a minute is startup ~100 GB a few minutes — still wins comparable once overheads count ~1 TB completes, spilling heavily comparable — parallelism finally pays >5 TB cannot complete the only option The crossover has moved upward as single machines have grown. It sits considerably higher than most teams assume.

Three of the four rows favour the machine you already have.

Diagnostic Queries & Plan Validation 

-- Is the single-node join actually completing, or thrashing? Non-empty spill
-- during the run is fine; spill that grows without the query progressing is not.
SELECT count(*) AS spill_files, sum(size) / 1e9 AS spill_gb
FROM duckdb_temporary_files();

Diagnostic — is the partition-aligned join doing its job? Compare the candidate pair count against the product of the inputs. If it is close to the product, the alignment is not restricting anything and the join is effectively unpartitioned.

SELECT (SELECT count(*) FROM huge_a) * (SELECT count(*) FROM huge_b) AS pairs_possible,
       (SELECT count(*) FROM huge_a a JOIN huge_b b
        ON a.region = b.region AND a.geom && b.geom)               AS pairs_after_pruning;

Geometry Validation & Fallback Routing

Where a single node genuinely cannot hold the join, the fallback before a cluster is to run it in bounded pieces. A partition-at-a-time loop, driven from the partition list, turns one unbounded join into many bounded ones — which is what a distributed engine does, minus the shuffle, at the cost of doing them sequentially.

# One partition at a time. Sequential rather than parallel, but each piece is
# bounded and restartable — and on a fast machine the total is often
# competitive with a cluster once the cluster's overheads are counted.
regions = [r[0] for r in con.execute(
    "SELECT DISTINCT region FROM read_parquet('s3://lake/a/**/*.parquet', hive_partitioning=true)"
).fetchall()]

for region in regions:
    con.execute(f"""
        COPY (
            SELECT a.id AS a_id, b.id AS b_id
            FROM read_parquet('s3://lake/a/region={region}/*.parquet') a
            JOIN read_parquet('s3://lake/b/region={region}/*.parquet') b
              ON a.geom && b.geom
            WHERE ST_Intersects(a.geom, b.geom)
        ) TO 's3://lake/matches/region={region}' (FORMAT PARQUET)
    """)

Frequently Asked Questions

When do I genuinely need Sedona?

When the data exceeds the largest machine you can reasonably rent, when the workload is already embedded in a Spark pipeline where moving out costs more than it saves, or when Spark is the organisation’s data platform and a second engine is an operational cost rather than a saving. Those three are real; “the data is large” on its own has not been sufficient for several years.

Is a cluster faster on a hundred gigabytes?

Usually not, once its fixed overheads are counted. Startup, spatial partitioning and the geometry shuffle happen before any predicate runs and do not shrink with the data. Measuring SELECT count(*) on the cluster gives you the floor those overheads impose, and comparing it against a well-written single-node total frequently settles the question.

My single-node join fails. Does that prove I need a cluster?

Only if the attempt was a fair one. Three things have to be true first: an index or a partition-aligned key, a two-stage predicate with an envelope test, and a spill target on fast local storage. A join missing any of those fails on data that a properly written one handles comfortably.

What replaces spatial partitioning on a single node?

Nothing needs to replace it, because there is no shuffle to arrange — the data is already where the computation is. What is worth borrowing is the idea of restricting the join to aligned partitions, expressed as an equality on the partition key alongside the envelope test, which prunes the same pairs a distributed partitioner would.

Can the two work together?

Yes, and it is a common arrangement: Spark owns the very large upstream transformations, writes GeoParquet, and DuckDB queries that output for analysis. The format is the interface, so neither engine has to know about the other, and the analytical side gets instant startup without the cluster having to stay up for it.

How do I decide without building both?

Measure two numbers. The first is the well-configured single-node runtime for your real query. The second is the cluster’s fixed overhead on a trivial one. If the first is smaller than the second, no amount of distributed parallelism will help, and the decision is made without building anything.

Up: DuckDB Spatial vs PostGIS, GeoPandas and Sedona