PostGIS SRIDs Explained: Why Your Spatial Queries Return Wrong Results

2026-03-13

PostGIS SRIDs Explained: Why Your Spatial Queries Return Wrong Results

The Problem

Every geometry stored in PostGIS has an SRID -- a Spatial Reference Identifier that tells PostGIS what coordinate system the data uses. SRID 4326 means WGS 84 (latitude/longitude in degrees, the system GPS uses). SRID 3857 means Web Mercator (the projection Google Maps renders). SRID 32632 means UTM zone 32N (meters, accurate for a narrow longitude band). Each system defines different units, different origin points, and different distortion characteristics.

When the SRID is wrong, every spatial calculation is wrong. Not error-wrong -- silently-wrong. PostGIS returns a number, your application uses that number, and nobody realises the answer is off by orders of magnitude until someone checks the real world.

Three specific SRID problems account for most spatial query bugs:

SRID 0 (unknown/unset). When you insert geometry without specifying an SRID, PostGIS stores it with SRID 0. This means "I don't know what coordinate system this is." PostGIS will not transform SRID 0 data to any other system -- ST_Transform fails. Functions like ST_Distance still run, but the result is in whatever unit the raw coordinates happen to be. If the coordinates are degrees, the "distance" is in degrees -- meaningless for real-world measurement.

Mixed SRIDs in the same column. This happens when data is loaded from multiple sources without transforming to a common SRID first. Half the rows use SRID 4326 (degrees), the other half use SRID 32632 (meters). Spatial joins between these rows produce nonsense -- PostGIS computes the distance between a point at (51.5, -0.1) degrees and a point at (500000, 5700000) meters as if they are in the same coordinate space.

SRID 3857 (Web Mercator) used for distance calculations. Web Mercator is a projection designed for rendering map tiles, not measuring distance. It distorts areas and distances increasingly as you move away from the equator. At 60 degrees latitude, distances are doubled. ST_Distance on SRID 3857 data in London returns a number roughly 60% larger than the actual distance. In Scandinavia, it is 100% off. The query runs, returns a plausible-looking number, and the application happily uses it.

How to Detect It

Start by auditing the SRIDs registered for all geometry columns:

-- List SRIDs for every geometry column
SELECT f_table_name, f_geometry_column, srid, type
FROM geometry_columns
ORDER BY f_table_name;

-- Find columns with SRID 0 (unknown/unset)
SELECT f_table_name, f_geometry_column
FROM geometry_columns
WHERE srid = 0;

SRID 0 is always a problem. Any column showing SRID 0 means the geometry was stored without coordinate system metadata, and every spatial function operating on it is producing unreliable results.

Next, check for mixed SRIDs within individual columns. The geometry_columns view only shows the declared SRID -- the actual data can contain rows with different SRIDs:

-- Find mixed SRIDs within a single column
SELECT
    'points_of_interest' AS table_name,
    ST_SRID(geom) AS srid,
    count(*) AS row_count
FROM points_of_interest
GROUP BY ST_SRID(geom)
HAVING count(DISTINCT ST_SRID(geom)) > 1;

If this query returns multiple rows for the same table, the column contains geometry in different coordinate systems. Every spatial operation on those rows is mixing incompatible units.

Finally, identify columns using SRID 3857 that might be used in distance or area calculations:

-- Find non-standard SRIDs that may indicate projection issues
SELECT f_table_name, f_geometry_column, srid
FROM geometry_columns
WHERE srid NOT IN (4326, 3857)
ORDER BY f_table_name;

How myDBA.dev Shows It

PostGIS page showing SRID distribution across columns and mixed-SRID warnings

myDBA.dev's PostGIS monitoring page surfaces SRID configuration for every geometry column in one view. You can see the declared SRID for each column, identify columns stuck at SRID 0, and spot mixed-SRID situations where the column-level SRID does not match the data. This catches SRID problems at the schema level before they corrupt query results -- you do not need to run diagnostic queries table by table.

Health Check showing SRID-related warnings and mixed-SRID detection

The Health Check runs targeted SRID validation rules automatically. It flags columns with SRID 0 as critical findings, warns about mixed SRIDs within columns, and identifies columns using Web Mercator (3857) that may be involved in distance calculations. Each finding includes the table, column, and specific SRID values found -- giving you a direct path to the rows that need correction.

How to Fix It

The fix depends on which problem you have. For SRID 0 data where you know the actual coordinate system, assign the correct SRID and then update the column metadata:

-- Transform existing data to a standard SRID
UPDATE points_of_interest
SET geom = ST_Transform(geom, 4326)
WHERE ST_SRID(geom) != 4326;

-- Update the column's declared SRID
SELECT UpdateGeometrySRID('public', 'points_of_interest', 'geom', 4326);

Note the distinction between ST_SetSRID and ST_Transform. ST_SetSRID relabels the SRID without changing coordinates -- use it when the coordinates are already in the target system but the SRID tag is wrong (or zero). ST_Transform reprojects the coordinates from one system to another -- use it when the coordinates are genuinely in a different system (e.g., converting UTM meters to WGS 84 degrees).

For mixed-SRID columns, transform all rows to a single SRID and add a constraint to prevent future mismatches:

-- Enforce a single SRID going forward
ALTER TABLE points_of_interest
ADD CONSTRAINT enforce_srid CHECK (ST_SRID(geom) = 4326);

This constraint rejects any INSERT or UPDATE that attempts to store geometry with a different SRID. It is lightweight -- ST_SRID extracts the SRID from the geometry header without parsing coordinates.

For distance calculations on Web Mercator data, transform to geography or use ST_Transform to convert to an appropriate local projection before measuring:

-- Accurate distance using geography cast
SELECT ST_Distance(geom::geography, other_geom::geography) AS distance_meters
FROM locations;

Casting to geography tells PostGIS to compute geodesic distance on the WGS 84 ellipsoid -- accurate anywhere on Earth regardless of the source projection.

How to Prevent It

Standardise on SRID 4326 (WGS 84) for storage. It is the most widely supported SRID, compatible with every mapping library and API, and works correctly with PostGIS geography operations for accurate distance and area calculations.

For data imports, always transform to the target SRID during the load step rather than after:

-- During COPY or INSERT, transform at the point of entry
INSERT INTO locations (name, geom)
VALUES ('Office', ST_Transform(ST_GeomFromText('POINT(500000 5700000)', 32632), 4326));

Add CHECK constraints on every geometry column that enforce the expected SRID. This is a one-line addition to each table definition and prevents mixed-SRID data from entering the system at all.

Know the common SRIDs and when to use each:

| SRID | Name | Units | Use Case | |------|------|-------|----------| | 4326 | WGS 84 | Degrees | Storage, GPS data, web APIs | | 3857 | Web Mercator | Meters (distorted) | Map tile rendering only -- NOT distance | | 326xx | UTM zones | Meters (accurate) | Local-area measurement and analysis |

Monitor SRID consistency continuously. myDBA.dev flags SRID 0 columns and mixed-SRID data in its health checks, catching configuration drift as new data sources are integrated. The cost of fixing SRID problems grows with table size -- a CHECK constraint on day one is far cheaper than transforming 50 million rows six months later.