Get latitude/longitude from PostGIS point

Question

I have a PostGIS table with a geometry field of SRID 3857 data. I want to get the centroid of the geometry as lat/lon coordinates, but can't seem to convert the values correctly:

SELECT 
ST_X(ST_CENTROID(geometry)),
ST_Y(ST_CENTROID(geometry)),
ST_X(ST_TRANSFORM(ST_CENTROID(geometry),3857)) AS LONG, 
ST_Y(ST_CENTROID(ST_TRANSFORM(geometry,3857))) AS LAT
FROM my_table

=> -8220067.19453888107, 4997528.08142071683, -8220067.19453888107, 4997528.08142071683

Answer 1

You are using the 3857 projection (Pseudo-Mercator) which isn't in lon/lat degrees, it's in meters.

To get lon/lat values you need to use the 4326 projection:

db=# SELECT
ST_X(ST_Centroid(geometry)),
ST_Y(ST_Centroid(geometry)),
ST_X(ST_Centroid(ST_Transform(geometry, 4326))) AS long,
ST_Y(ST_Centroid(ST_Transform(geometry, 4326))) AS lat
FROM my_table;
       st_x        |       st_y       |       long        |       lat
-------------------+------------------+-------------------+------------------
 -8220067.19453888 | 4997528.08142072 | -73.8421199734375 | 40.8994922746749
(1 row)