Conversion of EPSG:3857 coordinate to UTM

Question

I'm trying to convert coordinates from Google Maps using PostGIS.

I'm using this query:

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(-29.16297 -51.1795034)', 900913), 29182));

The problem is that when I put a point on the map using the UTM coordinates, it is in a different place.

Is there a chance of one of these SRIDs be wrong?

Answer 1

Your input coordinates look as lat/long or long/lat coordinates, so you should use 4326 instead of 900913:

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(-29.16297 -51.1795034)', 4326), 29182));

Or:

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(-51.1795034 -29.16297)', 4326), 29182));

The input on that case should be POINT(long lat).

Answer 2

Use ST_MakePoint and the geography type. Google Maps exports in 4326, which is the default SRID for Geography

SELECT ST_AsText(geom)
FROM ST_Transform( ST_MakePoint(long, lat)::geography, 29182 )
  AS t(geom);