DuckDB Spatial Analytical SQL for GIS

ST_MakeValid Repair Strategies

Calling ST_MakeValid is easy; deciding what should happen to the rows it changes beyond recognition is the actual work — this walkthrough, part of the geometry validity and repair reference, sets out when to repair, when to simplify instead, and when to reject, and how to tell those cases apart from a query rather than by eye.

Root-Cause Analysis: repairs that succeed and still cause damage

A repair that returns a valid geometry has not necessarily returned a useful one. Four outcomes are worth distinguishing, because they need different handling and only the first is unambiguously fine.

  • Faithful repair. The input had a small, local defect — one self-intersection, a ring closing a hair out — and the output has the same class, essentially the same area, and the same bounding box. This is the overwhelming majority on real data and needs no special handling at all.
  • Class change. The input’s edges genuinely describe more than one region, so a POLYGON becomes a MULTIPOLYGON. Valid, defensible, and silently fatal to any downstream filter on geometry type. Needs an explicit decision: accept the multipolygon, take its largest part, or reject the row.
  • Collapse. The input had zero or near-zero area — a sliver, a collinear “polygon” — and the repair returns a LINESTRING, a POINT, or an empty geometry. The row survives every validity check and disappears from every areal query. This is the outcome that produces counts nobody can reconcile.
  • Explosion. The input was a badly overlapping multipolygon, and re-noding produced hundreds of small parts where there were three. Valid, enormous, and now expensive in every subsequent operation. Worth catching by comparing vertex counts before and after.

The distinguishing signals are all comparisons between input and output rather than properties of the output alone, which is why a gate that only checks ST_IsValid(repaired) misses three of the four.

Four repair outcomes, told apart by comparing input with output Faithful repair, class change, collapse and explosion, distinguished by whether class, area and vertex count survive the repair. OUTCOME SIGNAL WHAT TO DO faithful repair class, area and bbox all survive nothing — accept it class change POLYGON → MULTIPOLYGON decide: accept, keep largest, reject collapse area → 0; class → line or point quarantine — it will vanish silently explosion vertex count multiplies simplify, or reject as unusable

Only the first row is safe to ignore, and it is the only one a validity check on its own can see.

Deterministic Configuration

Repair is the memory-hungry half of validity work, and it is hungry in proportion to how bad the input is rather than how large it is.

INSTALL spatial; LOAD spatial;

-- Re-noding a badly overlapping multipolygon can produce an output several
-- times the input. Size for the worst row, not the average one.
SET memory_limit = '8GB';
SET threads = 8;
SET temp_directory = '/var/tmp/duckdb_repair';
SET max_temp_directory_size = '40GB';

Optimized Execution Pattern

The naive repair replaces the geometry and moves on. The pattern that stays honest computes the repair once, compares it against the input, and routes on the comparison.

-- ANTI-PATTERN: repair and forget. Collapses and class changes pass silently.
UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);
-- PATTERN: repair once into a CTE, then classify the outcome by comparing
-- against the input. Every route is explicit and countable.
CREATE OR REPLACE TABLE parcels_repaired AS
WITH r AS (
    SELECT parcel_id, geom AS geom_in, ST_MakeValid(geom) AS geom_out
    FROM parcels WHERE NOT ST_IsValid(geom)
)
SELECT
    parcel_id,
    geom_out AS geom,
    CASE
        WHEN ST_GeometryType(geom_in) = ST_GeometryType(geom_out)
             AND ST_NPoints(geom_out) <= ST_NPoints(geom_in) * 3   THEN 'faithful'
        WHEN ST_Area(geom_out) < ST_Area(geom_in) * 0.001          THEN 'collapse'
        WHEN ST_NPoints(geom_out) > ST_NPoints(geom_in) * 3        THEN 'explosion'
        ELSE 'class_change'
    END AS outcome
FROM r;

-- The counts per outcome are the thing to look at, not the repair itself.
SELECT outcome, count(*) FROM parcels_repaired GROUP BY 1 ORDER BY 2 DESC;

The thresholds — three times the vertex count, a thousandth of the area — are conventions rather than laws, and they should be tuned once against a real dataset and then left alone. Their job is not to be exactly right but to separate “this repair did what I expected” from “this repair did something I should look at”, and any threshold in roughly the right place does that.

Diagnostic Queries & Plan Validation

The repair should appear once in the plan, not twice. Writing ST_MakeValid(geom) in both the SELECT list and the CASE expression computes it twice, and it is the expensive part of the statement.

-- Expect a single evaluation of ST_MakeValid, inside the CTE. If the plan
-- shows the function in both the projection and the filter, the CTE was
-- inlined and the repair is running twice per row.
EXPLAIN ANALYZE
WITH r AS (SELECT ST_MakeValid(geom) AS g FROM parcels WHERE NOT ST_IsValid(geom))
SELECT count(*) FILTER (WHERE ST_GeometryType(g) = 'MULTIPOLYGON') FROM r;

Diagnostic — area drift across the repair: total area is the summary statistic that most reliably distinguishes a benign repair pass from a damaging one, because it aggregates the collapses that individual row checks might be tuned to miss.

-- A repair pass should not move total area by more than a fraction of a
-- percent. A larger change means collapses, and the count tells you how many.
SELECT
    sum(ST_Area(geom_in))                              AS area_before,
    sum(ST_Area(geom_out))                             AS area_after,
    1 - sum(ST_Area(geom_out)) / sum(ST_Area(geom_in)) AS relative_loss
FROM (SELECT geom AS geom_in, ST_MakeValid(geom) AS geom_out
      FROM parcels WHERE NOT ST_IsValid(geom));
Repair, simplify, or reject ST_MakeValid for local defects, ST_SimplifyPreserveTopology for over-detailed geometry, and quarantine for degenerate input that no operation can recover. repair ST_MakeValid keeps the input’s edges use for local defects may change class or multiply the vertices simplify ST_SimplifyPreserveTopology removes vertices to a tolerance use when over-detailed validity often follows as a side effect; edges move reject route to quarantine with the reason attached use for degenerate input no operation recovers a shape that was never there The three are not a fallback chain. Which one is right depends on why the geometry is invalid, which is what the reason column is for. Four comparisons that turn a repair into a measurement Geometry type detects class change, area detects collapse, vertex count detects explosion, and the bounding box detects a repair that moved the shape. COMPARE DETECTS WHAT TO DO ABOUT IT ST_GeometryType before / after a class change decide: accept, reduce, reject ST_Area before / after a collapse quarantine — it will vanish ST_NPoints before / after an explosion simplify, or reject the bounding box a repair that moved the shape investigate — this should not happen

One extra projection each. Together they are the difference between repairing and knowing what you repaired.

Geometry Validation & Fallback Routing

For a class change, the most common resolution is to keep the largest part and record that a decision was made, which preserves the one-row-one-shape assumption most downstream code holds without silently discarding the row.

-- Keep the largest part of a repaired multipolygon, and record that the row
-- was reduced so the decision is visible rather than implicit.
SELECT
    parcel_id,
    CASE WHEN ST_GeometryType(g) = 'MULTIPOLYGON'
         THEN (SELECT part FROM (SELECT unnest(ST_Dump(g)).geom AS part)
               ORDER BY ST_Area(part) DESC LIMIT 1)
         ELSE g
    END AS geom,
    ST_GeometryType(g) = 'MULTIPOLYGON' AS was_reduced
FROM (SELECT parcel_id, ST_MakeValid(geom) AS g FROM parcels WHERE NOT ST_IsValid(geom));

Whether that is the right resolution depends entirely on the dataset. For administrative parcels, where a multipolygon almost always means a digitising artefact, keeping the largest part is nearly always correct. For land parcels that genuinely come in detached pieces, it destroys real data — and there the right answer is to accept the multipolygon and fix the downstream type filter instead.

Repairing at scale without repairing everything

On a table that is largely clean, the dominant cost of a repair pass is repairing the rows that did not need it. ST_MakeValid is not free even when its input is already valid — it still decomposes and reassembles — so a statement that applies it unconditionally does the expensive work on every row and discards it for the overwhelming majority.

The split-and-union form avoids that, and it has a second benefit that matters more on a large table: the two branches have different memory profiles, so the engine can schedule them independently rather than sizing the whole statement against the worst case.

-- Two branches, two profiles. The clean branch is a projection; the repair
-- branch is where the memory goes, and it now runs over a small fraction.
CREATE OR REPLACE TABLE parcels_out AS
SELECT parcel_id, geom, 'clean' AS state
FROM parcels WHERE ST_IsValid(geom)
UNION ALL
SELECT parcel_id, ST_MakeValid(geom), 'repaired'
FROM parcels WHERE NOT ST_IsValid(geom);

On a table where one row in five hundred is invalid, that is roughly two orders of magnitude less repair work for one extra scan — and the scan is the cheap half.

Frequently Asked Questions

Does ST_MakeValid preserve area?

Not exactly, and for a genuinely invalid shape it cannot — the input’s area was ambiguous, which is what “invalid” means. For a faithful repair the change is a fraction of a percent; for a collapse it goes to zero. Comparing total area before and after across a repair pass is the single most useful summary, because it aggregates the collapses that per-row checks may be tuned to let through.

When should I simplify instead of repair?

When the geometry is over-detailed for its purpose and the invalidity is a symptom of that detail — a coastline traced at centimetre precision that self-intersects in a dozen places. Simplifying to a tolerance appropriate to the output often produces a valid result as a side effect and makes everything downstream cheaper. When the shape is already at the right level of detail, simplification only loses information.

What should I do when a repair changes the geometry class?

Decide once, per dataset, and encode the decision. Keeping the largest part suits data where a multipolygon means a digitising artefact; accepting the multipolygon suits data where detached parts are real. What must not happen is leaving it implicit, because a downstream filter on POLYGON will then quietly drop the row.

Is the repair deterministic?

For a fixed input and a fixed GEOS version, yes. Across versions, or across a round trip that changed precision anywhere, it may not be — re-noding depends on exact coordinate comparisons. If reproducibility matters, snap to an explicit precision grid before repairing and record the GEOS version alongside the output.

Up: Geometry Validity and Repair