DuckDB Spatial Analytical SQL for GIS

Aggregating Geometries with ST_Union_Agg

Dissolving a layer is the one aggregation whose output can be larger than its input, which makes it the operation most likely to exhaust a memory limit and the one where the grouping key matters more than the geometry. This walkthrough, part of the vectorized aggregations reference, covers why an ungrouped union fails, how to bound each merge, and the three preparation steps that decide whether the result is correct as well as affordable.

Root-Cause Analysis: why a dissolve exhausts memory

  • The output is a new, larger shape. A union of a thousand adjacent polygons produces one polygon whose boundary is the union of every edge that survives the merge. Unlike a sum, the result does not fit in a fixed accumulator.
  • Every intermediate is held. The merge proceeds incrementally, and each partial result is a geometry that must exist while the next is computed. An ungrouped union over a large table holds a very large partial for most of its run.
  • Invalid input poisons the merge. A single self-intersecting ring makes the union either fail or return a subtly wrong shape, and which of the two you get depends on the GEOS version rather than on your data.
  • Floating-point drift creates slivers. Two polygons that share an edge in intent but not in the last binary digit produce a hairline gap or overlap in the output, and a dissolve over a whole layer produces thousands of them.
  • The grouping key is missing. Without one there is exactly one merge and it is unbounded. With one there are many merges and each is bounded by its group, which is the entire difference between a dissolve that completes and one that does not.

The distinguishing question is whether any single merge is bounded. A dissolve with a grouping key is a set of small problems; without one it is a single large one.

One merge or forty, on identical inputs Ungrouped: one unbounded 900 MB intermediate on one core. Grouped by zone: forty 20 MB intermediates in parallel. PROPERTY NO GROUPING KEY GROUPED BY ZONE number of merges 1 40 largest intermediate ~900 MB ~20 MB parallelism one group, one core across cores under an 8 GB limit exhausts it comfortable

Identical inputs and identical output geometry. Only the largest thing held at once differs.

Deterministic Configuration 

INSTALL spatial; LOAD spatial;

-- A dissolve inflates. Size for the largest group, not the average one, and
-- give it somewhere to spill because the largest group is always bigger than
-- you estimated.
SET memory_limit = '8GB';
SET threads = 8;
SET temp_directory = '/var/tmp/duckdb_dissolve';
SET max_temp_directory_size = '60GB';

Optimized Execution Pattern

The pattern is three preparation steps and then a grouped aggregate. Skipping any of the three produces a result that is affordable and wrong.

-- ANTI-PATTERN: one unbounded merge over unvalidated, full-precision input.
-- It will either exhaust memory or return a shape full of slivers.
SELECT ST_Union_Agg(geom) AS outline FROM parcels;
-- PATTERN: validate, snap, group. Each merge is bounded by its zone, the
-- input is clean, and shared edges actually coincide.
CREATE OR REPLACE TABLE zone_outlines AS
SELECT zone_id,
       ST_Union_Agg(ST_ReducePrecision(geom, 0.001)) AS outline,   -- millimetre grid
       count(*)                                      AS parcels
FROM parcels
WHERE ST_IsValid(geom)                                            -- one bad ring is enough
GROUP BY zone_id;

-- Only now merge the handful of zone outlines, if a single shape is wanted.
SELECT ST_Union_Agg(outline) AS city_outline FROM zone_outlines;

The two-stage merge at the end matters as much as the grouping. Forty zone outlines merge in a fraction of a second because each is already simplified by its own dissolve; the same forty merges attempted over 1.2 million individual parcels is the operation that failed.

Three preparations, three failures prevented Validity filtering stops a poisoned merge, precision reduction stops slivers, and a grouping key stops the merge being unbounded. PREPARATION PREVENTS COST WHERE ST_IsValid(geom) a poisoned or failed merge one scan predicate ST_ReducePrecision hairline slivers in the output a stated tolerance GROUP BY a real key an unbounded merge nothing — it is usually wanted anyway

One clause each, and all three change the answer rather than only the runtime.

Why ST_Union_Agg beats an iterative merge

It is tempting to express a dissolve as a loop or a recursive CTE that merges one geometry at a time into an accumulator. That is quadratic in the worst case: each step copies a growing result, so merging N shapes copies roughly N squared over two vertices in total.

ST_Union_Agg builds a balanced merge tree internally instead, combining pairs and then pairs of pairs. The total vertex work is proportional to N log N rather than N squared, and the largest intermediate is bounded by half the group rather than by all of it. That is why the aggregate form is not merely more idiomatic but categorically cheaper, and why a hand-rolled merge tends to look fine on a hundred shapes and fail on a hundred thousand.

Diagnostic Queries & Plan Validation

Two numbers tell you whether a dissolve is affordable before you run it, and both are cheap.

-- Largest group by vertex count, which is what the merge actually costs.
-- Row count per group is a poor proxy when the geometries vary in size.
SELECT zone_id,
       count(*)                AS parcels,
       sum(ST_NPoints(geom))   AS vertices
FROM parcels
GROUP BY zone_id
ORDER BY vertices DESC
LIMIT 5;

If the largest group holds a large fraction of the total vertices, the grouping key is too coarse and the dissolve is effectively ungrouped for that zone. Splitting the dominant group on a second key restores the bound.

Four dissolve symptoms and their causes OOM means unbounded; slivers mean drift; an exception means invalid input; one slow group means skew. SYMPTOM CAUSE FIX out of memory an unbounded merge group, or group more finely hairline gaps or overlaps floating-point drift ST_ReducePrecision first an exception inside the union invalid input geometry filter on ST_IsValid one group far slower than the rest vertex skew split that group

Four symptoms, four different fixes, and none of them is a bigger memory limit.

Choosing the precision tolerance

The tolerance is a decision about what the output is for, and it should be stated once and applied consistently rather than tuned until the slivers disappear. A dissolve intended for cartography at 1:25,000 does not need millimetre precision and is substantially cheaper without it; one intended to feed a legal boundary calculation may need every digit the source carries and should accept the slivers as real disagreements to investigate rather than snap them away.

The failure to avoid is applying different tolerances at different stages of the same pipeline, which produces boundaries that agree in one place and disagree in another for reasons nobody can reconstruct. Pick a tolerance per dataset, record it, and apply it at ingest so every downstream operation inherits it.

Geometry Validation & Fallback Routing

Where one group is genuinely too large to merge, split it deterministically rather than raising the limit.

-- Split the dominant group on a grid, dissolve each piece, then merge the
-- pieces. The result is identical; the largest intermediate is bounded.
WITH tiled AS (
    SELECT zone_id,
           floor(ST_X(ST_Centroid(geom)) / 5000)::INT AS tx,
           floor(ST_Y(ST_Centroid(geom)) / 5000)::INT AS ty,
           ST_ReducePrecision(geom, 0.001) AS geom
    FROM parcels WHERE ST_IsValid(geom)
), per_tile AS (
    SELECT zone_id, tx, ty, ST_Union_Agg(geom) AS part FROM tiled GROUP BY 1, 2, 3
)
SELECT zone_id, ST_Union_Agg(part) AS outline FROM per_tile GROUP BY zone_id;

Frequently Asked Questions

Why does my dissolve run out of memory?

Because it has no grouping key, so there is one merge and its largest intermediate is the whole result. Adding a key turns one unbounded problem into many bounded ones, and it usually turns a single-threaded operation into a parallel one at the same time.

What is the difference between ST_Union and ST_Collect?

ST_Collect bundles shapes into a collection without any topology work, which is cheap and lossless. ST_Union dissolves shared boundaries and produces a genuinely new shape, which is expensive. If the goal is to carry shapes together, collect; if it is to remove internal edges, union.

Why are there hairline slivers in my output?

Floating-point drift. Two polygons that share an edge in intent differ in the last binary digits, so the union leaves a gap or an overlap a few nanometres wide. Snapping both to a common precision grid with ST_ReducePrecision before the merge removes the whole class.

Should I validate before dissolving?

Always. One self-intersecting ring makes the entire group either fail or return a subtly wrong shape, and which of those you get depends on the GEOS version rather than on your data. A WHERE ST_IsValid(geom) is one predicate and it removes an entire category of irreproducible result.

Is a recursive merge ever better than the aggregate?

No. An iterative merge copies a growing accumulator at every step, which is quadratic in total vertex work; the aggregate builds a balanced merge tree, which is N log N. The hand-rolled version looks fine on a hundred shapes and fails on a hundred thousand.

One group takes far longer than the rest. Why?

Vertex skew: that group holds most of the vertices even if it does not hold most of the rows. Check sum(ST_NPoints(geom)) per group rather than the row count, and split the dominant one on a grid — the result is identical and the largest intermediate is bounded again.

Up: Vectorized Aggregations in DuckDB Spatial