Repairing invalid geometries with ST_MakeValid

This page adds a repair step to a dbt staging layer that is safe to run in production: it fixes the defects that can be fixed, refuses the ones that cannot, records what changed for every repaired row, and never silently turns a polygon into something else.

When to use this approach

  • Invalid geometry arrives regularly and blocking the build is not an option. A feed with a handful of self-intersections every week needs repair-and-record, not a red build every week.
  • A downstream function is failing on invalid input. ST_Intersection, ST_Union and ST_Buffer all misbehave on invalid polygons, sometimes by returning a wrong answer rather than an error.
  • You already quarantine and now need to recover some of it. Repair complements the quarantine pattern in quarantining invalid geometries in staging rather than replacing it.

Prerequisites

  • PostGIS 3.0+ (ST_MakeValid with the method parameter arrived in 3.2 and is worth having).
  • A quarantine table already in place, so unrepairable rows have somewhere to go.
  • ST_IsValidReason available for recording why a geometry was invalid before repair.
  • Agreement that repaired geometry is acceptable for the use case. For a regulated boundary set it may not be.

Step-by-step instructions

1. Classify the defects before repairing anything

Not all invalidity is equal, and the reason string tells you which kind you have.

sql
-- analyses/invalidity_profile.sql
select
    split_part(st_isvalidreason(geom), '[', 1) as reason,
    count(*)                                   as features,
    round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
from {{ source('ops', 'zones_raw') }}
where not st_isvalid(geom)
group by 1
order by features desc
text
 reason                        | features | pct
-------------------------------+----------+------
 Self-intersection             |      412 | 61.2
 Ring Self-intersection        |      158 | 23.5
 Duplicate Rings               |       61 |  9.1
 Too few points in geometry    |       28 |  4.2
 Nested holes                  |       14 |  2.1

Verify the profile is stable between deliveries. A sudden new reason category is a change at the source, and repairing it blindly hides that signal — the drift monitoring in alerting on geometry drift between runs is the right place to notice it.

Three invalidity defects and what repair does to each Three cases side by side. A bowtie self-intersection becomes two separate polygons, changing the geometry type from polygon to multipolygon. A ring self-intersection is resolved into a polygon with a hole, preserving type. A polygon with too few points cannot be repaired at all and must be quarantined. Each case is annotated with whether the type changed and whether area was preserved. bowtie self-intersection → MULTIPOLYGON of 2 parts type changes · area preserved ring self-intersection → POLYGON with a hole type preserved · area reduced too few points → nothing to repair quarantine, do not fabricate Repair is not one operation — it is three different outcomes with different consequences.

2. Repair, but keep the type contract

ST_MakeValid may return a different geometry type than it was given: a self-intersecting polygon becomes a multipolygon, and a degenerate one can come back as a line or a collection. A staging model that declares “polygons” and silently emits a GEOMETRYCOLLECTION breaks everything downstream.

sql
-- models/staging/stg_zones.sql
{{ config(materialized = 'table') }}

with raw as (
    select zone_id, zone_name, geom, st_isvalid(geom) as was_valid,
           st_isvalidreason(geom) as invalid_reason
    from {{ source('ops', 'zones_raw') }}
),

repaired as (
    select
        zone_id, zone_name, was_valid, invalid_reason,
        case when was_valid then geom
             else st_makevalid(geom, 'method=structure keepcollapsed=false')
        end as geom_repaired
    from raw
),

typed as (
    select
        zone_id, zone_name, was_valid, invalid_reason,
        -- keep only polygonal components; a repair that yields lines or points
        -- is a repair that changed the meaning of the row
        st_collectionextract(geom_repaired, 3) as geom,
        st_geometrytype(geom_repaired) as repaired_type
    from repaired
)

select * from typed where geom is not null and not st_isempty(geom)

method=structure is the newer repair algorithm and generally gives cleaner results for polygon data than the default linework method; keepcollapsed=false drops the zero-area fragments that would otherwise arrive as lines. ST_CollectionExtract(…, 3) then keeps only polygonal parts, so the output type contract holds.

Verify the repair produced polygons and only polygons:

sql
select st_geometrytype(geom) as type, count(*)
from {{ ref('stg_zones') }} group by 1;
-- Expect ST_Polygon and ST_MultiPolygon only

3. Record every repair

A repair that leaves no trace is indistinguishable from source data, which makes a later “this boundary looks wrong” conversation unanswerable.

sql
-- models/staging/stg_zone_repairs.sql
{{ config(materialized = 'incremental', unique_key = ['zone_id', 'detected_at']) }}

select
    zone_id,
    '{{ run_started_at }}'::timestamp        as detected_at,
    invalid_reason,
    repaired_type,
    round((st_area(geom) / nullif(st_area(geom_original), 0))::numeric, 6) as area_ratio
from {{ ref('stg_zones_with_original') }}
where not was_valid

The area_ratio is the number that matters in review. A repair that preserves area is usually a topological tidy-up; one that removes 12 per cent of a zone changed the answer, and someone should decide whether that is acceptable rather than discovering it in a dashboard.

Verify the repair log is small and stable:

sql
select detected_at::date, count(*) as repairs, round(avg(area_ratio), 4) as mean_area_ratio
from {{ ref('stg_zone_repairs') }} group by 1 order by 1 desc limit 14;

4. Fail the build when repair changes too much

sql
-- tests/assert_repair_within_tolerance.sql
select zone_id, invalid_reason, area_ratio
from {{ ref('stg_zone_repairs') }}
where detected_at > current_date - interval '1 day'
  and (area_ratio < 0.98 or area_ratio > 1.02)
yaml
models:
  - name: stg_zones
    columns:
      - name: geom
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "st_isvalid(geom)"

The validity test after repair looks redundant and is not: ST_MakeValid can fail to produce valid output on pathological input, and finding that out in staging is far better than in a join.

The three outcomes of a repair step, and where each row ends up Raw geometry enters a validity check. Valid rows pass straight through. Invalid rows go to repair, from which three outcomes emerge: repaired within the area tolerance and logged, repaired but outside tolerance which fails the build, and unrepairable which is quarantined. Each destination is labelled with what happens next. raw geometry from the feed ST_IsValid + reason ST_MakeValid method=structure repaired, area within ±2% logged and shipped repaired, area moved too far fails the build for review unrepairable quarantined, counted, reported valid rows skip the repair entirely Distribution of area change across one delivery's repairs A histogram of the ratio of repaired area to original area. Most repairs cluster tightly at a ratio of one, meaning area was preserved. A shaded tolerance band spans two percent either side. A small group of repairs sits near a ratio of 0.88, outside the band, annotated as the rows the build should stop on because the repair removed a real part of the shape. ±2% tolerance repairs that removed 12% of the shape 0.88 1.00 1.10 repaired area ÷ original area →

Configuration reference

Parameter Where Values Note
method ST_MakeValid linework (default), structure structure gives cleaner polygon results; PostGIS 3.2+
keepcollapsed ST_MakeValid true / false false drops zero-area fragments that would otherwise become lines
ST_CollectionExtract type model SQL 1 point, 2 line, 3 polygon Enforces the output type contract after repair
area tolerance test ±2% Tighten for regulated data; loosen only with a reason
repair log retention log model 90 days Long enough to answer “when did this boundary change?”
quarantine destination separate model — Unrepairable rows are data, not errors to discard

Gotchas & edge cases

  • Repair is not idempotent across versions. A different GEOS build can repair the same input differently, which is one more reason to pin versions, as in managing PostGIS extension versions across environments.
  • A repaired multipolygon breaks a unique assumption if downstream code assumed one ring per feature. Test the geometry type, not only validity.
  • ST_MakeValid on a valid geometry is a no-op but not free. Guard it with case when st_isvalid(...) so the common path costs nothing.
  • Repair can produce an empty geometry. Filter empties explicitly; an empty polygon passes ST_IsValid and then silently matches nothing in every join.
  • Never repair in the join. ST_Intersects(ST_MakeValid(a.geom), b.geom) disables the index and repairs the same row once per candidate pair.

FAQ

Should I repair or quarantine?

Both, split by outcome. Repair the defects that are clearly encoding artefacts — self-intersections from a digitising tool, duplicate rings — and quarantine anything whose repair materially changes the shape. The area-ratio test is what draws that line automatically instead of case by case.

Is method=structure always better?

For polygon coverage data, usually. The linework method preserves every input line, which can leave slivers and spikes; the structure method rebuilds the polygon’s area and tends to produce what a person would draw. For linear data, where the linework is the content, the default is the safer choice.

Where should repair live — staging or a separate model?

In staging, as close to the source as possible, so nothing downstream ever sees invalid geometry. Keeping the repair log as a separate model is what allows staging to stay a simple one-row-per-feature model while the audit trail accumulates alongside it.

How do I tell whether a repair mattered?

Compare area, vertex count and geometry type before and after. Area is the headline number; a type change from polygon to multipolygon usually indicates a bowtie, which is benign; a large vertex-count drop suggests the repair simplified rather than fixed, which deserves a look.

Up: Part of Geometry Validation and Data Quality.