topic Re: Return Lat Long from SQL Spatial Geometry Type Query in Data Management Questions

Return Lat Long from SQL Spatial Geometry Type Query

AaronGreiner1 — Thu, 26 Feb 2015 17:00:32 GMT

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!

Re: Return Lat Long from SQL Spatial Geometry Type Query

ChristianWells — Thu, 26 Feb 2015 18:35:30 GMT

Hi Aaron,

Can you try this in SQL Server:

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

Re: Return Lat Long from SQL Spatial Geometry Type Query

AaronGreiner1 — Thu, 26 Feb 2015 18:42:08 GMT

Hi Christian,

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

Aaron

Re: Return Lat Long from SQL Spatial Geometry Type Query

ChristianWells — Thu, 26 Feb 2015 18:44:52 GMT

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

Re: Return Lat Long from SQL Spatial Geometry Type Query

AaronGreiner1 — Thu, 26 Feb 2015 18:49:34 GMT

Chris,

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

Thanks.

Aaron

Re: Return Lat Long from SQL Spatial Geometry Type Query

ChristianWells — Thu, 26 Feb 2015 19:01:30 GMT

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?

Re: Return Lat Long from SQL Spatial Geometry Type Query

AaronGreiner1 — Thu, 26 Feb 2015 20:47:24 GMT

Chris,

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

Thanks

Re: Return Lat Long from SQL Spatial Geometry Type Query

BryanMc — Fri, 27 Feb 2015 13:17:13 GMT

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.

Re: Return Lat Long from SQL Spatial Geometry Type Query

AaronGreiner1 — Fri, 27 Feb 2015 13:30:12 GMT

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.