How convert wgs 84 to lat/long in SQL Server

Question

I having a little trouble figuring out how to convert between types of coordinates. I have a list of coordinate sets with the following description. I have been searching around and did find some code to do this, but it does not SQL Query.

"Coordinates are always in the WGS84 system. All coordinates a represented as Decimal values x and y.

An example:

Ellipsoid:
    wgs84 (world geodetic system 1984)

UTM Zone:
    39 - 48E to 54E

UTM Projection:
    X, m: 702964.0000     ------->   latitude : 27.7818550282488
    Y, m: 3074740.0000    ------->   longitude : 53.0598812425032

The query should be SQL Server Query.

Well, I need to convert these to long/lat
Anyone who can provide some code for doing this?

Here is a db<>fiddle.

You need to do a bit of math https://en.wikipedia.org/wiki/Universal_Transverse_Mercator_coordinate_system — Serg, Dec 16 '19 at 14:50
It tends to be more than a bit and is tricky to get right. Any chance you can defer on performing the conversion until you get the values into HLL? Then you can use some sort of existing software library function to do it so you don't have to write all the tricky code in MSSQL. — robbpriestley, Dec 16 '19 at 16:38

score 1 · Answer 1 · edited Apr 15 '20 at 07:10

1

You can probably use the UF_utm_to_lat() function from this previous answer.

Easting northing to latitude longitude

edited Apr 15 '20 at 07:10

Dale K

25,246
15
42
71

answered Feb 26 '20 at 06:59

e-Fungus

321
3
17

I think your answer is not working see this: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d65cb638efcf4cb68ed178e78c116373 – Javad Abedi Mar 04 '20 at 09:52
Yup. Your xy is projected already. I have to lookup what they match up to. – e-Fungus Mar 04 '20 at 12:15
see this example: http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx i loaded it in fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6b2340a2f1f36c83a2f1441bbf06cb44 just missing a conversion from your projection to a earth point. reading up on it. – e-Fungus Mar 04 '20 at 13:14

score 1 · Answer 2 · answered Apr 28 '20 at 21:22

I haven't found suitable t-sql code for this, so I have finally 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>

score 1 · Answer 3 · edited Sep 23 '21 at 16:38

This T-SQL was adapted from some JavaScript code supplied by Xander Bakker at ESRI (replace [column_name] with your own geography shape field).

180.0 / PI() * (2.0 * ATAN(EXP(([column_name].Shape.STY / (2.0 * PI() * 6378137.0 / 2.0) * 180.0) * PI() / 180.0)) - PI() / 2.0) as Latitude, -- Convert Y to Latitude

([column_name].Shape.STX / (2.0 * PI() * 6378137.0 / 2.0) * 180.0)  as Longitude, -- Convert X to Longitude

I spot checked numerous locations and they were all spot on.

How convert wgs 84 to lat/long in SQL Server

3 Answers3