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;