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
|
 Updating...
Andy Hughes, Jun 23, 2012, 1:24 AM
|