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
2
  • 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? Jul 26, 2013 at 15:04
  • Does the solution can be also used with Geography ?
    – Royi Namir
    Feb 4, 2015 at 9:14

2 Answers 2

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 
4
  • thanks man but what about N S E W value...???
    – Gunner
    Jul 26, 2013 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. Jul 26, 2013 at 16:49
  • Shouldn't this be used with geography rather than geometry ? but geography doesn't have STX....
    – Royi Namir
    Feb 4, 2015 at 8:56
  • @Royi, can use it with either, see Baodad below for using geography. Feb 4, 2015 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.