Best practices for structuring municipal GIS databases

Your zoning ingestion pipeline ran clean for six months, then a county published a retroactive ordinance correction and a routine point-in-time query started returning two conflicting entitlement states for the same parcel on the same date. A week later a union against a freshly published overlay throws psycopg2.errors.InvalidParameterValue: Operation on mixed SRID geometries, and a downstream feasibility model silently scores a parcel as commercial when it has been residential since the last amendment. None of these are bugs in your transform code — they are symptoms of a database that was structured as a geometry dump instead of a versioned spatial system. This page answers one narrow question: how do you physically structure a PostGIS schema so that municipal data volatility cannot corrupt it? It is the runnable companion to the broader municipal data structures patterns that sit at the heart of automated zoning change and municipal GIS tracking. The answer is four schema-level guarantees: SRID enforced as a constraint, bi-temporal validity enforced with an exclusion constraint, topology validated at a gate, and a quarantine path that makes reruns idempotent.

Diagnosis: the three structural failures

Before changing the schema, reproduce each failure so the fix can be measured rather than assumed. All three pass naive application-level checks, which is exactly why they reach production.

  1. Mixed-SRID joins. Legacy planning departments distribute zoning shapefiles in local State Plane projections (EPSG:2230, EPSG:32610), while cloud analytics stacks default to WGS84 (EPSG:4326) or Web Mercator (EPSG:3857). A spatial operation across two SRIDs raises Operation on mixed SRID geometries. Detect whether the table is even homogeneous before trusting any join:

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

    More than one row means the table is silently mixed and every overlay result is suspect.

  2. Overlapping validity windows. A flat table storing only current_zoning_code cannot answer “what was the zoning on this date,” and the common half-fix — valid_to = NULL for superseded rows — makes LEFT JOIN return duplicate entitlement states. Find the overlaps:

    SELECT a.parcel_id
FROM zoning_history a
JOIN zoning_history b
  ON a.parcel_id = b.parcel_id
 AND a.ctid <> b.ctid
 AND daterange(a.valid_from, a.valid_to, '[]')
     && daterange(b.valid_from, b.valid_to, '[]');

    Any returned parcel_id is a parcel whose history is ambiguous.

  3. Silent topology degradation. Self-intersecting rings, sliver polygons, and boundaries that fail to snap propagate through spatial joins as false-negative compliance checks. They never raise on insert — only ST_IsValid exposes them:

    SELECT id, ST_IsValidReason(geom) AS defect
FROM staging_zoning
WHERE NOT ST_IsValid(geom);
Ingestion path with three quarantine gates and a snapshot lane A raw municipal feed (shapefile, WFS, or GeoJSON) lands as-is in the staging_zoning table, then passes through three sequential gates: Gate 1 enforces SRID with ST_SRID equal to 4326, Gate 2 validates topology with ST_IsValid, and Gate 3 checks for schema drift. Any row failing a gate is routed down into a shared quarantine schema tagged with its batch_id, the failing check, and the raw payload, and the batch continues. Rows that pass all three gates flow into a bi-temporal upsert (using ON CONFLICT for idempotent reruns) and are written to the production zoning_history table, which is protected by a GiST exclusion constraint. The production table feeds a read-only daily snapshot lane used for point-in-time recovery via pg_restore. Ingestion path — valid rows promote, failures quarantine, production snapshots for recovery Raw feed shapefile · WFS GeoJSON staging_zoning land as-is GATE 1 SRID ST_SRID = 4326 GATE 2 Topology ST_IsValid GATE 3 Schema drift check Upsert bi-temporal ON CONFLICT zoning_history production exclusion constraint pass quarantine schema batch_id · failing_check · raw_payload fail → quarantine, batch continues daily snapshot read-only · pg_restore PITR

Step-by-step implementation

Each step hardens exactly one of the failures above, applied at the schema layer so the database — not the pipeline code — is the enforcement point.

Step 1 — Enforce SRID as a check constraint

Do not rely on the transform step to normalize projection; make the table physically reject anything else. This is the storage-side counterpart to the projection discipline detailed under CRS alignment strategies.

ALTER TABLE zoning_overlays
ADD CONSTRAINT enforce_srid_4326
CHECK (ST_SRID(geom) = 4326);

Land raw feeds in a separate staging table first, and gate promotion on an approved-SRID registry. In the transform itself, build the projection with always_xy=True so axis order is never swapped during batch transforms:

from pyproj import Transformer

# always_xy=True keeps (lon, lat) -> (x, y); without it some CRS invert axes
transformer = Transformer.from_crs("EPSG:2230", "EPSG:4326", always_xy=True)

def reproject(geom_coords):
    return [transformer.transform(x, y) for x, y in geom_coords]

A mismatch against the registry is routed to a quarantine schema and alerted, never coerced in place.

Step 2 — Model history as a bi-temporal table

Store every zoning state as an immutable row carrying valid_from, valid_to, and a transaction_id. Use valid_to = 'infinity' for active records rather than NULL so range comparisons stay total.

CREATE TABLE zoning_history (
    parcel_id       VARCHAR(32)  NOT NULL,
    zoning_code     VARCHAR(16)  NOT NULL,
    jurisdiction_id VARCHAR(16)  NOT NULL,
    valid_from      DATE         NOT NULL,
    valid_to        DATE         NOT NULL DEFAULT 'infinity',
    transaction_id  BIGINT       NOT NULL,
    geom            GEOMETRY(MultiPolygon, 4326) NOT NULL,
    raw_payload     JSONB,
    PRIMARY KEY (parcel_id, valid_from)
);

CREATE INDEX idx_zoning_geom ON zoning_history USING GIST (geom);

Step 3 — Forbid overlaps with an exclusion constraint

Resolve overlapping validity in the database, not in application logic. A GiST exclusion constraint makes a second state for the same parcel during the same window physically impossible:

ALTER TABLE zoning_history
ADD CONSTRAINT exclude_overlapping_zoning
EXCLUDE USING gist (
    parcel_id WITH =,
    daterange(valid_from, valid_to, '[]') WITH &&
);

When a feed publishes a retroactive amendment, close the prior record to the day before the new valid_from, insert the new state, and bump transaction_id — all in one transaction so the constraint never sees a transient overlap:

def apply_amendment(cur, parcel_id, new_code, new_from, geom_wkb, txn_id):
    cur.execute(
        """
        UPDATE zoning_history
           SET valid_to = %s - INTERVAL '1 day'
         WHERE parcel_id = %s
           AND valid_to = 'infinity'
        """,
        (new_from, parcel_id),
    )
    cur.execute(
        """
        INSERT INTO zoning_history
            (parcel_id, zoning_code, valid_from, transaction_id, geom)
        VALUES (%s, %s, %s, %s, ST_GeomFromWKB(%s, 4326))
        ON CONFLICT (parcel_id, valid_from) DO UPDATE
            SET zoning_code = EXCLUDED.zoning_code,
                transaction_id = EXCLUDED.transaction_id
        """,
        (parcel_id, new_code, new_from, txn_id, geom_wkb),
    )

The ON CONFLICT clause is what makes a rerun of a half-failed batch idempotent instead of duplicating rows.

Step 4 — Gate topology before promotion

Run validity as a gate immediately after SRID normalization and before any row reaches zoning_history. Repair only after logging the original state and tagging the row, so the correction is auditable:

def gate_topology(cur):
    cur.execute(
        "SELECT id, ST_IsValidReason(geom) FROM staging_zoning "
        "WHERE NOT ST_IsValid(geom)"
    )
    for row_id, reason in cur.fetchall():
        cur.execute(
            "INSERT INTO topology_defects (staging_id, reason) VALUES (%s, %s)",
            (row_id, reason),
        )
        cur.execute(
            "UPDATE staging_zoning "
            "SET geom = ST_MakeValid(geom), correction_applied = TRUE "
            "WHERE id = %s",
            (row_id,),
        )

Codes carried in raw_payload should be resolved against the canonical zoning taxonomy mapping at this stage, and structural field checks belong to schema validation and data quality checks.

Step 5 — Emit a per-batch compliance manifest

Every promotion produces one immutable JSON artifact tying the run to the table state. This is the record compliance framework integration consumes for audit and underwriting review.

import hashlib, json
from datetime import datetime, timezone

def build_manifest(cur, batch_id, source_srid):
    cur.execute("SELECT count(*) FROM staging_zoning")
    valid = cur.fetchone()[0]
    cur.execute("SELECT count(*) FROM quarantine_zoning WHERE batch_id = %s", (batch_id,))
    quarantined = cur.fetchone()[0]
    cur.execute("SELECT count(*) FROM topology_defects")
    defects = cur.fetchone()[0]
    cur.execute("SELECT md5(string_agg(parcel_id || valid_from::text, '' ORDER BY parcel_id)) "
                "FROM zoning_history")
    checksum = cur.fetchone()[0]
    return json.dumps({
        "batch_id": batch_id,
        "generated_at": datetime.now(timezone.utc).isoformat(),
        "source_srid": source_srid,
        "target_srid": 4326,
        "records_valid": valid,
        "records_quarantined": quarantined,
        "topology_defects": defects,
        "table_checksum": checksum,
    }, indent=2)

Verification & testing

Confirm the structure holds rather than assuming the migration worked.

  • SRID is homogeneous. The GROUP BY ST_SRID(geom) query from diagnosis must return exactly one row (4326). The check constraint guarantees it for new rows; run it once after backfilling legacy data.
  • No overlaps survive. The self-join overlap query must return zero rows. If the exclusion constraint failed to create, it is almost always pre-existing overlapping data — clean it before adding the constraint.
  • Point-in-time queries are single-valued. For a sample of parcels, assert that WHERE %(d)s BETWEEN valid_from AND valid_to returns exactly one row for any date d.
  • Reruns are idempotent. Run the same batch twice and assert the table_checksum in the manifest is identical. A changed checksum means a non-idempotent upsert is double-writing.
  • Topology is clean post-gate. SELECT count(*) FROM zoning_history WHERE NOT ST_IsValid(geom) must be zero; every repaired row must carry correction_applied = TRUE and a matching topology_defects log entry.

Failure recovery

When a corrupted feed bypasses a gate or a promotion fails mid-batch, the structure must let you recover without manual surgery.

  • Quarantine, never coerce. A row failing SRID, topology, or schema-drift checks is written to a quarantine_zoning partition with its batch_id, the failing check, and the raw payload, then the batch continues. This mirrors the dead-letter discipline used across error handling and retry logic in the ingestion layer.
  • Snapshot rollback. Keep daily read-only snapshots of zoning_history. If a bad batch lands, restore the snapshot with pg_restore and replay only the verified batches from quarantine by transaction_id, so you never lose downstream-confirmed states.
  • Fallback to cached feeds. When a municipal endpoint is entirely unavailable, route to a versioned archive and tag outputs data_freshness: cached so stale zoning never triggers an automated entitlement approval — the routing rules live in fallback routing logic.
  • Resume by transaction_id. Persist the last committed transaction_id per source. On restart, skip any batch already reflected in the manifest checksum so the rerun is idempotent against the exclusion constraint.
  • Throttle the fetch, not the schema. Large county backfills belong to async batch processing; the schema gates stay synchronous so a slow feed never partially promotes.

Frequently asked questions

Why use valid_to = 'infinity' instead of NULL for active rows?

NULL breaks range logic: daterange(valid_from, NULL) is treated as unbounded in some operators and as unknown in comparisons, which is what lets duplicate states slip through a LEFT JOIN. The 'infinity' sentinel keeps every range total and bounded, so the exclusion constraint and BETWEEN queries behave deterministically.

Should I enforce SRID with a constraint or just trust the transform step?

Enforce it with a CHECK (ST_SRID(geom) = 4326) constraint. Transform code is one bug or one new feed away from inserting a mismatched projection, and the failure is silent until a join throws. A constraint makes the database the single enforcement point and turns a runtime corruption into an insert-time rejection you can quarantine.

Will the GiST exclusion constraint slow down high-volume upserts?

It adds an index probe per insert, which is negligible next to the spatial index maintenance already happening on geom. The far larger cost is leaving overlaps undetected and reconciling them later. Batch the amendment close-and-insert in one transaction so the constraint is checked once at commit, not row by row.

Where do I resolve fragmented local zoning codes into a standard taxonomy?

Resolve them at the staging gate, before promotion, against a versioned lookup keyed on source code and jurisdiction. Unmapped codes are flagged UNMAPPED and quarantined rather than guessed. The full deterministic mapping pattern, including regex fallback for density and overlay modifiers, is covered under zoning taxonomy mapping.

For continuous archiving and point-in-time recovery, see the official PostGIS administration and backup documentation.