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

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