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

Question

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

Answer 1

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 

Answer 2

(Credit to @Russell at ISC for the use of the modulo operator.)
We have a table in SQL Server with a column of geography data type, called [GeoCoor].

This is how we convert to degrees, minutes, and seconds:

SELECT
[GeoCoor].[Lat] as [DecimalLatitude],
floor(ABS([GeoCoor].[Lat]))*(CASE WHEN [GeoCoor].[Lat] < 0 then -1 ELSE 1 END) as [LatDegrees],
convert(int,ABS([GeoCoor].[Lat])*60) % 60 as [LatMinutes],
convert(decimal(4,2),convert(decimal(17,10),ABS([GeoCoor].[Lat])*3600) % 60) as [LatSeconds],
[GeoCoor].[Long] as [DecimalLongitude],
floor(ABS([GeoCoor].[Long]))*(CASE WHEN [GeoCoor].[Long] < 0 then -1 ELSE 1 END) as [LongDegrees],
convert(int,ABS([GeoCoor].[Long])*60) % 60 as [LongMinutes],
convert(decimal(4,2),convert(decimal(17,10),ABS([GeoCoor].[Long])*3600) % 60) as [LongSeconds]
FROM SQLTableWithGeographyData

This creates separate columns for degrees, minutes, and seconds, which we put together later at the application or report layer.