DEV Community

Cover image for PostGIS SRIDs Explained: Why Your Spatial Queries Return Wrong Results
Philip McClarence
Philip McClarence

Posted on

PostGIS SRIDs Explained: Why Your Spatial Queries Return Wrong Results

#database #postgres #tutorial #sql

PostGIS SRIDs Explained: Why Your Spatial Queries Return Wrong Results

Every geometry in PostGIS carries an SRID -- a Spatial Reference Identifier that defines its coordinate system. SRID 4326 is WGS 84 (GPS coordinates in degrees). SRID 3857 is Web Mercator (the projection behind Google Maps tiles). SRID 326xx is a UTM zone (meters, accurate within a narrow longitude band). Get the SRID wrong and every spatial function -- distance, area, intersection, containment -- returns a number that looks plausible but is incorrect.

The insidious part is that PostGIS never raises an error. It computes whatever you ask, in whatever units the coordinate system implies, and returns a result. Your application uses that result. Nobody discovers the problem until someone cross-checks against the real world.

The Three SRID Problems

SRID 0: The Coordinate System Nobody Chose

When geometry is inserted without specifying an SRID, PostGIS stores it as SRID 0 -- meaning "unknown." This is more common than you would expect. Data loaded via bulk COPY from CSV files, imported from shapefiles without .prj files, or constructed with ST_MakePoint(lon, lat) instead of ST_SetSRID(ST_MakePoint(lon, lat), 4326) all end up with SRID 0.

The consequences: ST_Transform refuses to operate on SRID 0 data (it cannot transform from an unknown system). ST_Distance and ST_Area still execute, but the result is in raw coordinate units -- if the coordinates are degrees, the "distance" is in degrees.

-- Find all geometry columns with SRID 0
SELECT f_table_name, f_geometry_column
FROM geometry_columns
WHERE srid = 0;
Enter fullscreen mode Exit fullscreen mode

Mixed SRIDs: Two Coordinate Systems, One Column

This happens when data from multiple sources is loaded without transforming to a common SRID. Half the rows in a locations table might be GPS data (SRID 4326, coordinates in degrees), while the other half comes from a European surveyor (SRID 32632, coordinates in meters). PostGIS stores both happily. Spatial operations between them compute the relationship between a point at (51.5, -0.1) and a point at (500000, 5700000) as if those numbers are in the same unit.

-- Detect mixed SRIDs in a geometry column
SELECT ST_SRID(geom) AS srid, count(*) AS row_count
FROM locations
GROUP BY ST_SRID(geom);
Enter fullscreen mode Exit fullscreen mode

If this returns more than one row, the column has mixed SRIDs and every spatial operation across those rows is unreliable.

Web Mercator Distances: The 60% Error

SRID 3857 (Web Mercator) is a conformal cylindrical projection optimised for rendering map tiles at any zoom level. It is not designed for measurement. The projection distorts distances and areas as you move away from the equator. At the equator, it is reasonably accurate. At 45 degrees latitude, distances are stretched by about 41%. At 60 degrees (Scandinavia, Alaska), they are doubled.

Running ST_Distance on two SRID 3857 geometries gives you projected meters -- not real-world meters. A query that says "find all restaurants within 1000 meters" actually finds restaurants within 1000 projected meters, which at London's latitude corresponds to roughly 640 real meters. Users see fewer results than they should, and nobody understands why.

The Fixes

For SRID 0 data, determine the actual coordinate system (usually WGS 84 for GPS data) and assign it:

-- If coordinates are already WGS 84, just tag them
UPDATE locations SET geom = ST_SetSRID(geom, 4326) WHERE ST_SRID(geom) = 0;

-- If coordinates are in a different system, transform
UPDATE locations SET geom = ST_Transform(ST_SetSRID(geom, 32632), 4326)
WHERE ST_SRID(geom) = 0;
Enter fullscreen mode Exit fullscreen mode

ST_SetSRID relabels without changing coordinates. ST_Transform reprojects the coordinates from one system to another.

For mixed SRIDs, transform everything to one standard:

UPDATE locations SET geom = ST_Transform(geom, 4326) WHERE ST_SRID(geom) != 4326;
SELECT UpdateGeometrySRID('public', 'locations', 'geom', 4326);
Enter fullscreen mode Exit fullscreen mode

For accurate distance calculations, cast to geography:

-- Geodesic distance, accurate anywhere on Earth
SELECT ST_Distance(a.geom::geography, b.geom::geography) AS distance_meters
FROM locations a, locations b
WHERE a.id = 1 AND b.id = 2;
Enter fullscreen mode Exit fullscreen mode

Prevention

The most effective prevention is a CHECK constraint on every geometry column:

ALTER TABLE locations ADD CONSTRAINT enforce_srid CHECK (ST_SRID(geom) = 4326);
Enter fullscreen mode Exit fullscreen mode

This rejects any INSERT or UPDATE with the wrong SRID. It is cheap -- ST_SRID reads the geometry header without parsing coordinates.

Beyond constraints:

  • Standardise on SRID 4326 for storage. Transform data at the point of entry, not at query time.
  • Never use SRID 3857 for distance or area calculations. It is for rendering tiles. Cast to geography or transform to a local UTM zone for measurement.
  • Audit geometry columns periodically. New data sources integrated six months from now will have their own SRID assumptions. A scheduled check against geometry_columns WHERE srid = 0 catches these before they corrupt results.

The SRID is the most basic piece of metadata on a geometry column, and getting it wrong invalidates everything downstream. The fix is straightforward -- the challenge is catching the problem before wrong results have been served to users for months.

Originally published at mydba.dev/blog/postgis-srid-explained

Top comments (0)