Diagnosing Invalid Geometry with ST_IsValidDetail

ST_IsValid tells you that a geometry is broken. ST_IsValidDetail tells you what is broken and where — a reason string, a point marking the defect, and a validity flag — which is the difference between a count of failures and a repair plan. This guide builds the triage table around it: screening cheaply, expanding only the failures, materialising defect points you can map, and grouping by reason so repair strategy is decided per defect class rather than per feature. It is the PostGIS diagnosis path for the checks described in Geometry Validity Checks for Vector Data.

Prerequisites

• PostGIS 3.2+ on PostgreSQL 14+, with GEOS 3.10 or later. Reason strings changed wording across GEOS versions, so record the version with your results: SELECT postgis_full_version();
• A geometry column with a spatial index — not for the validity check itself, which is a full scan, but for everything you will do with the results.
• Write access to a schema for the triage table. The triage output is data, and it should live somewhere queryable rather than in a psql scrollback buffer.
• A decision on the ESRI validity flag before you start counting, because it materially changes the numbers.

Step-by-Step Procedure

Step 1 — Screen with the cheap boolean

-- How much of the layer is affected, before doing any detailed work.
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid,
       count(*)                                     AS total,
       round(100.0 * count(*) FILTER (WHERE NOT ST_IsValid(geom)) / count(*), 3)
         AS invalid_pct
FROM parcels;

Verification: if invalid_pct is above a few percent, something systematic happened — a bad import, a failed overlay, a coordinate precision change. Investigate the source before triaging individual features.

Step 2 — Expand only the failures

-- ST_IsValidDetail returns a composite: (valid, reason, location).
CREATE TABLE qa.invalid_parcels AS
SELECT
  p.parcel_id,
  ST_GeometryType(p.geom)          AS geom_type,
  ST_NPoints(p.geom)               AS vertices,
  ST_Area(p.geom)                  AS area_m2,
  d.reason,
  d.location                       AS defect_point,
  split_part(d.reason, '[', 1)     AS reason_class
FROM parcels p
CROSS JOIN LATERAL ST_IsValidDetail(p.geom) AS d
WHERE NOT ST_IsValid(p.geom);          -- screen first; the lateral only runs on failures

CREATE INDEX ON qa.invalid_parcels USING GIST (defect_point);

Verification: the row count must equal the invalid count from Step 1. A mismatch means the screen and the detail disagree, which happens if the geometry column changed between the two queries — run them in one transaction on a busy table.

Step 3 — Group by reason to plan the work

SELECT reason_class,
       count(*)                                  AS features,
       round(avg(vertices))                      AS avg_vertices,
       round(sum(area_m2)::numeric, 1)           AS total_area_m2,
       min(parcel_id)                            AS example_id
FROM qa.invalid_parcels
GROUP BY reason_class
ORDER BY features DESC;

Typical output on a cadastral layer looks like this:

Verification: the grouping is what turns 523 broken features into five decisions. Anything with a handful of features and a high vertex count is worth looking at individually; anything with hundreds of features and a common reason is a batch job.

Step 4 — Map the defect points

-- Export the defect locations for visual review in QGIS.
COPY (
  SELECT parcel_id, reason_class, ST_AsGeoJSON(defect_point) AS geojson
  FROM qa.invalid_parcels
) TO '/tmp/defect_points.csv' WITH CSV HEADER;

-- Or ask the spatial question directly: are the defects clustered?
SELECT reason_class,
       count(*) AS defects,
       ST_AsText(ST_Centroid(ST_Collect(defect_point))) AS centroid,
       round(ST_Area(ST_ConvexHull(ST_Collect(defect_point)))::numeric / 1e6, 2)
         AS spread_km2
FROM qa.invalid_parcels
GROUP BY reason_class;

Verification: a small spread_km2 for a reason class means the defects are concentrated — usually one digitising session, one map sheet, or one overlay operation. That is a much more useful finding than the feature count, because it points at a cause rather than a symptom.

Step 5 — Repair per reason, with a guard

-- Deterministic classes: repair, verify, and record what changed.
WITH candidates AS (
  SELECT parcel_id FROM qa.invalid_parcels
  WHERE reason_class IN ('Self-intersection', 'Ring Self-intersection')
),
repaired AS (
  SELECT p.parcel_id,
         p.geom                                            AS before_geom,
         ST_CollectionExtract(ST_MakeValid(p.geom), 3)     AS after_geom
  FROM parcels p JOIN candidates c USING (parcel_id)
)
UPDATE parcels p
SET geom = r.after_geom
FROM repaired r
WHERE p.parcel_id = r.parcel_id
  AND ST_IsValid(r.after_geom)
  AND abs(ST_Area(r.after_geom) - ST_Area(r.before_geom))
      / NULLIF(ST_Area(r.before_geom), 0) < 0.001;    -- area-loss guard: 0.1%

-- Anything that failed the guard stays broken and stays visible.
SELECT parcel_id, reason_class FROM qa.invalid_parcels
WHERE parcel_id NOT IN (SELECT parcel_id FROM parcels WHERE ST_IsValid(geom));

Verification: re-run Step 1. The invalid count should fall by the number of repaired features and no further; if it falls by more, something repaired features that were not in the candidate set, and the audit trail is incomplete. The guard and the audit pattern here follow Repairing Invalid Geometries with ST_MakeValid.

Interpreting Results

The distinction that governs everything is deterministic versus ambiguous. A self-intersection has one obvious repair and no judgement involved. A hole outside its shell does not: the correct fix might be to delete the hole, move it, or reject the feature, and each answer changes the area. Deterministic reasons belong in an unattended batch; ambiguous ones belong in a steward’s queue, per the routing model in Automated Geometry Remediation.

Gotchas & Edge Cases

Only one defect is reported per geometry. GEOS stops at the first problem it finds, so a feature with three self-intersections reports one location. Repair and re-check in a loop rather than assuming one pass clears everything.

The ESRI flag changes the answer, not just the wording. ST_IsValidDetail(geom, 1) accepts self-touching rings that the OGC interpretation rejects. Data exported from ArcGIS frequently contains them by design. Choose the interpretation once, record it next to the defect counts, and resist switching mid-programme.

Reason strings are not a stable API. Wording has changed between GEOS releases. Grouping on the prefix before the coordinate bracket, as in Step 2, survives those changes; matching the full string does not.

ST_MakeValid can return a collection. Repairing a polygon may produce a GEOMETRYCOLLECTION containing polygons and stray linework. ST_CollectionExtract(..., 3) keeps only the polygonal parts, which is almost always what a polygon column requires.

The location point can be inside a hole or outside the feature. It marks where GEOS detected the problem, not where the feature is. Do not use it as a proxy for the feature’s position when mapping.

Validity checks do not use the spatial index. The screen in Step 1 is a full scan, and on a large table it is worth running it as part of a scheduled job rather than interactively during an investigation.

When to Escalate

• A validity rate above a few percent is a source problem. Find the import, the overlay or the export that produced them, because repairing symptoms while the source keeps producing them is unbounded work.
• Defects clustered in one area or one map sheet point at a specific digitising session or supplier batch — escalate with the spatial extent, which is far more actionable than a feature list.
• Interior is disconnected or Hole lies outside shell in a cadastral layer should reach the registrar before any repair, because the repair changes which land is inside the parcel.
• Repairs failing the area-loss guard are exactly the cases a human should see. Route them to the steward queue rather than loosening the guard, which is the usual and wrong response.

Frequently Asked Questions

Related

• Geometry Validity Checks for Vector Data — the validity model and the defect vocabulary
• Repairing Invalid Geometries with ST_MakeValid — the repair step this triage feeds
• Validating Polygon Self-Intersections in QGIS — the desktop equivalent of this workflow

Back to Geometry Validity Checks for Vector Data