1

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.

Namazani
  • 53
  • 1
  • 9
  • What is the source of the data? For example, are they OSGB grid references? – Jon Bellamy Mar 24 '14 at 14:07
  • 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 Mar 24 '14 at 14:33
  • 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 Mar 24 '14 at 17:55
  • 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 http://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 http://gis.stackexchange.com/a/42971/4287 – yellowcap Mar 25 '14 at 11:27

1 Answers1

0

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>
Jose V. Garcia
  • 119
  • 7