How do I perform a coordinate reprojection as part of a query?
To perform a reprojection, both the source and destination coordinate systems must be defined in the
spatial_ref_sys table, and the geometries being reprojected must already have an SRID set on them.
Once that is done, a reprojection is as simple as referring to the desired destination SRID. The below projects a geometry to WGS84 longitude/latitude coordinates.
SELECT ST_Transform(geom, 4326) FROM mytable;
Make sure that your geometry column has an SRID, or reprojection won’t work! You can check if the SRID is enforced at the column level:
SELECT srid FROM geometry_columns WHERE f_table_name = 'mytable'
And you can check if it is set at the geometry level:
SELECT ST_SRID(geom) AS srid, Count(*) FROM mytable GROUP BY srid;