Storing Failed Geometries in a PostGIS Dead-Letter Queue

To store a failed geotask durably, write it to a PostGIS dead-letter table that keeps the geometry as a real geometry column with an explicit SRID, the raw payload as jsonb, and enough failure context — error text, attempt count, first-seen timestamp — to triage it later. Unlike a broker-only DLQ, a spatial DLQ table lets you run ST_IsValid, bounding-box, and CRS queries over your failures, so you can group them by region or projection before deciding how to replay. This page gives you the table DDL, a working insert built from ST_GeomFromWKB with its EPSG code, and the index that makes triage fast.

It is a concrete companion to Dead-Letter Queues for Failed Geotasks, which frames the broader routing pattern; here the queue is a queryable PostGIS relation.

When to Use This Pattern#

Choose a PostGIS-backed DLQ over a plain message broker when:

• Your failures are individual features or geometries, not opaque byte blobs, and you want to inspect them spatially.
• Triage means asking spatial questions — “which failures fall inside this tile?”, “which are in EPSG:4326 vs EPSG:3857?”, “which have invalid rings?”.
• You already run PostGIS as a pipeline sink, so a DLQ table needs no new infrastructure.
• You need a durable audit trail with per-row attempt counters and timestamps for compliance.

Designing the Table#

The table has to answer three questions for every failure: what geometry failed, what was the original request, and why and when did it fail. Model the geometry as a typed geometry column so PostGIS functions work directly on it, and pin the SRID at the column level only if every failure shares one CRS — otherwise store mixed CRSes and record each row’s EPSG in a plain column.

CREATE TABLE failed_geotasks (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    task_id      TEXT        NOT NULL,
    geom         geometry(Geometry, 0) NOT NULL,  -- SRID 0 = mixed; set per row
    epsg         INTEGER     NOT NULL,            -- source EPSG of this geometry
    payload      JSONB       NOT NULL,            -- raw request as received
    error        TEXT        NOT NULL,            -- classified error message
    error_class  TEXT        NOT NULL DEFAULT 'fatal',  -- transient | fatal
    attempts     INTEGER     NOT NULL DEFAULT 1,
    geom_hash    TEXT        NOT NULL,            -- dedup key for safe replay
    first_seen   TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen    TIMESTAMPTZ NOT NULL DEFAULT now(),
    status       TEXT        NOT NULL DEFAULT 'pending'  -- pending | replayed | quarantined
);

The geometry(Geometry, 0) type accepts any geometry type at SRID 0, letting one table hold points, polygons, and multipolygons from providers using different CRSes; the per-row epsg column preserves each feature’s true reference system so replay can reproject correctly. Storing payload as jsonb (not json) enables GIN indexing and key-level queries during triage.

Complete Working Example#

The insert accepts a failed feature’s Well-Known Binary, its EPSG code, and the original payload, then writes one DLQ row. It computes a stable geometry hash for later de-duplication and upserts on (task_id, geom_hash) so a re-failure of the same feature bumps attempts rather than creating a duplicate.

from __future__ import annotations

import hashlib
import json
from typing import Any

import psycopg
from shapely.geometry.base import BaseGeometry


def geom_hash(geom: BaseGeometry, epsg: int) -> str:
    """Stable content hash of the geometry + its CRS for dedup on replay."""
    payload = f"{epsg}:{geom.wkb_hex}".encode()
    return hashlib.sha256(payload).hexdigest()


def store_failed_geotask(
    conn: psycopg.Connection,
    *,
    task_id: str,
    geom: BaseGeometry,
    epsg: int,
    payload: dict[str, Any],
    error: str,
    error_class: str = "fatal",
) -> None:
    wkb: bytes = geom.wkb  # binary geometry, CRS carried separately in `epsg`
    ghash: str = geom_hash(geom, epsg)

    sql = """
        INSERT INTO failed_geotasks
            (task_id, geom, epsg, payload, error, error_class, geom_hash)
        VALUES
            (%(task_id)s,
             ST_SetSRID(ST_GeomFromWKB(%(wkb)s), %(epsg)s),
             %(epsg)s, %(payload)s, %(error)s, %(error_class)s, %(ghash)s)
        ON CONFLICT (task_id, geom_hash) DO UPDATE
            SET attempts  = failed_geotasks.attempts + 1,
                last_seen = now(),
                error     = EXCLUDED.error;
    """
    with conn.cursor() as cur:
        cur.execute(
            sql,
            {
                "task_id": task_id,
                "wkb": wkb,
                "epsg": epsg,
                "payload": json.dumps(payload),
                "error": error,
                "error_class": error_class,
                "ghash": ghash,
            },
        )
    conn.commit()

ST_GeomFromWKB parses the raw binary and ST_SetSRID stamps it with the feature’s real EPSG — passing the SRID to both the constructor and the column keeps the stored geometry spatially meaningful, so a later ST_Transform during replay reprojects from the correct source. The ON CONFLICT clause needs a matching unique constraint; add it and the triage index next.

-- Uniqueness that powers the idempotent upsert above
ALTER TABLE failed_geotasks
    ADD CONSTRAINT uq_failed_geotasks_task_geom UNIQUE (task_id, geom_hash);

-- Triage index: most DLQ scans filter pending rows by error class, newest first
CREATE INDEX ix_failed_geotasks_triage
    ON failed_geotasks (status, error_class, first_seen DESC);

-- Spatial index for region-scoped triage (ST_Intersects, ST_Within)
CREATE INDEX ix_failed_geotasks_geom
    ON failed_geotasks USING GIST (geom);

Parameter & Option Reference#

Verification & Testing#

Confirm the round trip preserves both geometry and CRS. Insert a known polygon in EPSG:4326, then read it back and check the SRID and validity:

-- Every stored geometry should carry a real SRID, never 0
SELECT id, task_id, ST_SRID(geom) AS srid, ST_IsValid(geom) AS valid
FROM failed_geotasks
WHERE status = 'pending'
ORDER BY first_seen DESC
LIMIT 20;

-- Triage by CRS: how many failures per source projection?
SELECT epsg, count(*) AS n
FROM failed_geotasks
WHERE status = 'pending'
GROUP BY epsg
ORDER BY n DESC;

-- Region-scoped triage using the GIST index
SELECT count(*)
FROM failed_geotasks
WHERE ST_Intersects(
        geom,
        ST_MakeEnvelope(-124.5, 32.5, -114.0, 42.0, 4326)
      );

ST_SRID(geom) must return the EPSG you inserted, not 0 — if it returns 0 you forgot ST_SetSRID and every downstream ST_Transform will fail or silently mis-project. From the CLI, ogrinfo -so PG:"dbname=gis" failed_geotasks confirms the layer’s geometry type and declared SRID outside Python.

Common Pitfalls#

• Storing geometry without a SRID. A geom column full of SRID-0 rows cannot be reprojected on replay; always ST_SetSRID to the feature’s true EPSG at insert time.
• Using ST_GeomFromText on binary payloads. Failed features usually arrive as WKB; round-tripping through WKT loses precision and coordinate dimensionality. Parse the binary with ST_GeomFromWKB.
• No dedup key. Without geom_hash, a feature that fails repeatedly spawns duplicate rows and inflates replay counts; the upsert plus unique constraint keeps one row per feature.
• json instead of jsonb. The json type cannot be GIN-indexed, so payload-key triage on a large DLQ degrades to sequential scans.

Related: Once rows land here, drain them with reprocessing dead-letter geotasks safely, only route to this table after exponential backoff for API rate limits is exhausted, key replays with idempotency keys in spatial ETL, and shed load upstream using circuit breakers for external WMS services. This all sits under Resilience & Failure Handling for GIS Pipelines.

← Back to Dead-Letter Queues for Failed Geotasks