Detecting Invalid Geometries at Scale

Problem Statement

A national cadastre table holds forty-one million polygons and nobody knows how many are invalid, because the only tool anyone has tried — SELECT count(*) FROM parcels WHERE NOT ST_IsValid(geom) — has never finished. This page, part of geometry validity and repair, builds an audit that does finish: incremental, resumable, safe to run during business hours, and cheap enough to keep permanently current afterwards.

Why the Naive Approach Fails

One long statement against many short ones Two runs of the same audit. The single statement runs for six hours in one transaction, blocks autovacuum for the duration, and loses everything if interrupted at hour five. The batched run commits every two thousand rows, so vacuum keeps working and an interruption costs at most one batch. Same total work, very different operational cost one statement single transaction — 6 h, xmin horizon pinned throughout interrupted at hour 5 → zero rows audited batched with a watermark … each commits · interrupted at hour 5 → 5 h of results kept Neither version is faster. Only one of them can be run on a Tuesday afternoon.

There is a second, subtler problem with the one-shot query: it gives you a count and nothing else. A count cannot be acted on. What the repair step needs is a per-row reason, because a bowtie and a hole that escaped its shell call for different responses — one is a mechanical repair, the other is a data-quality incident.

Production-Ready Implementation

The audit table is keyed by the parcel id and carries the reason, the failure location and a timestamp:

sql
CREATE TABLE parcel_validity (
    id           bigint PRIMARY KEY REFERENCES parcels(id) ON DELETE CASCADE,
    is_valid     boolean     NOT NULL,
    reason       text,
    location     geometry(Point, 4326),
    vertices     integer     NOT NULL,
    checked_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX parcel_validity_reason_idx ON parcel_validity (reason)
    WHERE NOT is_valid;

Storing valid rows as well as invalid ones costs a little disk and buys the ability to answer “when was this row last checked?” — which is what makes the audit incremental rather than repeated.

The batch statement checks a keyset window and upserts the result:

sql
WITH batch AS (
    SELECT p.id, p.geom
    FROM parcels p
    LEFT JOIN parcel_validity v ON v.id = p.id
    WHERE p.id > %(after)s
      AND v.id IS NULL                    -- not yet audited
    ORDER BY p.id
    LIMIT %(size)s
),
checked AS (
    SELECT b.id,
           (d).valid    AS is_valid,
           (d).reason   AS reason,
           (d).location AS location,
           ST_NPoints(b.geom) AS vertices
    FROM batch b, LATERAL ST_IsValidDetail(b.geom) AS d
)
INSERT INTO parcel_validity (id, is_valid, reason, location, vertices)
SELECT id, is_valid, reason, location::geometry(Point, 4326), vertices
FROM checked
ON CONFLICT (id) DO UPDATE
    SET is_valid   = EXCLUDED.is_valid,
        reason     = EXCLUDED.reason,
        location   = EXCLUDED.location,
        vertices   = EXCLUDED.vertices,
        checked_at = now()
RETURNING id;

The Python runner is deliberately boring — its only jobs are advancing the watermark, committing, and staying out of the way:

python
import logging
import time
import psycopg

log = logging.getLogger("validity-audit")

def audit(dsn: str, batch_size: int = 2_000, pause: float = 0.05) -> dict:
    """Walk parcels in keyset order, recording validity. Resumable."""
    stats = {"checked": 0, "invalid": 0, "batches": 0}
    after = _resume_point(dsn)

    with psycopg.connect(dsn, autocommit=False) as conn:
        # keep this session out of the way of application traffic
        with conn.cursor() as cur:
            cur.execute("SET statement_timeout = '5min'")
            cur.execute("SET application_name = 'validity-audit'")
        conn.commit()

        while True:
            with conn.cursor() as cur:
                cur.execute(BATCH_SQL, {"after": after, "size": batch_size})
                ids = [r[0] for r in cur.fetchall()]
            conn.commit()

            if not ids:
                log.info("audit complete: %s", stats)
                return stats

            after = max(ids)
            stats["batches"] += 1
            stats["checked"] += len(ids)
            if stats["batches"] % 50 == 0:
                log.info("audited %s rows, watermark id=%s",
                         stats["checked"], after)
            time.sleep(pause)      # leave headroom for live traffic


def _resume_point(dsn: str) -> int:
    """The highest id already audited — where the last run stopped."""
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute("SELECT coalesce(max(id), 0) FROM parcel_validity")
        return cur.fetchone()[0]

Because the resume point comes from the audit table itself rather than from a file or a variable, the job survives a crash, a deploy, and a database failover with no special handling.

From a periodic scan to a draining queue Two modes. In the first run the queue holds all forty-one million rows and drains over about ninety minutes. Afterwards, a trigger clears the audit row whenever a geometry is updated, so the queue holds only recently changed rows and the same runner drains it in seconds per pass. The first run is the expensive one — and the only expensive one first pass — cold audit 41,000,000 rows to check · ~90 minutes of CPU run it once, on a replica if you have one steady state — trigger-fed queue ~18,000 rows changed since the last pass · 4 seconds schedule it every ten minutes and the audit is never more than ten minutes stale The trigger is three lines; it is what turns a report into a monitor.

The trigger that keeps the queue fed:

sql
CREATE OR REPLACE FUNCTION parcels_invalidate_audit() RETURNS trigger AS $$
BEGIN
    DELETE FROM parcel_validity WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER parcels_geom_changed
AFTER UPDATE OF geom ON parcels
FOR EACH ROW
WHEN (OLD.geom IS DISTINCT FROM NEW.geom)
EXECUTE FUNCTION parcels_invalidate_audit();

The WHEN clause matters: without it the trigger fires on every update to any column, and an update that only touches an owner name pointlessly re-queues an expensive validity check.

What each audit column is for The audit table holds five useful columns. The reason drives the repair strategy. The location points a human at the problem. The vertex count predicts repair cost. The checked_at timestamp makes the audit incremental. The validity flag is what the monitoring query counts. Five columns, five different consumers reason chooses the repair method for each class of failure location the point a human opens in QGIS to see what happened vertices predicts repair cost and flags the pathological outliers checked_at turns the next run from a full scan into a queue drain is_valid the number the dashboard and the alert both read Storing valid rows as well as invalid ones is what makes the timestamp useful.

Configuration and Tuning Knobs

statement_timeout on the audit session should be generous but finite. A single batch of two thousand ordinary parcels finishes in under a second; a batch containing one pathological hundred-thousand-vertex coastline can take minutes. Five minutes catches genuine hangs without failing on legitimate outliers.

batch_size trades round-trips against transaction length. Two thousand rows is a good default for polygons averaging a few hundred vertices. If your table holds mostly small geometries, ten thousand is fine; if it holds coastlines, drop to two hundred.

The pause between batches exists solely to leave I/O headroom for application traffic. On a dedicated replica set it to zero. On a primary serving live queries, fifty milliseconds costs the audit about ten percent of its wall-clock time and makes it effectively invisible to everything else.

Verification Steps

Confirm the audit covered everything it should have:

sql
-- every parcel has an audit row
SELECT count(*) AS unaudited
FROM parcels p
LEFT JOIN parcel_validity v ON v.id = p.id
WHERE v.id IS NULL;

-- the distribution that drives the repair plan
SELECT reason, count(*) AS n, round(avg(vertices)) AS avg_vertices
FROM parcel_validity
WHERE NOT is_valid
GROUP BY reason
ORDER BY n DESC;

If unaudited is greater than zero after a completed run, rows were inserted while the audit was walking — expected, and the next pass picks them up. If it keeps growing, inserts are outpacing the audit and the batch size or schedule needs raising.

Gotchas Checklist

  • ST_IsValidDetail returns a composite type. Access its fields as (d).reason, with the parentheses — d.reason is a syntax error in this position and the mistake is easy to miss inside a CTE.
  • The location column needs an explicit type cast. (d).location is an untyped geometry; assigning it to a geometry(Point, 4326) column requires the cast shown above or the insert fails on the type modifier.
  • ON DELETE CASCADE on the audit table is not optional. Without it, deleting a parcel leaves an orphan audit row and the “every parcel has an audit row” check quietly starts comparing different sets.
  • Do not add the audit to the same transaction as the repair. Auditing and repairing in one transaction means a repair failure discards the audit result too, and you re-check the same forty million rows.
  • Watch for NaN coordinates. They are not invalid by GEOS’s definition but break almost everything downstream. Add WHERE NOT ST_IsValid(geom) OR ST_XMin(geom) <> ST_XMin(geom) to catch them in the same pass.