sql 2012 geometry to lat long in wgs84

I have a query which extracts latitude and longitude from a geometry field in a table.

SELECT TOP 10 *
, Geom.STX as [Latitude]
, Geom.STY as [Longitude]

FROM LOCATIONS_ALL_ACTIVE

The property of the GEOM field is GEOM(geometry, null)

It produces this:

LOC      Latitude      Longitude
1709036      2566110.742      971741.23
1480125      2546663.063      1004989.767
1473797      2517822.745      961433.929
1473851      2499826.1      901428.05
1469381      2546028.5      954613.67
1469322      2498035.41      927803.9
1475057      2497484.54      938479.25
1473436      2505412.3      917097.54
1800985      2537353.598      939850.052
1473819      2550003.19      962727.89

I am trying to get these coordinates to work with a web service and they are telling me it needs to be in a wgs84 projection.  The above coordinates place it an ocean
PeelSeel2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
I'm not an expert on the subject, but since the question is 'neglected' I'll try to help you out. I don't think SQL Server can do the conversion for you without you writing some code to do the conversion yourself. But then we need to determine what format is used for your coordinates. I think latitude and longitude are about degrees (-90 to 90 and -180 to +180), and your numbers look like a distance to me. So maybe they are easting and northing coordinates. My suggestion:
1. Determine the current coordinate system (if you don't know already).
2. Find a way to do the conversion, and test it manually to see if you get to the right location.
3. Find a way to do the conversion automatically (preferably within SQL Server).

If you don't know the answer to 1, you could ask in the 'GIS & GPS Programming' if anyone can help you out. Just mention a few of the coordinates and to what location they refer and someone might know the answer.
0
ste5anSenior DeveloperCommented:
GEOMETRY is a coordinate in a plane. WGS84 is a coordinate in a sphere. WGS84 coordinates are also a latitude/longitude pair.

The problem is: What is the origin of your GEOMETRY values? When you don't know this, then it's a really hard task to get the projection right. Cause there are different transformations possible, which have a different precision.

Also, what does "The above coordinates place it an ocean." mean? Sounds like you need to provide an altitude in that web service.
0
PeelSeel2Author Commented:
Our coordinates are in northing/easting NAD83.  I can use OGR2OGR for the conversion.  I just thought maybe I was missing an easy way to do it inside of an SQL query.
0
ste5anSenior DeveloperCommented:
I don't know an easy one, cause I haven't done conversion on that level.

My approach would be CLR-function doing this, but this requires some C# skills.
0
Nico BontenbalCommented:
I'm afraid there is no easy way to this in a query. But if you can find the algorithm for the conversion you can try to implement it in a function. For an example (for a different conversion) you can google on:
"SQL function to convert UK OS coordinates from easting/northing to longitude and latitude"
(including the quotes). This will take you to a post on a competitor of EE (that's why I'm not allowed to post the link).

I found this site also: http://tagis.dep.wv.gov/convert/. Maybe you can use it to check if you got the right conversion. And if you found it you can try and find the algorithm.

When I use WV State Plane North/South NAD83 as the source at least the coordinates are not in an ocean.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.