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.

  • What did you try to achieve this? – Mazu_R Jul 20 '18 at 14:01

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)
    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;
  • I understood, why use reference 4326 and not another? Is this for any input reference? – Adriel Gomes Costa Jul 20 '18 at 18:41
  • Without changing the database, can you retrieve this information (lat lat in decimal degrees) in java? I'm trying but I can not. I am using the JTS from vividsolutions. – Adriel Gomes Costa Jul 20 '18 at 21:37

Rather than a conversion of values in your queries, I believe the best solution to be to reproject your data, then to save those geom values as attributes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.