[GIS] Converting coordinates into decimal using SQL Server Spatial

convertcoordinate systemsql-server-spatial

I am looking to convert a pair of coordinates to decimal, and am unsure how to go about doing it.

The following script

select Centroid.STY as Longitude,
Centroid.STX as Latidude
from
(    select Shape.STCentroid() as Centroid
     from gis.USA_10_CENSUS_TRACT
)U

generates these results:

-1125114.67826638   -2502243.88901991
-1054751.90111223   -2514282.59232887
-1048731.88354633   -2500496.88304733
-1046266.95217362   -2499166.70651986

When I am in fact looking for results in the following format:

34.54270600 -112.46910900
33.37959400 -111.76312100
31.64200000 -110.33700000
33.75689700 -111.99271000

(note that the above and bottom examples are not intended to match)

Best Answer

Your data is in a projected coordinate system, and the units you're getting from STY and STX are returning values in the units of that coordinate system (probably feet).

Unfortunately MSSQL Server doesn't support projecting your data on the fly like PostGIS does.

So what you'll have to do - if possible - is to load a copy of your data projected to WGS84 (SRID:4326) and use the same SQL statement. That way you'll get decimal degrees returned from the query, which are the units of WGS84 / 4326.

Related Question