2

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
2

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
  • 1
    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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.