PostGIS – How to Convert UTM to Lat Long in Decimal Degrees using PostGIS

Question

I want the Postgres / PostGIS queries to convert the UTM coordinates of the SIRGAS 2000 / UTM zone 20N (EPSG: 31974) projection to the Decimal Degrees projection. Separating longitude and latitude into distinct columns.

Answer 1

You can use a combination of ST_Transform to reproject the data, then ST_X and ST_Y to extract the latitude and longitude.

WITH src AS (select st_geomfromtext('POINT(0 5000000)',31974) geom31974)
SELECT 
    ST_transform(geom31974,4326) geom4326,
    ST_asText(ST_transform(geom31974,4326)) txtgeom4326,
    ST_X(ST_transform(geom31974,4326)) longitude,
    ST_Y(ST_transform(geom31974,4326)) latitude
FROM src;