Convert Latitude and Longitude into Y, X coordinate in T-SQL(MS SQL Server) and Y, X coordinate into Latitude and Longitude

2012-08-25T07:04:00.001-07:00

T-SQL (MS SQL SERVER) functions to convert latitude and longitude into Y and X coordinate at specific zoom level (Latitude represents Y axis and longitude X axis) and vice-versa. Latitude range is from +90 degree to -90 degree and longitude range is from +180 degree to -180 degree.

Convert Latitude and Longitude into Y and X coordinate in T-SQL

Convert Latitude into Y Coordinate:
CREATE FUNCTION [dbo].[fnGetLatToY]
(
@Latitude decimal(18,14),
@ZoomLevel tinyint
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Power DECIMAL(30,14)
DECLARE @SinLatitude DECIMAL(30,14)
DECLARE @YPixel BIGINT
SELECT @Power = CAST(POWER(2,@ZoomLevel) AS DECIMAL(30,14))*256
SELECT @SinLatitude = SIN(@Latitude * PI()/180)
SELECT @YPixel = CAST(((.5 - LOG((1+@SinLatitude)/(1-@SinLatitude)) / (4*PI())) *@Power) AS BIGINT)

RETURN @YPixel
END

Convert Longitude into X Coordinate:
CREATE FUNCTION [dbo].[fnGetLonToX]
(
@Longitude decimal(18,14),
@ZoomLevel tinyint
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Power DECIMAL(30,14)
DECLARE @XPixel BIGINT
SELECT @Power = CAST(POWER(2,@ZoomLevel) AS DECIMAL(30,14))*256
SELECT @XPixel = CAST((((@Longitude + 180)/360)*@Power) AS BIGINT)

RETURN @XPixel
END

Convert Y Coordinate into Latitude:
CREATE FUNCTION [dbo].[fnGetYToLat]
(
@YPixel BIGINT,
@ZoomLevel tinyint
)
RETURNS DECIMAL(18,14)
AS
BEGIN
DECLARE @Latitude DECIMAL(18,14)
DECLARE @Power DECIMAL(30,14)
DECLARE @SinLatitude DECIMAL(30,14)
DECLARE @MapSize DECIMAL(30,14)
DECLARE @TempMaxY DECIMAL(30,14)
DECLARE @MaximumMaxY DECIMAL(30,14)
SELECT @Power = CAST(POWER(2,@ZoomLevel) AS DECIMAL(30,14))*256
SELECT @SinLatitude = SIN(@Latitude * PI()/180)

-- calculate Lat from Y
SELECT @MapSize = 256 * CAST(POWER(2, @ZoomLevel) AS BIGINT)
SELECT @MaximumMaxY = CASE WHEN @YPixel >= 0 THEN @YPixel ELSE 0 END
SELECT @TempMaxY = 0.5 - ((CASE WHEN @MaximumMaxY <= @MapSize - 1 THEN @MaximumMaxY ELSE @MapSize - 1 END) / @MapSize)
SELECT @Latitude = 90 - 360 * Atan(Exp(-@TempMaxY * 2 * PI())) / PI();

RETURN @Latitude
END

Convert X Coordinate into Longitude:
CREATE FUNCTION [dbo].[fnGetXToLon]
(
@XPixel BIGINT,
@ZoomLevel tinyint
)
RETURNS DECIMAL(18,14)
AS
BEGIN

DECLARE @Longitude decimal(18,14)
DECLARE @Power DECIMAL(30,14)

SELECT @Power = CAST(POWER(2,@ZoomLevel) AS DECIMAL(30,14))*256
-- calculate Long from X
SELECT @Longitude = @XPixel/@Power*360-180

RETURN @Longitude
END

Shishir's Blog

Convert Latitude and Longitude into Y, X coordinate in T-SQL(MS SQL Server) and Y, X coordinate into Latitude and Longitude