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().
-
1Please state the SRS or CRS of the geometry -- it matters.– Martin FCommented 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?– csfCommented May 9, 2014 at 12:17
Add a comment
|
4 Answers
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 FCommented 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
-
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 FCommented May 9, 2014 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"
answered May 26, 2016 at 10:23
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 !
