We’ve already mentioned the idea of projections when describing measurement functions. While PostGIS has methods to handle simple distances in each of projected and unprojected (lat/long) coordinates, any more complicated requests, such as area or distances between non-points will be performed without consideration of the projection. Even the unprojected length functions require knowledge of the spheroid on which the data has been measured. Getting it wrong produces results that are misleading or unclear.
How do we interpret the result of the following query?
SELECT SUM(ST_Length(the_geom))
FROM jacksonco_streets
WHERE namelow = 'E Main St';
In this case, the projection (2270) is in feet, and as such the results of the ST_Length function are as well. PostGIS provides a simple function to perform all your projection needs, once you decide what those needs are. For example, the distance in meters can be determined by transforming (ST_Transform) the geometries into a metric projection (2839).
SELECT SUM(ST_Length(ST_Transform(the_geom, 2839)))
FROM jacksonco_streets
WHERE namelow = 'E Main St';
But how do you know the projection in the first place? A simple approach would be to look in the geometry_columns table as described earlier.
SELECT f_table_name, f_geometry_column, srid
FROM geometry_columns;
This approach is usually appropriate, but the table record itself doesn’t enforce the listed projection (also known as “srid”, or “spatial reference ID”) on the geometries. In order to ensure that the listed srid is correct, the AddGeometryColumns function described earlier will create a check constraint against the geometry column. It also creates check constraints to ensure the geometry type and coordinate dimension are as listed.
If these constraints are not present, there is one final way to check the srid of a geometry; query it directly with the ST_SRID function.
SELECT ST_SRID(the_geom)
FROM jacksonco_streets
LIMIT 1;
It is important to ensure your projections are consistent for reasons beyond the measurement functions. PostGIS will simply refuse to perform many functions on geometries with different srids, as the results will generally be meaningless.
SELECT ST_Distance(ST_Point(-122.857, 42.331), the_geom)
FROM jacksonco_streets
WHERE namelow = 'E Main St';
What do you do when your srid is not set when the data is loaded, or worse, you discover it is set incorrectly? For these cases, PostGIS offers the ST_SetSRID function. This allows you to declare the srid on a geometry without performing any projection.
SELECT ST_Distance(ST_SetSRID(ST_Point(-118.143, 33.812), 2270), the_geom)
FROM jacksonco_streets
WHERE namelow = 'E Main St';
While this is useful for small tasks, it is much preferred to update the database so it will always know the appropriate srid. This is done using the UpdateGeometrySRID function. This function takes the table name, geometry column name, the srid value, and optionally the schema name and updates every record in the table with the new srid, creates the check constraint on the table and updates the entry in the geometry_columns table.
SELECT UpdateGeometrySRID('lines', 'line', 4326);
Note
The UpdateGeometrySRID function is intended to set the correct srid on the table only. It will not reproject the data.