Short answer

SQL turns spatial quality control from a manual click-through into a repeatable, auditable batch you can run over millions of rows. In PostGIS the core moves are: validate geometry with ST_IsValid / ST_IsValidReason, repair with ST_MakeValid, confirm a single correct ST_SRID, and find topology problems — overlaps, gaps, duplicates, slivers — with spatial self-joins backed by a GiST index. Because every check is a query, the QC report is reproducible and the exact rule that flagged each feature is visible.

Why SQL beats clicking for QC

A desktop "check validity" tool is fine for one layer once. But QC on a production dataset has different demands: it runs on every load, it must scale to large tables, and it must produce evidence — which features failed which rule. SQL gives you all three. A query is the rule and the record at once, and PostGIS exposes the OGC-standard predicate and measurement functions (ST_Intersects, ST_Overlaps, ST_Area, ST_Distance) that the desktop GUI is wrapping anyway. The first prerequisite is an index, or every self-join will table-scan:

CREATE INDEX idx_parcels_geom ON parcels USING GIST (geom);
ANALYZE parcels;

The GiST index lets the bounding-box stage of ST_Intersects/ST_Overlaps prune candidate pairs before the expensive exact test runs.

Geometry validity

Invalid geometries (self-intersecting polygons, rings that touch, non-closed rings) break area, buffer, and overlay operations and silently corrupt results. Find them and read why:

SELECT id,
       ST_IsValidReason(geom) AS reason,
       ST_AsText(ST_PointN(ST_ExteriorRing(geom), 1)) AS sample_vertex
FROM   parcels
WHERE  NOT ST_IsValid(geom);

ST_IsValidReason returns text such as Self-intersection[...] with the offending location — far more useful than a boolean. Repair into a new column so the change is reviewable:

ALTER TABLE parcels ADD COLUMN geom_fixed geometry;
UPDATE parcels SET geom_fixed = ST_MakeValid(geom);

-- audit: how much did the repair change each shape?
SELECT id,
       ST_Area(geom)        AS area_before,
       ST_Area(geom_fixed)  AS area_after,
       abs(ST_Area(geom) - ST_Area(geom_fixed)) AS delta
FROM   parcels
WHERE  NOT ST_IsValid(geom)
ORDER  BY delta DESC;

ST_MakeValid can split a self-intersecting polygon into a multipolygon; a large area delta or an unexpected geometry-type change deserves a manual look before you overwrite geom.

CRS / SRID checks

A geometry whose SRID is 0 has no declared CRS, so every distance and area it produces is meaningless or wrong. Confirm one correct SRID across the table:

SELECT ST_SRID(geom) AS srid, count(*) 
FROM   parcels 
GROUP  BY ST_SRID(geom);

A single row (e.g. 27700 | 482311) is healthy. Multiple SRIDs, or 0, is a defect. To assign a known-correct SRID to geometries that are already in that CRS but unlabelled, use ST_SetSRID — it changes the label only:

UPDATE parcels SET geom = ST_SetSRID(geom, 27700) WHERE ST_SRID(geom) = 0;

To actually move coordinates between CRSs, use ST_Transform — never ST_SetSRID:

-- reproject British National Grid to UTM 30N for a metric overlay
UPDATE parcels SET geom = ST_Transform(geom, 32630);

Confusing the two is the classic error: ST_SetSRID on data that needs transformation just mislabels it, and the features end up in the wrong place on Earth while reporting a valid SRID.

Overlaps, gaps, and duplicates

These are the bread-and-butter of polygon QC for cadastre, geology, and land-cover layers.

Overlapping polygons that should be mutually exclusive:

SELECT a.id AS id_a, b.id AS id_b,
       ST_Area(ST_Intersection(a.geom, b.geom)) AS overlap_area
FROM   parcels a
JOIN   parcels b ON a.id < b.id        -- a.id < b.id avoids self-pairs and duplicates
WHERE  ST_Overlaps(a.geom, b.geom)
ORDER  BY overlap_area DESC;

The a.id < b.id join condition reports each overlapping pair once and never a feature against itself. The GiST index makes the ST_Overlaps pre-filter cheap.

Gaps (slivers) between polygons that should tile a study area without holes — union everything and subtract from the boundary:

WITH coverage AS (
  SELECT ST_Union(geom) AS g FROM parcels
)
SELECT (ST_Dump(ST_Difference(b.geom, c.g))).geom AS gap_geom,
       ST_Area((ST_Dump(ST_Difference(b.geom, c.g))).geom) AS gap_area
FROM   study_boundary b, coverage c;

Filter gap_area to ignore negligible numeric slivers and surface the real holes.

Duplicate or near-duplicate geometries:

SELECT ST_AsText(geom) AS wkt, count(*) 
FROM   parcels 
GROUP  BY geom 
HAVING count(*) > 1;

For near-duplicates (snapping noise), group on ST_SnapToGrid(geom, 0.001) instead, which collapses geometries that differ only at sub-millimetre precision.

Degenerate geometries — zero-area polygons, zero-length lines, repeated vertices:

SELECT id FROM parcels WHERE ST_Area(geom) = 0 OR ST_NPoints(geom) < 4;  -- a valid ring needs >=4 points
SELECT id, ST_NPoints(geom) - ST_NPoints(ST_RemoveRepeatedPoints(geom)) AS repeated
FROM   parcels
WHERE  ST_NPoints(geom) <> ST_NPoints(ST_RemoveRepeatedPoints(geom));

A consolidated QC report

Roll the checks into one summary so a load either passes or fails with counts:

SELECT
  count(*)                                          AS total,
  count(*) FILTER (WHERE NOT ST_IsValid(geom))      AS invalid,
  count(*) FILTER (WHERE ST_SRID(geom) <> 27700)    AS wrong_srid,
  count(*) FILTER (WHERE ST_IsEmpty(geom))          AS empty,
  count(*) FILTER (WHERE ST_NPoints(geom) < 4)      AS degenerate
FROM parcels;

Wire this into the ingest pipeline so any non-zero count blocks publication and writes the offending IDs to a qc_failures table for review.

Validation

  • Re-run after repair. After ST_MakeValid, the invalid count in the summary should be zero; if not, inspect the residual ST_IsValidReason.
  • Check the index is used. EXPLAIN ANALYZE on the overlap query should show an Index Scan / Bitmap Index Scan on the GiST index, not a sequential scan.
  • Quantify, do not just count. Order overlaps and gaps by area so genuine errors rise above numeric noise.
  • Snapshot the QC summary with each load date so you can show data quality trending over time.

Common pitfalls and why they happen

  • Using ST_SetSRID when you needed ST_Transform. The data gets a valid-looking SRID but the coordinates were never reprojected, so features land in the wrong place. Use ST_Transform to move coordinates.
  • No spatial index, so QC "hangs." Self-joins without GiST degrade to O(n²) exact tests. Create the index and ANALYZE first.
  • Treating tiny slivers as real errors. Floating-point overlay produces micro-areas; threshold by area before flagging.
  • Overwriting geom with ST_MakeValid blindly. A repair can change geometry type or area materially; repair into a new column and audit deltas first.
  • SRID 0 measurements. Areas and distances on unset-SRID geometry are unitless or wrong; fix the SRID before any spatial measure.

Bathyl perspective

We codify spatial QC as SQL that runs on every data load, not as a one-off cleanup. Each rule is a query, each failure is a row with a reason, and the summary either gates publication or does not. That makes data quality a measurable, versioned property of the dataset rather than a hope that someone checked it in the GUI.

Related reading

Sources