DuckDB Spatial Analytical SQL for GIS

Detecting Invalid Geometry at Ingest

The cheapest moment to find a broken polygon is the moment it arrives, before it has been indexed, joined, dissolved and copied into three derived tables — this walkthrough, part of the geometry validity and repair reference, sets out the ingest-time gate: what to check, what it costs, where to put it in the load, and how to report the result so that a failure names its own cause instead of appearing three weeks later as a total that will not reconcile.

Root-Cause Analysis: why invalidity arrives at ingest

Invalid geometry is almost never generated by DuckDB. It arrives, and it arrives from a small number of recurring sources, each of which produces a characteristic failure.

  • Digitising artefacts. Hand-traced boundaries — historical parcels, coastlines, land-use surveys — accumulate self-intersections at every place where the operator’s hand wobbled back over a line already drawn. These present as Self-intersection and are the largest single category in any dataset with a manual origin.
  • Format round-trips through reduced precision. A geometry exported to a text format at six decimal places and re-imported has had its vertices moved. Rings that closed exactly now close approximately, and edges that met at a point now cross fractionally. This class is invisible in the source system and appears only after the trip.
  • Coordinate transformation. Reprojecting a shape moves every vertex independently, so a polygon that was valid in one frame can become self-intersecting in another — most often near the projection’s edge cases, where adjacent vertices map to nearly the same point. A transform is a place to re-check, not a place to assume validity carries over.
  • Merges of overlapping sources. Two surveys of the same area, unioned into one layer without reconciliation, produce multipolygons whose parts overlap. GEOS treats overlapping parts of a multipolygon as invalid, and the resulting interior is undefined.
  • Genuinely degenerate features. A parcel recorded as a strip one centimetre wide, or a “polygon” whose three vertices are collinear, has zero or near-zero area. These sometimes validate and sometimes do not, depending on precision, which makes them the most annoying category to handle consistently.

The distinguishing question is whether ST_IsValidReason returns the same reason across most failures — a single dominant reason points at a systematic cause in the source or the transfer, while a scatter of different reasons points at genuinely dirty source data.

Four places to put the gate, and what each one is worth In the source read it catches nothing; after staging it catches everything for one pass; before each operation it is narrower; after the derived tables it is too late to be a fix. WHERE THE GATE SITS COST VERDICT inside the source read none wrong place — nothing to check yet right after staging one pass, per load the right default before each set operation one pass, per operation narrower, and easy to forget after the derived tables one pass, too late a rebuild, not a fix

One pass in the second row buys a guarantee every later query can rely on.

Deterministic Configuration

The gate is a scan, so it needs almost nothing beyond a sensible memory ceiling. What it does need is a precision decision made before it runs, because that decision changes the answer.

INSTALL spatial; LOAD spatial;

-- The gate is a scan; it does not inflate. This ceiling is for the repair
-- that follows it, not for the check itself.
SET memory_limit = '6GB';
SET threads = 8;
SET temp_directory = '/var/tmp/duckdb_ingest';

Confirm these before running the gate on a production load:

Optimized Execution Pattern

The naive gate computes ST_IsValid twice — once to count and once to filter — and computes ST_MakeValid on every row including the clean ones. On a table that is 99% clean, that is roughly an order of magnitude of wasted work.

-- ANTI-PATTERN: validity computed repeatedly, repair computed for everyone.
SELECT count(*) FROM staging WHERE NOT ST_IsValid(geom);          -- pass 1
CREATE TABLE clean AS
  SELECT *, ST_MakeValid(geom) AS fixed FROM staging;             -- repairs all rows
-- PATTERN: one pass produces the summary, and repair touches only the
-- rows that failed. The FILTER clause keeps it to a single scan.
CREATE OR REPLACE TABLE ingest_report AS
SELECT
    count(*)                                        AS rows_total,
    count(*) FILTER (WHERE NOT ST_IsValid(geom))    AS rows_invalid,
    count(*) FILTER (WHERE geom IS NULL)            AS rows_null,
    count(*) FILTER (WHERE ST_IsEmpty(geom))        AS rows_empty,
    min(ST_XMin(geom))                              AS min_x,
    max(ST_XMax(geom))                              AS max_x
FROM staging;

-- Repair only the failures, then union back. On a mostly-clean table this
-- is far cheaper than repairing everything and discarding the work.
CREATE OR REPLACE TABLE parcels AS
SELECT parcel_id, geom, 'clean' AS state FROM staging WHERE ST_IsValid(geom)
UNION ALL
SELECT parcel_id, ST_MakeValid(geom), 'repaired' FROM staging WHERE NOT ST_IsValid(geom);

The min_x / max_x columns in the report are not about validity at all, and they belong there anyway: a coordinate range that lands inside plus or minus 180 when the layer is supposed to be projected is a frame defect, and the ingest report is the natural place to catch it alongside everything else that has to be true about a fresh load.

Diagnostic Queries & Plan Validation

The gate’s plan should be a single scan with an aggregate above it. Two shapes indicate a problem.

-- Expect: one scan, one aggregate. Two scans means the FILTER clauses were
-- written as separate queries; a PROJECTION containing ST_MakeValid means
-- repair is running before the check has decided who needs it.
EXPLAIN ANALYZE
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) FROM staging;

Diagnostic — failure reasons, grouped: a raw count of invalid rows tells you there is a problem; the grouped reason tells you which one, and whether it is systematic.

-- One dominant reason means a systematic cause (a transform, a round-trip,
-- a merge). A scatter of reasons means genuinely dirty source data.
SELECT
    regexp_extract(ST_IsValidReason(geom), '^[A-Za-z ]+') AS reason,
    count(*)                                              AS n,
    min(parcel_id)                                        AS example_id
FROM staging
WHERE NOT ST_IsValid(geom)
GROUP BY 1
ORDER BY n DESC;
Reading the shape of an ingest report One dominant reason means a systematic upstream cause; a scatter means dirty source data; a sudden jump means the source changed; slow growth is normal. WHAT THE REPORT LOOKS LIKE WHAT IT MEANS, AND WHERE THE FIX IS one reason dominates systematic — fix the transform or the round-trip many reasons, none dominant dirty source — repair and quarantine zero last load, large this load the source changed — check the export process slow growth every load normal for a live dataset — track, do not fail What belongs in an ingest report, and what each column detects Row totals, null and empty counts, the validity failure count, and the coordinate range — five aggregates in one scan, each catching a different class of defect. COLUMN WHAT A SURPRISING VALUE MEANS CLASS OF DEFECT rows_total the baseline for everything else transfer completeness rows_null records arrived without a shape source problem rows_empty shapes describing no space upstream operation rows_invalid genuine topology defects geometry problem min_x / max_x inside ±180 means still degrees coordinate frame

Five aggregates, one scan, and only one of them is about validity.

Geometry Validation & Fallback Routing

The gate’s fallback behaviour matters more than its detection. A gate that aborts the load on the first bad feature means one bad parcel in one municipality stops a national dataset from loading at all. A gate that drops bad rows silently means the load succeeds and the totals are wrong. The three-way route — pass, repair, quarantine — is the only arrangement that both completes and stays honest.

-- Reduce precision first: much apparent invalidity is floating-point noise
-- rather than topology error, and snapping removes that class outright so
-- the quarantine holds only failures worth a human's attention.
CREATE OR REPLACE TABLE staging_snapped AS
SELECT * REPLACE (ST_ReducePrecision(geom, 0.001) AS geom)   -- millimetre grid
FROM staging;

-- Then the three-way route, with the class check that stops a repair from
-- silently turning polygons into linestrings.
CREATE OR REPLACE TABLE quarantine AS
SELECT parcel_id, geom, ST_IsValidReason(geom) AS reason
FROM staging_snapped
WHERE NOT ST_IsValid(geom)
  AND (NOT ST_IsValid(ST_MakeValid(geom))
       OR ST_GeometryType(ST_MakeValid(geom)) <> ST_GeometryType(geom));

If the quarantine table is large enough to matter, the question is no longer a pipeline question — it is a data-supply question, and the report grouped by reason is what makes that conversation concrete.

Frequently Asked Questions

How much does the gate cost?

On a mixed parcel layer, roughly 3–6% of a full scan — comparable to computing a bounding box per row, because that is broadly what the check does before it looks at anything harder. The repair that may follow is where the variance lives, and running it only on the rows that failed keeps it proportional to how dirty the data actually is.

Should I snap precision before or after checking validity?

Before. A large share of apparent invalidity is floating-point noise — rings closing a nanometre out, vertices “duplicated” at the fifteenth decimal — and snapping to a millimetre grid removes that class outright. What remains is genuine topology error, which is both rarer and worth a human looking at.

What should go in the quarantine table?

The identifier, the original geometry, and ST_IsValidReason output. The reason is what turns the quarantine from a bin into a work queue: it groups, it points at systematic causes, and it tells whoever reviews the rows whether they are looking at one problem or forty.

Does this catch a wrong coordinate reference system too?

Not directly, but the same report is the right place for the check. Add the coordinate range to the ingest summary: a layer that is supposed to be projected but whose x values all fall inside plus or minus 180 is still in degrees, and that defect is at least as damaging as an invalid ring and just as silent.

Up: Geometry Validity and Repair