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!
Hi Aaron,
Can you try this in SQL Server:
select shape, shape.STX as LONG, shape.STY as LAT from sde.fc
Hi Christian,
Thanks for the quick reply, unfortunately those only work for sqlGeography types and our data is in sqlGeometry types.
Aaron
Hi Aaron,
SQL Server does also support STX and STY for the geometry type:
In addition, are you looking to convert the Feet units of a Projected Coordinate System to Decimal Degrees?
Chris
Chris,
Thanks, I missed that on the geometry type. Yes, I am converting feet units to decimal degrees.
Thanks.
Aaron
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?
Chris,
Bummer, so i'll need to find another solution for getting that our of the data.
Thanks
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.
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.