Modeling bitemporal zoning history in PostGIS

An underwriter emails at 4pm: “What was parcel 08-114-0032 in Larimer County zoned on 12 March 2026?” You run the query, answer “R-1,” and close the ticket. Two weeks later the county issues a correction saying that parcel was actually re-designated MU-2 effective 1 March — a change their portal published late. Now you have two problems at once. Your March report said R-1 and needs to stay reproducible exactly as it was, because a deal was priced on it. But your current best knowledge is that the parcel was MU-2 on 12 March all along. A single-timeline table cannot hold both truths; overwriting the row destroys the report, and refusing the correction leaves your data wrong. This guide solves that exact double-bind by modeling zoning history bitemporally in PostGIS, so a back-dated correction slots in without mutating a single prior record. It is the hands-on companion to temporal versioning & snapshots within the broader Spatial Impact Analysis & Zoning Change Detection area.

Diagnosis: why one timeline cannot answer the question

The failure is not a bug in your SQL — it is a missing dimension in your schema. A uni-temporal table stores one axis. If it stores valid time, the correction rewrites what “12 March” resolves to and your old report silently changes. If it stores transaction time, you know when rows were written but not when the zoning actually took effect, so you cannot answer the effective-date question at all.

Reproduce it concretely. With a naive table that carries only valid_from/valid_to, applying the correction forces an UPDATE on the R-1 row:

-- Naive single-timeline table
UPDATE zoning
SET zoning_code = 'MU-2', valid_from = '2026-03-01'
WHERE parcel_id = '08-114-0032';
-- The R-1 fact that your March report relied on is now gone forever.

Re-run the March report and it returns MU-2. The number an underwriter priced against has vanished with no trace that it ever existed. The tell-tale symptom in production is a reproducibility failure: a report regenerated from the same query on the same parcel returns a different answer than the archived PDF, and nobody can explain why. That is the signature of a correction having overwritten history on the only axis you kept.

The fix is two axes. Valid time answers “when was MU-2 in effect on the ground” (from 1 March). Transaction time answers “when did we believe it” (from the day the correction landed). Keeping both, the March report reads the parcel as we believed on 12 March — R-1 — while a fresh query reads it as best known now — MU-2 — and both are correct.

Bitemporal timeline of a back-dated zoning correction Two horizontal time axes for one parcel. The upper transaction-time band shows the original belief: R-1 valid from early March, believed from ingest until the correction lands. When the correction arrives, that belief is closed on the transaction axis and two new versions open: R-1 valid only up to 1 March and MU-2 valid from 1 March onward, both now believed. An as-of query reading the old transaction slice returns R-1 for 12 March; the same query reading the current slice returns MU-2. No prior row was mutated. belief before correction belief after correction R-1 valid from 1 Mar · believed at ingest → correction correction lands R-1 valid 1 Mar → 1 Mar (closed) MU-2 valid from 1 Mar · believed now as-of 12 Mar, believed then → R-1 as-of 12 Mar, known now → MU-2 One parcel, two beliefs — both provable, nothing overwritten
The correction closes the original belief on the transaction axis and opens two new versions; the same as-of query returns R-1 or MU-2 depending on which belief instant it reads.

Step-by-step implementation

Each step is one concern. Build them in order against a PostGIS database.

Step 1 — Define the bitemporal table with two range columns

Use tstzrange for both axes and half-open bounds so consecutive versions abut cleanly. Store the parcel’s durable business key, not a surrogate that changes on re-export.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE zoning_bt (
    version_id   bigserial PRIMARY KEY,
    parcel_id    text NOT NULL,
    zoning_code  text NOT NULL,
    geom         geometry(MultiPolygon, 4326) NOT NULL,
    valid_range  tstzrange NOT NULL,                               -- effect on the ground
    txn_range    tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity'),  -- our belief
    corrected    boolean   NOT NULL DEFAULT false,                 -- flags back-dated fixes
    CONSTRAINT valid_nonempty CHECK (NOT isempty(valid_range))
);

Step 2 — Add the GiST exclusion constraint

This is the guardrail that makes the model trustworthy: for one parcel, within any transaction-time slice, no two valid-time ranges may overlap. The database now rejects a contradictory timeline at write time instead of storing it silently.

ALTER TABLE zoning_bt
  ADD CONSTRAINT no_overlap_per_belief
  EXCLUDE USING gist (
      parcel_id   WITH =,
      txn_range   WITH &&,   -- only rows believed in the same window conflict
      valid_range WITH &&
  );

CREATE INDEX zoning_bt_lookup ON zoning_bt USING gist (parcel_id, valid_range, txn_range);

Step 3 — Insert a new version on a forward change

A normal forward change (the parcel really was rezoned going forward) closes the current version at the new effective date on the valid axis, closes its transaction window now, and inserts the successor. Wrap it in one transaction so a crash never leaves a half-versioned parcel.

import psycopg
from datetime import datetime, timezone

def apply_forward_change(conn, parcel_id, new_code, geom_wkt, effective_from):
    """Close the current version at effective_from, insert the successor."""
    now = datetime.now(timezone.utc)
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE zoning_bt
            SET valid_range = tstzrange(lower(valid_range), %s, '[)'),
                txn_range   = tstzrange(lower(txn_range), %s, '[)')
            WHERE parcel_id = %s AND upper_inf(txn_range)
              AND lower(valid_range) < %s          -- guard: only close earlier versions
            """,
            (effective_from, now, parcel_id, effective_from),
        )
        cur.execute(
            """
            INSERT INTO zoning_bt (parcel_id, zoning_code, geom, valid_range, txn_range)
            VALUES (%s, %s, ST_GeomFromText(%s, 4326),
                    tstzrange(%s, 'infinity', '[)'),
                    tstzrange(%s, 'infinity', '[)'))
            """,
            (parcel_id, new_code, geom_wkt, effective_from, now),
        )
    conn.commit()

Step 4 — Write the as-of query for either belief instant

One query answers both “as believed then” and “as known now” by taking two instants: the valid-time point you care about and the transaction-time point you are reading from.

def as_of(conn, parcel_id, valid_at, known_at=None):
    """Version in effect at valid_at, as believed at known_at (default: now)."""
    known_at = known_at or datetime.now(timezone.utc)
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT zoning_code, lower(valid_range) AS effective_from, corrected
            FROM zoning_bt
            WHERE parcel_id = %s
              AND valid_range @> %s::timestamptz
              AND txn_range   @> %s::timestamptz
            """,
            (parcel_id, valid_at, known_at),
        )
        return cur.fetchone()

Reading the March report reproducibly means passing the report’s original run timestamp as known_at; the query then returns R-1 even after the correction lands.

Step 5 — Apply the back-dated correction without mutation

Here is the crux. The correction says the parcel was MU-2 effective 1 March, contradicting the R-1 we currently believe. We do not touch the R-1 row’s geometry or code. Instead we close its transaction window (we stopped believing it now), then insert the corrected valid-time timeline as freshly-believed versions.

def apply_backdated_correction(conn, parcel_id, corrected_code, geom_wkt,
                               corrected_effective_from):
    """Supersede the current belief without destroying it.

    The prior version stays fully queryable via its now-closed txn_range;
    the corrected fact opens as a new belief on the transaction axis.
    """
    now = datetime.now(timezone.utc)
    with conn.cursor() as cur:
        # 1. Stop believing every currently-open version of this parcel.
        cur.execute(
            """
            UPDATE zoning_bt
            SET txn_range = tstzrange(lower(txn_range), %s, '[)')
            WHERE parcel_id = %s AND upper_inf(txn_range)
            """,
            (now, parcel_id),
        )
        # 2. Insert the corrected valid-time fact as a new, current belief.
        try:
            cur.execute(
                """
                INSERT INTO zoning_bt
                    (parcel_id, zoning_code, geom, valid_range, txn_range, corrected)
                VALUES (%s, %s, ST_GeomFromText(%s, 4326),
                        tstzrange(%s, 'infinity', '[)'),
                        tstzrange(%s, 'infinity', '[)'), true)
                """,
                (parcel_id, corrected_code, geom_wkt,
                 corrected_effective_from, now),
            )
        except psycopg.errors.ExclusionViolation as exc:
            conn.rollback()
            raise RuntimeError(
                f"Correction for {parcel_id} overlaps an existing belief; "
                f"check the effective date: {exc}"
            )
    conn.commit()

Because step 1 closed the old belief’s txn_range, the exclusion constraint no longer sees the R-1 and MU-2 versions as conflicting — they are believed in different transaction windows — so the insert succeeds while the R-1 row survives untouched.

Verification & testing

Prove the correction preserved the old answer instead of masking it:

  • The old report still reproduces. Assert that as_of(parcel_id, "2026-03-12", known_at=<original run ts>) returns R-1 after the correction has been applied. If it returns MU-2, a version’s txn_range was left open when it should have been closed.
  • Current knowledge is corrected. Assert as_of(parcel_id, "2026-03-12") (no known_at) returns MU-2. Both assertions passing simultaneously is the whole point of the model.
  • No row was mutated. Snapshot the R-1 row’s version_id, zoning_code, and geom before and after the correction and assert they are byte-identical; only its txn_range upper bound may change.
  • The constraint actually fires. Deliberately insert an overlapping valid-time version within the same open transaction window and assert an ExclusionViolation. A silent success means btree_gist is missing or the constraint was never created.
  • Containment covers the boundary. Query exactly at 1 Mar 00:00. Half-open [) ranges put the boundary instant in the new version, not the old one — confirm the answer flips exactly there and not a day off.

Failure recovery

When a correction goes wrong mid-batch, recover without compounding the damage:

  • Roll back, never patch forward. If the INSERT raises an ExclusionViolation, the transaction rolls back whole; do not try to “fix” it with a manual UPDATE, which is how history gets silently mutated. Investigate the effective date first — an overlap almost always means the correction’s valid_range collides with a version you forgot to close.
  • Reopen a wrongly-closed belief. If you closed the wrong version’s txn_range, do not delete anything. Insert a corrective transaction-time slice that re-asserts the intended belief; the erroneous closure remains on record, which is itself the audit trail a reviewer expects.
  • Reconcile against the immutable snapshot. When the live table’s history looks inconsistent, rebuild the affected parcels from the versioned GeoParquet snapshot described in temporal versioning & snapshots rather than trusting a hand-edit.
  • Keep as-of queries fast during recovery. A recovery batch can bloat the history table; if as-of queries start table-scanning, revisit the GiST index strategy in spatial database indexing & performance before the backlog grows further.
  • Alert on correction volume. Emit the corrected=true insert rate; a spike usually means an upstream feed is republishing effective dates, not that dozens of parcels were genuinely re-zoned.

Frequently asked questions

Why not just keep an audit table with triggers instead of two tstzrange columns?

A trigger-based audit log tells you when a row changed but not when the zoning was effective, so it cannot answer effective-date questions and cannot represent a back-dated correction as anything but “a later edit.” Bitemporal ranges make both axes first-class and queryable with the same as_of call, and the exclusion constraint enforces timeline integrity that triggers cannot.

Does the exclusion constraint block legitimate corrections?

No, because corrections are believed in a different transaction-time window. The constraint only forbids two versions overlapping in valid time within the same transaction-time slice. Closing the old belief’s txn_range before inserting the correction is what keeps the constraint satisfied while both facts coexist in the table.

Should geometry changes create a new version like code changes?

If a correction moves the polygon — a datum fix or a survey adjustment — treat it as a new version so the geometry timeline is queryable too. The one nuance is distinguishing a real boundary change from a reprojection artifact; only version geometry that differs after both versions are in the same CRS, or you will churn history on cosmetic differences.

How do I reproduce a report from a specific past date exactly?

Store the transaction-time instant of each report when you generate it, then pass that instant as known_at to the as-of query. The query filters txn_range to that window and returns precisely the versions you believed then, regardless of how many corrections have landed since.