My table has a geometric field, in EPSG:3857, of type point. I need the latitude and longitude, in EPSG: 4326, of each point. Could someone give me a valid example of how to use ST_Transform().

  • 1
    Please state the SRS or CRS of the geometry -- it matters.
    – Martin F
    Commented May 8, 2014 at 21:41
  • My geometry is in EPSG:3857. I did not think it mattered ... if I want to get the coordinates of my geometry in the EPSG: 4326 as I do?
    – csf
    Commented May 9, 2014 at 12:17

4 Answers 4


From the PostGIS docs,

ST_Transform — Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.

geometry ST_Transform (geometry g, integer srid);

So, to read transformed points

SELECT ST_AsText (ST_Transform (geom, 4326)) FROM table ...

If you want long and lat as separate fields

SELECT ST_X (ST_Transform (geom, 4326)) AS long,
       ST_Y (ST_Transform (geom, 4326)) AS lat
FROM table ...
  • That is also based on an assumption: that PostGIS knows what the existing SRID is. See ST_SRID(), Find_SRID(), UpdateGeometrySRID(), etc, if unsure. Or ask for clarification here, and i'll expand my answer for you.
    – Martin F
    Commented May 9, 2014 at 16:59
  • The link above should be this one postgis.net/docs/ST_Transform.html (domain is postgis.net)
    – Miro J.
    Commented Sep 1, 2022 at 10:47
  • @MiroJ. .. Corrected, thanks
    – Martin F
    Commented Sep 1, 2022 at 15:36

Try this below. ST_AsText returns the well-known text. ST_X and ST_Y return the actual longitude and latitude (assuming geom is in the appropriate SRS).

SELECT ST_AsText(geom), ST_X(geom), ST_Y(geom) FROM table
  • That will work if the existing geom is already in a long-lat SRS. Otherwise it will produce easting-northing coords.
    – Martin F
    Commented May 9, 2014 at 16:06

Step 1

Set SRID to your table:

SELECT UpdateGeometrySRID('Table Name' , 'the_geom' ,32645)

Step 2

ST_X(ST_Transform (the_geom, 4326)) AS "Longitude",
ST_Y(ST_Transform (the_geom, 4326)) AS "Latitude"
FROM "Table Name"

These st_x/st_y functions are confusing. I am working with PostGresQL/PostGIS 2.4 as we speak. This is the bounding rect roughly enclosing the country of Kenya:

    bounds.northeast = new LatLng(4.959918, 41.012352);
    bounds.southwest = new LatLng(-1.603490, 33.849266);

Converting one of the points into a geometry object, then use st_x() = 4.95... st_y()=41.012... If you use google maps, x is latitude and y is longitude !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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