Auditing SRID Consistency Across a Database

Problem Statement

Spatial predicates between geometries in different SRIDs raise Operation on mixed SRID geometries, and the error arrives at query time rather than at insert time. In a database with two dozen geometry columns accumulated over years of imports, nobody can say with confidence which columns hold what. This page produces that answer, as the diagnostic complement to in-place SRID reprojection.

Why the Naive Approach Fails

Reading geometry_columns looks like the audit and is only half of it. That view reports what each column declares; it says nothing about what the rows actually contain, and on an untyped column the declaration is 0 regardless of the data.

Declared against actual, four columns Four geometry columns. The first declares 4326 and holds 4326 — correct. The second declares 0 because it is untyped and holds a mixture of 4326 and 3857 — the dangerous case. The third declares 3857 and holds 3857 but was reprojected without updating documentation. The fourth declares 4326 and holds SRID 0 rows from a loader that dropped it. What the catalogue says, and what the rows say declared actual parcels.geom 4326 4326 consistent imports.shape 0 (untyped) 4326 + 3857 mixed tiles.geom_web 3857 3857 consistent legacy.point 4326 0 unlabelled data Only rows two and four are problems, and neither is visible from the catalogue alone.

Production-Ready Implementation

The audit query builds itself: it reads the catalogue, then generates and runs a probe against each column.

python
from __future__ import annotations

from dataclasses import dataclass

import psycopg


@dataclass(frozen=True)
class ColumnAudit:
    table: str
    column: str
    declared_srid: int
    actual_srids: dict[int, int]      # srid -> row count
    typed: bool

    @property
    def status(self) -> str:
        if not self.typed:
            return "untyped" if len(self.actual_srids) <= 1 else "mixed"
        if set(self.actual_srids) - {self.declared_srid}:
            return "mismatch"
        return "ok"


CATALOGUE = """
    SELECT f_table_schema || '.' || f_table_name AS tbl,
           f_geometry_column                     AS col,
           srid,
           coord_dimension
    FROM geometry_columns
    ORDER BY 1, 2
"""


def audit(dsn: str, sample_rows: int = 100_000) -> list[ColumnAudit]:
    results: list[ColumnAudit] = []
    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(CATALOGUE)
            columns = cur.fetchall()

        for tbl, col, declared, _dim in columns:
            with conn.cursor() as cur:
                # sample rather than scan: a mixed column shows itself quickly
                cur.execute(f"""
                    SELECT ST_SRID({col}) AS srid, count(*)
                    FROM (SELECT {col} FROM {tbl}
                          WHERE {col} IS NOT NULL LIMIT %s) s
                    GROUP BY 1 ORDER BY 2 DESC
                """, (sample_rows,))
                actual = {srid: n for srid, n in cur.fetchall()}

                cur.execute("""
                    SELECT format_type(a.atttypid, a.atttypmod) LIKE '%%,%%'
                    FROM pg_attribute a
                    WHERE a.attrelid = %s::regclass AND a.attname = %s
                """, (tbl, col))
                typed = bool(cur.fetchone()[0])

            results.append(ColumnAudit(tbl, col, declared, actual, typed))
    return results

The table and column names come from geometry_columns rather than from user input, which is what makes the interpolation safe — every value in the loop originates in the database’s own catalogue.

Fixing what it finds has two forms. Where the column is untyped and the data is uniform, add the type modifier:

sql
-- rewrite-free on PostgreSQL 12+ when the data already matches
ALTER TABLE imports
    ALTER COLUMN shape TYPE geometry(Polygon, 4326)
    USING ST_SetSRID(shape, 4326);

Where a rewrite is not schedulable, a check constraint enforces the same invariant without one:

sql
ALTER TABLE imports
    ADD CONSTRAINT imports_shape_srid
    CHECK (shape IS NULL OR ST_SRID(shape) = 4326) NOT VALID;

ALTER TABLE imports VALIDATE CONSTRAINT imports_shape_srid;
Three remedies, three costs A check constraint is cheap and enforces the rule but leaves the declaration wrong. A type modifier change with matching data is a catalogue-only change and fixes both. A reprojection is a full data migration and is only needed when the data itself is in the wrong system. Match the remedy to what is actually wrong data is right, column is untyped ALTER COLUMN … TYPE geometry(Polygon, 4326) — catalogue-only when data matches cannot schedule a type change yet ADD CONSTRAINT … CHECK (ST_SRID(geom) = 4326) NOT VALID, then VALIDATE data is genuinely in the wrong system a full reprojection — add a column, backfill, index, switch reads

Configuration and Tuning Knobs

Sample size trades certainty against runtime. A hundred thousand rows finds any mixture that occurs in more than a fraction of a percent, and completes in seconds even on a large table. For a definitive answer before adding a constraint, drop the LIMIT — the constraint validation is going to scan everything anyway.

Schema scope matters in a database with staging or archive schemas. geometry_columns covers all of them, which is usually what you want for an audit and occasionally produces a lot of noise from tables nobody uses.

Frequency: run it after every schema change and on a schedule. Once the constraints are in place the audit should be permanently clean, which makes any new finding a signal that something bypassed the intended path.

What the audit found across 26 columns Twenty-six geometry columns audited: nineteen consistent and typed, four untyped but holding uniform data, two untyped and holding mixed SRIDs, and one typed 4326 while holding SRID 0 rows. Twenty-six geometry columns, four verdicts typed and consistent 19 — nothing to do untyped, uniform data 4 — add the type modifier, catalogue-only untyped, mixed SRIDs 2 — reproject the minority, then type it typed, unlabelled data 1 — a loader dropped the SRID Only the bottom two rows need data changes. The rest is a catalogue fix and a constraint.

Verification Steps

sql
-- every geometry column, with its declared SRID and its type-modifier status
SELECT f_table_schema, f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE srid = 0
ORDER BY 1, 2;
-- expect: no rows

-- and every column now carries an enforcing constraint or type modifier
SELECT c.relname, a.attname,
       format_type(a.atttypid, a.atttypmod) AS declared_type
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'geometry' AND a.attnum > 0 AND NOT a.attisdropped
  AND format_type(a.atttypid, a.atttypmod) = 'geometry';
-- expect: no rows — every geometry column should carry a type modifier

The second query is the durable one. A geometry column whose declared type is the bare word geometry is a column that can drift, and eliminating those is what turns this audit from a recurring chore into a one-off.

Gotchas Checklist

  • geometry_columns is a view over the catalogue, not a table. In PostGIS 2.0 and later it is derived automatically, so it cannot be out of date — but it reports declarations, never contents.
  • SRID 0 is not “no SRID”; it is a distinct SRID that matches only itself. Predicates between SRID 0 and SRID 4326 geometry error rather than assuming.
  • ST_SetSRID relabels; ST_Transform reprojects. Using the first when you needed the second leaves coordinates in the old system with a new label, which is the worst possible state and completely silent.
  • Adding a type modifier can require a rewrite. It is catalogue-only when the existing data already satisfies the new type; otherwise it rewrites, so check first with the audit.
  • Geography columns have their own story. They are always SRID 4326 in practice, so they do not drift — but they appear in a separate view, geography_columns, which an audit querying only geometry_columns will miss.

Keeping the Audit From Rotting

A one-off audit finds the drift that has already happened. It does nothing about the drift that starts the following week, and a report nobody reruns is indistinguishable from no report at all.

Two mechanisms keep the result durable, and they work at different levels.

The first is the column typmod. Declaring a column as geometry(Point, 4326) rather than bare geometry makes PostGIS reject any insert carrying a different identifier, which converts a silent data problem into an immediate, loud, obviously-located error. This is the strongest available guarantee and costs nothing at query time. Applying it to an existing column is an ALTER TABLE ... ALTER COLUMN ... TYPE that rewrites the table, so it belongs in a planned migration rather than a hotfix — but once applied, that column can never drift again.

The second is a check in continuous integration. The audit query that walks geometry_columns and compares each declared identifier against the values actually stored is fast enough to run on every build against a seeded test database, and failing the build on a mismatch stops the drift at the pull request rather than in production. The same query pointed at production on a weekly schedule catches the case the test database cannot: data arriving from an external loader that bypasses the application entirely.

Both mechanisms need an explicit statement of what the correct identifier is. That belongs in one place — a table, a constant, or a comment on the column, but exactly one — because two sources of truth for a projection is how the inconsistency starts.

It is also worth auditing spatial_ref_sys itself. Some deployments carry hand-edited rows there, and a locally modified projection definition means the same identifier produces different coordinates on different servers, which no amount of column-level checking will reveal.