[GIS] How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

convertdegrees minutes secondsgeometrysqlsql server

I have shape column of type geometry and I want to convert X and Y coordinates into Degree Minutes Second format using SQL query and i am using sql server 2008 R2.

Currently I got Lat and long using this query 

Select W.SHAPE.STY as latitude, W.SHAPE.STX as longitude from tbdata

Best Answer

Here's an implementation of the formula from Wikipedia, except that this will truncate fractional seconds because SQL only allows the modulo operator with ints.

DECLARE @geom geometry;

SET @geom = geometry::STGeomFromText('POINT(-83.255 32.567477)', 4326);


SELECT CASE WHEN @geom.STX < 0 then '-' ELSE '' END +
    CAST(FLOOR(ABS(@geom.STX)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 3600) AS INT) % 60 AS VARCHAR) + '"',
  CASE WHEN @geom.STY < 0 then '-' ELSE '' END +
    CAST(FLOOR(ABS(@geom.STY)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 3600) AS INT) % 60 AS VARCHAR) + '"'

or for N, S, E, W rather than signs

DECLARE @geom geometry;

SET @geom = geometry::STGeomFromText('POINT(-83.255 32.567477)', 4326);


SELECT CAST(FLOOR(ABS(@geom.STX)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 3600) AS INT) % 60 AS VARCHAR) + '"' +
    CASE WHEN @geom.STX < 0 then ' W' ELSE ' E' END,
  CAST(FLOOR(ABS(@geom.STY)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 3600) AS INT) % 60 AS VARCHAR) + '"' +
    CASE WHEN @geom.STY < 0 then ' S' ELSE ' N' END
Related Question