How to Set Up PostGIS for Emergency Response

A county emergency operations center stands up a fresh PostGIS instance at the start of a flood activation, and within the first operational period three feeds — sheriff GPS tracks in WGS 84, a parcel layer in NAD83 State Plane, and a tactical overlay in a localized UTM grid — land in the same incidents table. The first spatial join to render a combined common operating picture either errors with Operation on mixed SRID geometries or, worse, returns silently misaligned geometry that places a shelter marker on the wrong block. The single narrow failure this page solves is the one that derails a new emergency PostGIS deployment every time: a database initialized without an enforced single operational Coordinate Reference System for disaster zones, so SRID fragmentation and unindexed geometry surface as data corruption and query collapse exactly when command needs the map most.

Root Cause and Operational Impact

The danger is not that PostGIS cannot hold multiple coordinate systems — it can. The danger is that a default install does nothing to force convergence, so the failure is deferred until the worst possible moment. Three root causes compound:

No SRID constraint on the geometry column. A column declared geometry (with no type/SRID modifier) accepts a point in EPSG:4326 and a polygon in EPSG:2229 into adjacent rows. ST_Intersects, ST_DWithin, and every spatial join then throw a mixed-SRID error mid-incident, or — if the application strips SRIDs first — return geometrically meaningless results.
No spatial index, or a deferred one. Without a GIST index, every bounding-box filter degrades to a sequential scan. Under surge load, when six agencies are each panning the map and re-querying, sequential scans saturate the database and the common operating picture stops refreshing.
Non-transactional ingestion over an unstable WAN. Field connectivity drops mid-COPY, a partial batch commits, and the operational table now holds half a feed with no way to tell which rows are missing.

In a routine GIS shop these are tuning notes for next sprint. In an active incident they are hazards: a misaligned hazard polygon moves an evacuation hold line across a road, a stalled query blanks the situational map during a tactical push, and a half-ingested feed corrupts multi-agency data fusion because every downstream consumer trusts the table without re-checking it. The setup has to enforce convergence at the schema level, index immediately, and ingest transactionally — not best-effort.

Tiered Resolution Strategy

Work the build from the definitive fix down to a safe default that never ships a silently broken table:

Definitive fix — constrain the SRID at the DDL level and index immediately. Declare every geometry column with an explicit type and SRID (geometry(Point, 4326)), add CHECK (ST_IsValid(geom)), and attach the GIST index in the same migration. A row in the wrong projection cannot be inserted at all; a query cannot fall back to a sequential scan.
Normalize at the boundary with a staging schema and trigger. Land raw feeds in a staging schema that preserves native SRIDs, then promote to operational through a BEFORE INSERT OR UPDATE trigger that runs ST_MakeValid and ST_Transform into the single operational CRS. This isolates dirty input from the authoritative table.
Ingest inside explicit transactions with conflict handling. Wrap every load in BEGIN/COMMIT, use ON CONFLICT for idempotent re-sends, and roll back partial batches so a dropped WAN link leaves the table consistent.
Safe default with an audit flag. When a row’s SRID cannot be confirmed or its geometry cannot be repaired, do not guess and do not drop it. Route it to a staging.rejects audit table with the raw payload and a loud reason code, and quarantine it from the operational picture. A flagged row is recoverable in the next operational period; a silently mis-projected one that reaches a responder’s tablet is not.

Production Python Implementation

The implementation below provisions the operational schema with an SRID-constrained, indexed geometry column, then ingests a feed transactionally. Geometry is repaired with ST_MakeValid and reprojected with ST_Transform inside a single statement; always_xy-style axis safety is handled server-side because the SRID is explicit on both sides of the transform. Unconfirmable rows are written to an audit table rather than dropped. It uses connection pooling, structured logging, and explicit exception boundaries throughout — no print statements, no silent fallbacks. This same transactional discipline is what every downstream geospatial data ingestion pipeline on the platform depends on.

python

import json
import logging
from datetime import datetime, timezone
from typing import Any

import psycopg2
from psycopg2 import sql
from psycopg2.extras import execute_batch
from psycopg2.pool import ThreadedConnectionPool

logging.basicConfig(level=logging.INFO, format="%(asctime)s | %(levelname)s | %(message)s")
logger = logging.getLogger("postgis_incident_setup")

OPERATIONAL_SRID = 4326  # single converged CRS for the common operating picture


class IncidentDBError(Exception):
    """Raised when the operational PostGIS table cannot be provisioned or loaded safely."""


def provision_operational_schema(pool: ThreadedConnectionPool, srid: int = OPERATIONAL_SRID) -> None:
    """
    Create the staging/operational schemas, an SRID-constrained geometry column, the
    validity CHECK, and the GIST index in one transaction. The SRID modifier on the
    column is what makes a wrong-projection insert impossible rather than deferred.
    """
    ddl = sql.SQL(
        """
        CREATE SCHEMA IF NOT EXISTS staging;
        CREATE SCHEMA IF NOT EXISTS operational;

        CREATE TABLE IF NOT EXISTS operational.incidents (
            id          TEXT PRIMARY KEY,
            agency_id   TEXT NOT NULL,
            reported_at TIMESTAMPTZ NOT NULL,
            accuracy_m  DOUBLE PRECISION,
            updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            geom        geometry(Point, {srid}) NOT NULL
                        CONSTRAINT incidents_geom_valid CHECK (ST_IsValid(geom))
        );

        CREATE INDEX IF NOT EXISTS idx_incidents_geom
            ON operational.incidents USING GIST (geom);

        CREATE TABLE IF NOT EXISTS staging.rejects (
            received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            reason      TEXT NOT NULL,
            raw_payload JSONB NOT NULL
        );
        """
    ).format(srid=sql.Literal(srid))

    conn = pool.getconn()
    try:
        with conn:  # commits on success, rolls back on any exception
            with conn.cursor() as cur:
                cur.execute(ddl)
        logger.info("Operational schema provisioned with SRID %d, validity CHECK, GIST index.", srid)
    except psycopg2.Error as exc:
        raise IncidentDBError(f"Schema provisioning failed: {exc.pgerror or exc}") from exc
    finally:
        pool.putconn(conn)


def _quarantine(cur: psycopg2.extensions.cursor, reason: str, record: dict[str, Any]) -> None:
    """Write an unconfirmable row to the audit table instead of dropping it."""
    cur.execute(
        "INSERT INTO staging.rejects (reason, raw_payload) VALUES (%s, %s)",
        (reason, json.dumps(record)),
    )
    logger.warning("Quarantined record %s: %s", record.get("id", "<no-id>"), reason)


def ingest_feed(pool: ThreadedConnectionPool, feed: list[dict[str, Any]],
                target_srid: int = OPERATIONAL_SRID) -> tuple[int, int]:
    """
    Transactionally ingest a feed. Each row is repaired (ST_MakeValid) and reprojected
    (ST_Transform) into the operational SRID in one statement; rows whose source SRID is
    unknown or whose geometry is unrepairable are quarantined with an audit reason rather
    than silently dropped. Returns (loaded, quarantined).
    """
    upsert = sql.SQL(
        """
        INSERT INTO operational.incidents (id, agency_id, reported_at, accuracy_m, geom)
        VALUES (
            %(id)s, %(agency)s, %(ts)s, %(acc)s,
            ST_Transform(ST_MakeValid(ST_GeomFromText(%(wkt)s, %(src_srid)s)), %(target)s)
        )
        ON CONFLICT (id) DO UPDATE
            SET geom = EXCLUDED.geom,
                accuracy_m = EXCLUDED.accuracy_m,
                updated_at = NOW()
        """
    )

    loaded = 0
    quarantined = 0
    conn = pool.getconn()
    try:
        with conn:
            with conn.cursor() as cur:
                for record in feed:
                    src_srid = record.get("srid")
                    wkt = record.get("geometry")
                    if not src_srid or not wkt:
                        _quarantine(cur, "missing source SRID or geometry", record)
                        quarantined += 1
                        continue
                    try:
                        # Savepoint isolates a bad row so one failure cannot abort the batch.
                        cur.execute("SAVEPOINT row_sp")
                        cur.execute(upsert, {
                            "id": record["id"],
                            "agency": record["agency"],
                            "ts": record["timestamp"],
                            "acc": record.get("accuracy"),
                            "wkt": wkt,
                            "src_srid": int(src_srid),
                            "target": target_srid,
                        })
                        cur.execute("RELEASE SAVEPOINT row_sp")
                        loaded += 1
                    except psycopg2.Error as exc:
                        cur.execute("ROLLBACK TO SAVEPOINT row_sp")
                        _quarantine(cur, f"geometry rejected: {exc.pgcode}", record)
                        quarantined += 1
        logger.info("Ingest complete: %d loaded, %d quarantined.", loaded, quarantined)
        return loaded, quarantined
    except psycopg2.Error as exc:
        raise IncidentDBError(f"Transactional ingest aborted: {exc.pgerror or exc}") from exc
    finally:
        pool.putconn(conn)


if __name__ == "__main__":
    dsn = "dbname=incident user=eoc_writer host=db.eoc.local sslmode=require"
    db_pool = ThreadedConnectionPool(minconn=2, maxconn=16, dsn=dsn)
    try:
        provision_operational_schema(db_pool)
        sample = [
            {"id": "INC-001", "agency": "sheriff", "timestamp": datetime.now(timezone.utc).isoformat(),
             "geometry": "POINT(-118.2437 34.0522)", "srid": 4326, "accuracy": 5.0},
            {"id": "INC-002", "agency": "public_works", "timestamp": datetime.now(timezone.utc).isoformat(),
             "geometry": "POINT(6485000 1840000)", "srid": 2229, "accuracy": 1.0},  # NAD83 State Plane CA V
        ]
        ingest_feed(db_pool, sample)
    finally:
        db_pool.closeall()

Validation Checklist

Verify each item before the database backs a live common operating picture:

SELECT type, srid FROM geometry_columns WHERE f_table_name = 'incidents'; reports the single operational SRID — not 0 and not a mix.
The geometry column was declared with an explicit type and SRID (geometry(Point, 4326)), so a wrong-projection insert is rejected by the column, not just by application code.
CHECK (ST_IsValid(geom)) is present and enforced; an invalid ring cannot reach the operational table.
\d operational.incidents shows the GIST index, and EXPLAIN (ANALYZE, BUFFERS) on a bounding-box query uses an Index Scan, not a Seq Scan.
Ingestion runs inside an explicit transaction; killing the loader mid-batch leaves zero partial rows in operational.incidents.
A deliberately bad row (unknown SRID, self-intersecting polygon) lands in staging.rejects with a reason code rather than being dropped or aborting the batch.
Re-sending the same feed is idempotent — ON CONFLICT updates rather than duplicating, and no row collapses toward null-island (0, 0).

Edge Cases and Gotchas

Axis-order inversion on import. ST_GeomFromText(..., src_srid) trusts the WKT coordinate order you give it. Feeds delivered as GML or from authorities that publish lat/lon order can arrive swapped; PostGIS will faithfully store the swap. Spot-check one known point after import — a swapped Southern California incident lands in the Indian Ocean.

Null-island drift. A row with srid set but coordinates of (0, 0), or a ST_Transform from an unset/zero SRID, parks geometry at the equator/prime-meridian intersection. Treat any feature within a degree of (0, 0) as a failed ingest until proven otherwise, and add a CHECK excluding the origin if your operating area is far from it.

Agency-specific datum anomalies. Legacy parcel layers often carry NAD27 or an older local datum while live GPS feeds use NAD83(2011) or ITRF2014. ST_Transform between them needs the correct datum shift grids installed in spatial_ref_sys / PROJ; a missing grid silently falls back to a lower-accuracy transform and offsets the layer by metres. This is the same discipline applied when handling missing CRS in field-collected GPS logs before they reach the database.

Offline device quirks and degraded-mode reads. When WAN latency spikes or replication lags, point field clients at a read replica and serve the last-good picture from a local cache rather than blocking on the primary. Exporting the operational table to a local store ties this setup into broader offline GIS data caching strategies so responders keep spatial visibility through a backhaul outage.

Index bloat under surge. High-churn upsert traffic bloats the GIST index over a long activation. REINDEX INDEX CONCURRENTLY idx_incidents_geom; rebuilds it without locking the table; watch pg_stat_activity for the blocking analytical query before you reindex, not after.

Up: Coordinate Reference Systems for Disaster Zones overview