Converting geometry(spatial) to longitude and latitude

Question

I've uploaded .SHP files to my table in sql and I got geometry column with data. I can see points on "Spatial results" tab in sql server and that's all fine. When I run this line in sql server

SELECT [geom].STAsText() FROM myTable

I get these kind of results

POINT (444386.4927124856 5073381.9183241855)

So is there any way to convert this to regular latitude and longitude.

What is the source of the data? For example, are they OSGB grid references? — Jon Bellamy
I don't know, all I know is that I have this .shp file and I extract data with options "Planar geometry, SRID =4326", what is the actual data from which .shp file was created, I don't know... — Namazani
Your first job will be to try and find out what datum the coordinates are in then, otherwise converting them to WGS84 Latitude Longitudes could go horribly wrong. Conversion isn't easy (although there are tools to simplify it) but most require an input datum and output datum. — Jon Bellamy
I agree the numbers shown in your example indicate that your shapefile is not projected in lat/lon. Here is a discussion that might help you finding the right SRID stackoverflow.com/questions/1541202 . If you find the right projection, then you can use ST_Transform to project to lat lon, and ST_Y resp ST_X to get the coordinates, see gis.stackexchange.com/a/42971/4287 — yellowcap

Jose V. Garcia Jose V. Garcia · Accepted Answer · 2020-04-28T21:50:25

I have developed a library in .NET to be called from transact sql Converts WGS84/UTM coordinates to Latitude and Longitude

You can download it from github:

https://github.com/j-v-garcia/UTM2LATITUDE

usage:

SELECT dbo.UTM2LATITUDE(723399.51,4373328.5,'S',30) AS Latitude, dbo.UTM2LONGITUDE(723399.51,4373328.5,'S',30) AS Longitude

result:

39,4805657453054    -0,402592727245112

<param name="XUTM">pos UTM X</param>
<param name="YUTM">pos UTM Y</param>
<param name="LatBand">Latitude band grid zone designation letter (see http://www.dmap.co.uk/utmworld.htm) </param>
<param name="LongBand">Longitude band grid zone designation number (see http://www.dmap.co.uk/utmworld.htm) </param>

Converting geometry(spatial) to longitude and latitude

1 Answers