Geometry Validity and Repair in DuckDB Spatial

An invalid geometry is not a geometry that fails to load — it loads perfectly, indexes perfectly, and returns rows from every predicate you point at it. What it does instead is answer differently from the shape you think it describes, and it poisons every set operation it participates in. This page sits inside the DuckDB Spatial architecture and fundamentals reference and covers one decision in depth: where in a pipeline validity should be enforced, what ST_MakeValid actually does to a shape, and how to build a gate that quarantines the geometry it cannot fix rather than letting a single bad ring abort a batch or, worse, survive it.

The Simple Features rules that define validity are narrow and unintuitive. A polygon must have a closed exterior ring, no self-intersections, interior rings entirely inside the exterior and not crossing each other, and a consistent ring orientation. Real data violates all of them routinely — a digitised coastline that touches itself at a headland, a parcel boundary whose ring closes one micrometre away from where it started, a multipolygon whose parts overlap because two surveyors traced the same field. None of that stops the data being stored, transferred or displayed. It stops it being computed with.

Runtime Configuration & Memory Guardrails #

Validity checking is cheap; repair is not. ST_IsValid walks the vertices once and short-circuits on the first violation, so it costs about the same as a bounding-box computation. ST_MakeValid may decompose a shape, re-node its edges, and reassemble it, which for a self-intersecting multipolygon can produce an output several times the size of the input. Configure the session for the repair, not for the check.

INSTALL spatial; LOAD spatial;

-- Repair inflates geometry: a re-noded multipolygon can exceed its input
-- several times over. Size the ceiling for the worst shape in the table,
-- not the average one.
SET memory_limit = '8GB';

-- Physical cores. Validity work is per-row and parallelises cleanly, but the
-- kernels are SIMD-heavy so hyperthread siblings contend rather than add.
SET threads = 8;

-- A repair pass over a dirty table will spill. Give it somewhere fast to go
-- rather than discovering the limit halfway through.
SET temp_directory = '/var/tmp/duckdb_validity';
SET max_temp_directory_size = '40GB';

Trade-off Analysis: Running validity checks at ingest costs one extra pass over every row, on every load, forever. Running them lazily — only when a set operation fails — costs nothing until it costs you a corrupted output that nobody notices for a month. The cheap pass is the right default; the only case for skipping it is a source you control end to end and have already validated upstream, and even there the check is worth keeping as an assertion that the upstream guarantee still holds.

The two rows that error are the cheap ones. The two that answer are what this page exists for.

Primary Execution Pattern: gate, repair, quarantine #

The pattern that survives production is three-way rather than binary. A binary pass-or-fail gate either drops rows silently — producing counts nobody can reconcile — or aborts the batch on the first bad feature, which means a national dataset never loads because of one bad parcel in one municipality. The three-way gate passes what is valid, repairs what is repairable, and routes the rest to a quarantine table with the reason attached, so the batch completes and the exceptions are a work queue rather than an outage.

-- One pass, three destinations. ST_IsValidReason gives a human-readable
-- explanation that makes the quarantine table actionable rather than a bin.
CREATE OR REPLACE TABLE parcels_clean AS
WITH checked AS (
    SELECT *,
           ST_IsValid(geom)                  AS ok,
           ST_MakeValid(geom)                AS repaired
    FROM parcels_raw
)
SELECT parcel_id, zone_id,
       CASE WHEN ok THEN geom ELSE repaired END AS geom,
       CASE WHEN ok THEN 'clean' ELSE 'repaired' END AS validity_state
FROM checked
WHERE ok OR (ST_IsValid(repaired) AND ST_GeometryType(repaired) = ST_GeometryType(geom));

CREATE OR REPLACE TABLE parcels_quarantine AS
WITH checked AS (
    SELECT *, ST_IsValid(geom) AS ok, ST_MakeValid(geom) AS repaired
    FROM parcels_raw
)
SELECT parcel_id, geom,
       ST_IsValidReason(geom) AS failure_reason,
       ST_GeometryType(repaired) AS repaired_as
FROM checked
WHERE NOT ok
  AND (NOT ST_IsValid(repaired) OR ST_GeometryType(repaired) <> ST_GeometryType(geom));

The type check in the WHERE clause is the part that is easy to leave out and expensive to omit. ST_MakeValid is allowed to change the geometry class: a self-intersecting polygon whose lobes touch at a single point becomes a MULTIPOLYGON, and a degenerate polygon with zero area can come back as a LINESTRING or a GEOMETRYCOLLECTION. A repair that silently turns 40 polygons into linestrings will pass ST_IsValid and then vanish from every polygon predicate downstream, which looks exactly like a data loss with no error to trace.

What ST_MakeValid actually does #

The function does not “fix” a shape in the sense of restoring an intended geometry — it cannot know what you intended. It produces a valid geometry whose boundary is derived from the input’s edges, using a structured approach: decompose the input into its constituent line segments, re-node them so that every intersection becomes an explicit vertex, then reassemble the pieces into rings and classify each resulting area as interior or exterior. The output is valid by construction and bears a defensible relationship to the input, but it is not necessarily the shape a human would have drawn.

Valid by construction, faithful to the input’s edges, and not necessarily the shape anyone meant.

Two behaviours follow from that construction and are worth internalising. First, area is not preserved — the bow tie’s two lobes have a combined area that differs from what a naive traversal of the original ring would report, and that is the point rather than a defect. Second, repair is not idempotent in the way you might expect across formats: repairing, exporting to WKB, re-importing and repairing again can produce a byte-different result if precision changed anywhere in the loop. Snap to an explicit precision grid before repairing if you need reproducibility.

Execution Plan Validation #

A validity gate should be visible in the plan as a cheap filter over a scan, not as a bottleneck. ST_IsValid is a scalar function with no index support — there is no way to look up “the invalid rows” — so the gate is always a full scan, and its cost should be roughly proportional to total vertex count.

-- The gate should read as a projection plus a filter over one scan.
-- If ST_MakeValid appears in the scan rather than above the filter, every
-- row is being repaired before the check decides whether it needed to be.
EXPLAIN ANALYZE
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_rows,
       count(*)                                     AS total_rows
FROM parcels_raw;

Diagnostic — repair running on clean rows: the pattern above computes ST_MakeValid(geom) in a CTE for every row, including the 99.8% that are already valid, because the CTE has no way to know which ones will need it. On a large clean table that is a substantial waste. The fix is to split the pass: identify the invalid rows first, repair only those, and union the result back.

-- Repair only what needs repairing. On a table that is 99% clean this is
-- roughly an order of magnitude cheaper than repairing everything and
-- discarding most of the work.
CREATE OR REPLACE TABLE parcels_clean AS
SELECT parcel_id, zone_id, geom, 'clean' AS validity_state
FROM parcels_raw WHERE ST_IsValid(geom)
UNION ALL
SELECT parcel_id, zone_id, ST_MakeValid(geom), 'repaired'
FROM parcels_raw WHERE NOT ST_IsValid(geom);

Performance Trade-offs #

The check itself is close to free relative to anything else in a spatial pipeline — on a mixed parcel layer it costs roughly 3–6% of a full scan, comparable to computing a bounding box per row. Repair is where the variance lives, and it is driven entirely by how bad the input is rather than by how much of it there is. A table with a handful of simple self-intersections repairs in seconds; a table of hand-digitised historical boundaries where a third of the rows have overlapping interior rings can take longer to repair than to load.

The second trade-off is where in the pipeline the gate sits. Validating at ingest costs one pass per load and gives every downstream query a guarantee it can rely on. Validating before each set operation costs a pass per operation and gives the same guarantee more narrowly. Validating nowhere costs nothing and gives no guarantee at all, which is fine right up until an overlay produces a shape with a sliver in it that nobody notices for a quarter.

Trade-off Analysis: Reducing precision with ST_ReducePrecision before validation is often the highest-yield preprocessing step available, because a large share of real-world invalidity is floating-point noise — rings that fail to close by a nanometre, vertices that are “duplicated” only at the fifteenth decimal place. Snapping to a millimetre grid eliminates that class outright and makes the remaining failures genuine topology problems worth looking at. The cost is that you have decided, irrevocably, that a millimetre does not matter for this dataset.

The gate belongs between the second row and the third, which is exactly where ingest sits.

Edge Cases & Anti-Patterns #

Repairing without checking the resulting class. Covered above and worth repeating because it is the single most common way a validity gate causes a silent data loss. Always compare ST_GeometryType before and after.

Treating ST_Buffer(geom, 0) as a repair. It sometimes works, which is why the habit persists. It is not a repair function; it is a buffer of radius zero whose implementation happens to re-node edges as a side effect. It silently drops parts of multipolygons, collapses narrow slivers, and behaves differently across GEOS versions. Use ST_MakeValid, which is the function that exists for this.

Validating after the join rather than before. An invalid geometry in a join predicate does not error — it produces matches that are wrong in ways proportional to how invalid it is. By the time the output looks odd, the join has already happened and the wrongness is distributed across the result. The gate belongs upstream of anything that computes.

A quarantine table nobody reads. A three-way gate that routes failures somewhere and never revisits them is a two-way gate with extra steps. The quarantine needs an owner and a review cadence, or the pipeline is silently dropping rows on a schedule.

Assuming a source is clean because it came from a database. PostGIS may have accepted geometry that GEOS in DuckDB rejects during an overlay, because the two systems check different things at different moments. A migration is exactly where invalid geometry surfaces for the first time, which the PostGIS to DuckDB migration guide treats as one of its four mandatory checkpoints.

Query Regression Analysis #

Validity is a property that should be asserted continuously rather than checked once, because every new load is a new opportunity to introduce it. The cheapest useful form is a small summary captured per table per load and compared against the previous run — a count of invalid rows, a count of repairs, and a breakdown by failure reason.

import duckdb

con = duckdb.connect("gis.duckdb")
con.execute("LOAD spatial")

def validity_snapshot(table: str, geom: str = "geom") -> dict:
    """One row per failure reason, plus the totals. Cheap enough to run on
    every load; specific enough that a change names its own cause."""
    rows = con.execute(f"""
        SELECT
            coalesce(regexp_extract(ST_IsValidReason({geom}), '^[A-Za-z ]+'), 'valid') AS reason,
            count(*) AS n
        FROM {table}
        GROUP BY 1 ORDER BY n DESC
    """).fetchall()
    return {reason: n for reason, n in rows}

before = validity_snapshot("parcels_raw")
# … reload the table …
after = validity_snapshot("parcels_raw")

# A new reason appearing, or an existing count growing by more than a small
# margin, means the upstream source changed — not that the pipeline broke.
for reason, n in after.items():
    prior = before.get(reason, 0)
    assert n <= max(prior * 1.1, prior + 25), f"validity regression: {reason} {prior} → {n}"

The assertion above deliberately allows small growth: real datasets acquire a few new bad geometries every load, and a gate that fails on any increase gets disabled within a week. What it catches is the change of kind — a new failure reason appearing, or an existing one jumping by an order of magnitude — which is what a genuine upstream change looks like.

Where validity sits relative to the CRS decision #

The two most damaging silent defects in a spatial pipeline are an invalid geometry and a wrong coordinate frame, and they interact in a way that is worth stating explicitly: reprojection can create invalidity, and invalidity can survive reprojection unchanged. A transform moves every vertex independently, so two edges that met at a point in the source frame can cross fractionally in the target one — most often where the projection is least well-conditioned, near its edges or its poles. That means a validity gate placed only at ingest, before the reprojection, has checked a shape that no longer exists.

The ordering that survives is: ingest, snap precision, reproject, validate, index. Snapping before the transform removes the floating-point noise that the transform would otherwise amplify; validating after it checks the geometry that downstream queries will actually use; and indexing last means the R-tree is built over shapes that have already been fixed rather than over ones that will be replaced. Reversing any two of those steps produces a pipeline that works on clean data and fails quietly on real data.

-- The order that holds. Each step is cheap; the sequence is what matters.
CREATE OR REPLACE TABLE parcels AS
SELECT parcel_id, zone_id,
       ST_MakeValid(                                    -- 4. validate the result
         ST_Transform(                                  -- 3. reproject
           ST_ReducePrecision(geom, 0.001),             -- 2. snap the noise away
           'EPSG:4326', 'EPSG:27700')) AS geom
FROM staging;                                           -- 1. ingested as-is

CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom);   -- 5. index last

Trade-off Analysis: Wrapping four functions around a column in one statement is compact and hides where the cost is. On a large table it is worth materialising each stage instead, so EXPLAIN ANALYZE attributes the time and so a failure names its own step. The single-statement form is right for a small table and for documentation; the staged form is right for anything that will be re-run.

Frequently Asked Questions #

Does an invalid geometry make a query fail? #

Usually not, and that is the problem. Predicates evaluate, indexes build, and rows come back — they are simply the wrong rows, by an amount proportional to how invalid the shape is. The failures that do error tend to come later, inside a union or an intersection, at which point the offending row is several transformations away from where it entered.

Is ST_Buffer with a distance of zero a valid repair technique? #

No, though it often appears to work. It is a buffer whose implementation re-nodes edges as a side effect, so it drops parts of multipolygons, collapses slivers, and behaves differently across GEOS versions. ST_MakeValid is the function written for this job and it states what it is doing.

Why did ST_MakeValid change my polygon into a multipolygon? #

Because the repair reassembles the input’s edges into whatever set of valid rings they actually describe, and a self-intersecting polygon describes two. The row is still there and still valid, but it no longer matches a predicate that filters on POLYGON. Always compare the geometry type before and after a repair, and decide explicitly what to do when it changes.

Should I validate at ingest or before each operation? #

At ingest, as a rule, because it gives every downstream query a guarantee for the price of one pass per load. Validating before each set operation is defensible when loads are frequent and overlays are rare, but it distributes the check across many places where it can be forgotten, whereas ingest is one place that always runs.

Can I index a table that contains invalid geometry? #

Yes, and that is part of why the problem is quiet. An R-tree is built over bounding boxes, and an invalid shape has a perfectly good bounding box. The index will be built, will be used, and will return candidates that then fail exact topology in ways that depend on the nature of the invalidity.

Does reducing precision fix invalidity? #

It fixes a large share of it, because much real-world invalidity is floating-point noise rather than genuine topology error — rings closing a nanometre away, vertices duplicated at the fifteenth decimal. Snapping to a millimetre or centimetre grid with ST_ReducePrecision before validating removes that class and leaves the failures that are worth a human’s attention.

Related #

See also

• Detecting invalid geometry at ingest — the gate query, its cost, and where to put it.
• ST_MakeValid repair strategies — choosing between repair, simplify, and reject.
• Handling null and empty geometries — the third state that is neither valid nor invalid.
• Spatial indexing internals — why an index over invalid geometry builds happily and helps nothing.
• GeoJSON ingestion — where the quarantine pattern first earns its keep.

Up: DuckDB Spatial Architecture & Fundamentals