CODEWITHSUNDEEP
sql-server
Bill Gates
Bill Gates

Reputation: 857

Convert geometry to lat and long

I found in my database that shops location represented as geometry datatype.

Is it possible to convert to lat and long coordinates and use it as input for bing maps?

enter image description here

Upvotes: 11

Views: 30169

Answers (5)

Jose V. Garcia
Jose V. Garcia

Reputation: 119

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>

Upvotes: 0

girlthatCANeven
girlthatCANeven

Reputation: 99

Jan 2020: The previous formats shown threw the following error:

OperationalError: (1054, "Unknown column 'geometry_col.Long' in 'field list'")

However, this format worked:

SELECT ST_X(geometry_col) AS longitude,
       ST_Y(geometry_col) AS latitude
FROM yourTable;

Upvotes: 1

mp7
mp7

Reputation: 151

This works for geometry types in sql server

SELECT  [Geometry].STX AS [Longitude],
    [Geometry].STY AS [Latitude]
FROM YourTable

Upvotes: 13

user3833
user3833

Reputation: 21

The post is a bit old, but...

You need to use some resource that can do projections. Many shops have access to ESRI REST Geometry services and a common exposed service is Project.

Here are the manual steps, you can repackage them into a script/procedure using your normal DB tools:

--QA data from a known point with geom named ShapeGeom

select top 1 ShapeGeom.ToString(),Lat,Long from My_ADDRESS_POINTS

returns: POINT (5786835.7214864492 2235317.366254434) 35.10721420 -120.59089280

--extract/reformat x,y from POINT: 5786835.7214864492,2235317.366254434

--REST call - your path will be different

--Use inSR with the SRID of your Geometry data
select top 1  ShapeGeom.STSrid from  My_ADDRESS_POINTS

--http????/rest/services/Utilities/Geometry/GeometryServer/project?inSR=2229&outSR=4251&geometries=5786835.7214864492%2C+2235317.366254434&f=json

--result:

{
 "geometries": [
  {
   "x": -120.59089279999577,
   "y": 35.107214200388704
  }
 ]
}

Upvotes: 2

Ben Thul
Ben Thul

Reputation: 32687

The magic 8-ball says "all signs point to 'yes'". The documentation shows Lat and Long methods. So, you'd do:

select 
   Geometry.Lat as [Latitude],
   Geometry.Long as [Longitude]
from dbo.your_table

By the way, if it's in your power to change the name of that column, do so; you wouldn't call a column with data type integer "int".

Upvotes: 3

Related Questions