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

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
``````
• Do you mean you want to store the actual geometry as DMS, or you want to convert the native geometry type to DMS for querying? – DPSSpatial Jul 26 '13 at 15:04
• Does the solution can be also used with Geography ? – Royi Namir Feb 4 '15 at 9:14

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
``````
• thanks man but what about N S E W value...??? – Gunner Jul 26 '13 at 16:42
• Remove the "CASE WHEN geom.STX < 0 then '-' ELSE '' END +" at the start and add "+ CASE WHEN geom.STX < 0 then ' W' ELSE ' E' END" at the end. Similarly for STY < 0 is S otherwise N. Note ampersand omitted because the comment editor yacked on it. – Russell at ISC Jul 26 '13 at 16:49
• Shouldn't this be used with geography rather than geometry ? but geography doesn't have STX.... – Royi Namir Feb 4 '15 at 8:56
• @Royi, can use it with either, see Baodad below for using geography. – Russell at ISC Feb 4 '15 at 14:31

(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.