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

added 740 characters in body

Source Link

edited Jul 26, 2013 at 16:52

1.9k
10
11

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

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) + '"'

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

format

Source Link

edited Jul 26, 2013 at 15:28

Russell at ISC

1.9k
10
11

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(RoundFLOOR(ABS(@geom.STX, 0)) 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(RoundFLOOR(ABS(@geom.STY, 0)) 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) + '"'

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 CAST(Round(@geom.STX, 0) as varchar) + ' ' +
    CAST(CAST(ABS(@geom.STX) * 60 AS INT) % 60 as varchar) + ''' ' +
    CAST(
    (CAST(ABS(@geom.STX) * 3600 AS INT) % 60) 
         AS VARCHAR) + '"',
          CAST(Round(@geom.STY, 0) as varchar) + ' ' +
    CAST(CAST(ABS(@geom.STY) * 60 AS INT) % 60 as varchar) + ''' ' +
    CAST(
    (CAST(ABS(@geom.STY) * 3600 AS INT) % 60) 
         AS VARCHAR) + '"'

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) + '"'

Post Undeleted by Russell at ISC

occurred Jul 26, 2013 at 15:27

Post Deleted by Russell at ISC

occurred Jul 26, 2013 at 15:21

Source Link

created Jul 26, 2013 at 15:20

Russell at ISC

1.9k
10
11

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 CAST(Round(@geom.STX, 0) as varchar) + ' ' +
    CAST(CAST(ABS(@geom.STX) * 60 AS INT) % 60 as varchar) + ''' ' +
    CAST(
    (CAST(ABS(@geom.STX) * 3600 AS INT) % 60) 
         AS VARCHAR) + '"',
          CAST(Round(@geom.STY, 0) as varchar) + ' ' +
    CAST(CAST(ABS(@geom.STY) * 60 AS INT) % 60 as varchar) + ''' ' +
    CAST(
    (CAST(ABS(@geom.STY) * 3600 AS INT) % 60) 
         AS VARCHAR) + '"'

Stack Exchange Network

Return to Answer