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 May 8 '14 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 May 9 '14 at 12:17

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 May 9 '14 at 16:59

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 May 9 '14 at 16:06

Step 1

Set SRID to your table:

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

Step 2

SELECT *,
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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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