Return Lat Long from SQL Spatial Geometry Type Query

26853
8
02-26-2015 09:00 AM
AaronGreiner1
New Contributor II

I'm trying to use a SQL query to get the Lat Long of points in a point layer in our geodatabase.  The  STAsText method will return the XY as shown below, but I'd like to get the lat long instead.  Is there a method that can do this or is there a conversion I could use in SQL to convert the XY from STAsText?

 

Here is the query to get the XY:

select SHAPE, [SHAPE].STAsText() XY

from featureclass

 

Any help would be greatly appreciated. Thanks!

0 Kudos
8 Replies
ChristianWells
Esri Regular Contributor

Hi Aaron,

Can you try this in SQL Server:

select shape, shape.STX as LONG, shape.STY as LAT from sde.fc

AaronGreiner1
New Contributor II

Hi Christian,

Thanks for the quick reply, unfortunately those only work for sqlGeography types and our data is in sqlGeometry types.

Aaron

0 Kudos
ChristianWells
Esri Regular Contributor

Hi Aaron,

SQL Server does also support STX and STY for the geometry type:

STX (geometry Data Type)

In addition, are you looking to convert the Feet units of a Projected Coordinate System to Decimal Degrees?

Chris

AaronGreiner1
New Contributor II

Chris,

Thanks, I missed that on the geometry type.  Yes, I am converting feet units to decimal degrees.

Thanks.

Aaron

0 Kudos
ChristianWells
Esri Regular Contributor

Sounds good. Just something to keep in mind with conversions here is that SQL Server doesn't have support for a Transform or Reproject function. Is it possible to reproject spatial data using SQL Server?

AaronGreiner1
New Contributor II

Chris,

Bummer, so i'll need to find another solution for getting that our of the data.

Thanks

0 Kudos
BryanMc
Occasional Contributor

Aaron Greiner‌, you could possibly wrap your existing SQL query into python with the projected data, then send that collection to ArcGIS for Server's Geometry service to project the features via the script. There is also an amazing github JavaScript library that can transform point coords from one system to another (with datum transformations). Proj4js by proj4js  . However, this means you are going way beyond a SQL query.

0 Kudos
AaronGreiner1
New Contributor II

Thanks Bryan,

My intention was to have this live in the database as part of a spatial view without any processing outside the database.  I'll likely just use python to extract the view and do the final calculations.

Thanks.

0 Kudos