0

I have a list of data with geometry column in the format of 0x6A7F0000010D340AF.

I wand to retrieve the latitude and longitude in the range of (-90,90) and (-180,180). I have tried the following code:

select geom.STY as lat, geom.STX as lon 
from Table;

However, it returns values in the range of 5058449.313 for latitude and longitude.

Any idea what I should do? Can I also use Python to do the conversion? How can I get the CRS from the geom?

Update-Solution

Assuming that I know the CRS, I can use this code to get the latitude and longitude from the (Y,X) retrieved from

select geom.STY as lat, geom.STX as lon 
from Table;
NaHa
  • 1
  • 4

2 Answers2

1

STX and STY return X-coordinate and Y-coordinate property of a Point instance. here is what you need:

SELECT  
    geom.Long AS [Longitude]
    ,geom.Lat AS [Latitude]
FROM [Table]
eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • I have tried that and did receive the following error message: Could not find property or field 'Long' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'. – NaHa Nov 19 '20 at 16:09
  • oh so you are using wrong data type , you need to save data in `SqlGeography` datatype – eshirvana Nov 19 '20 at 16:13
  • I have been given the data. How can I convert it to `SqlGeography`? – NaHa Nov 19 '20 at 16:16
  • I think by this function [STGeomFromText](https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stgeomfromtext-geography-data-type?view=sql-server-ver15) – eshirvana Nov 19 '20 at 16:23
  • Thank you for your response. That code gets `latitude` and `longitude` and returns a `SqlGeography` type. Not what I need. – NaHa Nov 19 '20 at 16:27
  • you can use something like this `SELECT geography::STGeomFromText([geom].STAsText(),4326)` – eshirvana Nov 19 '20 at 16:34
  • and it document it says it gets `geography_tagged_text` as input , why you says it gets long/lat , that is wrong interpretation . – eshirvana Nov 19 '20 at 16:35
  • Because of the example: ```DECLARE @g geography; SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326); SELECT @g.ToString();``` – NaHa Nov 19 '20 at 17:12
  • that's just one example , did you try the one above? did it work? – eshirvana Nov 19 '20 at 17:26
  • Yes: `The label 0x6A7F0000***** in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).` – NaHa Nov 19 '20 at 17:32
0

Someone else has posted a proposed answer and the ensuing conversation has boiled down to "use geography instead of geometry". I agree!

But you also rightfully observe that you have what you have. And so my suggestion would be to get with whomever was responsible for writing the data and figure out how they stored latitude and longitude. That will get you a long way towards figuring out how to extract it from the extant data. Without knowing that, all you (and we) can do is speculate/guess.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68