Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Convert GPS Decimal to Degree/Minute/Second (DMS) String

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        21 Dec 2010

Description

Converts a GPS decimal format lat/long to a degree, minute, second string repesentation

Code

--SELECT dbo.gpsConvertDecimalToDMS(50.4942,0), dbo.gpsConvertDecimalToDMS(14.4481,1)

/*
50°29'39.12''  14°26'53.16''
*/

IF OBJECT_ID('gpsConvertDecimalToDMS') > 0
    
DROP FUNCTION gpsConvertDecimalToDMS
GO    

CREATE FUNCTION gpsConvertDecimalToDMS (@decimalGPS DECIMAL(20,8), @isLongitude bit)
RETURNS NVARCHAR(255)
BEGIN
    
    IF
@isLongitude = 1 AND @decimalGPS NOT BETWEEN -180 AND 180
        
RETURN('Invalid coordinate. Longitude has to be in interval <-180;180>')
    
IF @isLongitude = 0 AND @decimalGPS NOT BETWEEN -90 AND 90
        
RETURN('Invalid coordinate. Latitude has to be in interval <-90;90>')    

    
DECLARE
      
@Output VARCHAR(MAX),
      
@Degree INT,
      
@Minute INT,
      
@Second DECIMAL(12,10)
    
    
-- degrees is just the integer portion of the number.
    
SELECT @Degree = CAST(@decimalGPS AS INT)
    
    
    
-- minutes is the integer portion of the decimal portion of the number multiplied by 60.
    -- include a CAST even though implicit casting would be carried out (just in case this changes in future)
    
SELECT @Minute = CAST((@decimalGPS - @Degree) * 60 AS INT)
    

    
-- minutes is the integer portion of the decimal portion of the number multiplied by 3600 less the minute portion * 60.
    
SET @Second = (3600 * (@decimalGPS - @Degree)) - (@Minute * 60)

    
SET @Output =
      
CAST(@Degree AS NVARCHAR(10))
       +
CHAR(176) /* degree sign */
      
+ CAST(@Minute AS NVARCHAR(55))
        +
CHAR(39) /* apostroph */
        
+ CAST(@Second AS NVARCHAR(55))    

    
-- remove right zeros
    
SET @Output = RTRIM(REPLACE(@Output, '0', ' '))
    
SET @Output = REPLACE(@Output, ' ', '0')
    
    
SET @Output = @Output + CHAR(39) + CHAR(39) /*two apostrophs */
  
    
RETURN @Output

END


ċ
Convert GPS Decimal to Degrees Minutes and Seconds string.sql
(2k)
Andy Hughes,
Jun 23, 2012, 1:24 AM
Comments