Bing Maps Tile System Functions for TSQL

This blog presents functions for Bing Maps Tile System for TSQL.

This support includes converting Latitude/Longitude pairs to Bing Maps QuadKeys.

The source for the functions is over at the Mircosoft site – an article by James Schwartz.

They are donated to the public domain.

use DEVDB
go

-- *****************************************************************

Drop Function If Exists  [dbo].[STBingTileXYToQuadKey] ;
GO

Create Function [dbo].[STBingTileXYToQuadKey] (
  @p_tileX         integer, 
  @p_tileY         integer, 
  @p_levelOfDetail integer = 23
)  
returns varchar(100)
As
Begin
  Declare
    @i         integer,
	@v_digit   integer = 43, -- char(1) = '0',
	@v_mask    integer,
    @v_quadKey varchar(100);

	SET @i = ISNULL(@p_levelOfDetail,23);
	WHILE (@i > 0)
    BEGIN
      SET @v_mask = [dbo].[shiftLeft](1,(@i - 1));
      SET @v_digit = 0;
      IF ((@p_tileX & @v_mask) != 0) SET @v_digit += 1;  
      IF ((@p_tileY & @v_mask) != 0) 
      BEGIN
        SET @v_digit += 1;  
        SET @v_digit += 1;  
      END;
	  -- Append @v_digit to end of key
      SET @v_quadKey = CONCAT(@v_quadKey,CAST(@v_digit as varchar(100)));
	  SET @i -= 1;
    END;
    RETURN @v_quadKey;
END;
GO  


-- compared to Micorosft C# Class and is correct.

-- ************************************************************************

Drop Function If Exists [dbo].[STBingLatLongToQuadKey];
GO

Create Function [dbo].[STBingLatLongToQuadKey] (
  @p_latitude      float, 
  @p_longitude     float, 
  @p_levelOfDetail integer = 23
)
returns varchar(100)
AS
BEGIN
  DECLARE
    @c_MinLatitude   Float = -85.05112878,
    @c_MaxLatitude   Float = 85.05112878,
    @c_MinLongitude  Float = -180.0,
    @c_MaxLongitude  Float = 180.0,
    @v_latitude      float,
    @v_longitude     float,
	
	@v_levelOfDetail integer = ISNULL(@p_levelOfDetail,23),
    @v_x             float,
    @v_sinLatitude   float,
    @v_y             float,
	@v_mapSize       bigint, /* actual uint */
    @v_pixelX        float,
    @v_pixelY        float,
	@v_iPixelX       integer,
	@v_iPixelY       integer,
    @v_tileX         integer,
    @v_tileY         integer,
	
	@v_quadKey      varchar(100);	
  
  -- Clip latitude numbers to the specified minimum and maximum values.  
  SET @v_latitude  = case when @p_latitude > @c_MinLatitude then @p_latitude else @c_MinLatitude end;
  SET @v_latitude  = case when @v_latitude < @c_MaxLatitude then @v_latitude else @c_MaxLatitude end;
  
  -- Clip longitude numbers to the specified minimum and maximum values.  
  SET @v_longitude  = case when @p_longitude > @c_MinLongitude then @p_longitude else @c_MinLongitude end;
  SET @v_longitude  = case when @v_longitude < @c_MaxLongitude then @v_longitude else @c_MaxLongitude end;

  SET @v_x           = (@v_longitude + 180.0) / 360.0;   
  SET @v_sinLatitude = Sin(@v_latitude * PI() / 180.0);  
  SET @v_y           = 0.5 - LOG((1.0 + @v_sinLatitude) / (1.0 - @v_sinLatitude)) / (4.0 * PI());  
  
  SET @v_mapSize = [dbo].[shiftLeft](256,@v_levelOfDetail); 

  -- Clip pixelX
  -- (int) Clip(x * mapSize + 0.5, 0, mapSize - 1);  
  -- 
  SET @v_pixelX  = (@v_x * @v_mapSize + 0.5);
  SET @v_pixelX  = case when @v_pixelX > 0.0              then @v_pixelX else 0.0              end;
  SET @v_iPixelX = case when @v_pixelX < (@v_mapSize - 1) then @v_pixelX else (@v_mapSize - 1) end;  

  -- Clip pixelY
  -- 
  SET @v_pixelY  = (@v_y * @v_mapSize + 0.5);
  SET @v_pixelY  = case when @v_pixelY > 0.0              then @v_pixelY else 0.0              end;
  SET @v_ipixelY = case when @v_pixelY < (@v_mapSize - 1) then @v_pixelY else (@v_mapSize - 1) end;  

  -- Now convert PixelX/Y to TileXY
  SET @v_tileX = @v_iPixelX / 256;  
  SET @v_tileY = @v_iPixelY / 256;  

  -- Now convert TileXY to QuadKey
  SET @v_quadKey = [dbo].[STBingTileXYToQuadKey] (@v_tileX, @v_tileY, @v_levelOfDetail );

  RETURN @v_quadKey;
END;
GO

-- ************************************************************************

drop function if exists [dbo].[STBing2TileXY];
GO

create function [dbo].[STBing2TileXY] (
  @p_QuadKey varchar(100)
)
returns @result table (
          tileX         integer,
          tileY         integer,
		  levelOfDetail integer
        )
AS
-- public static void QuadKeyToTileXY(string quadKey, out int tileX, out int tileY, out int levelOfDetail)
BEGIN
  Declare
    @v_tileX         integer = 0,
    @v_tileY         integer = 0,
    @v_levelOfDetail integer = 1,
    @v_i             integer,
    @v_mask          integer,
    @v_qk            varchar(100);
	
    SET @v_levelOfDetail = LEN(@p_quadKey);
    SET @v_i = @v_levelOfDetail;
    WHILE ( @v_i > 0 )
    BEGIN
	  SET @v_mask = [dbo].[leftShift](1,(@v_i - 1));

	  SET @v_qk = SUBSTRING(@p_QuadKey,@v_levelOfDetail - @v_i + 1,1);
	  IF ( @v_qk = '0' ) break;
	  IF ( @v_qk = '1' ) BEGIN SET @v_tileX |= @v_mask; END;
	  IF ( @v_qk = '2' ) BEGIN SET @v_tileY |= @v_mask; END;
	  IF ( @v_qk = '3' ) BEGIN SET @v_tileX |= @v_mask; SET @v_tileY |= @v_mask; END;
      SET @v_i -= @v_i;
    END;
  INSERT INTO @result ( tileX,    tileY,    levelOfDetail ) 
       VALUES      ( @v_tileX, @v_tileY, @v_levelOfDetail ) 
  RETURN;
END;
GO

Some tests include:

Select [dbo].[STBingLatLongToQuadKey] (-42.0,147.0,3) as qKey
GO

qKey
313

select * from [dbo].[STBing2TileXY]('313');
GO

tileX tileY levelOfDetail
4     4     3

Select [dbo].[STBingTileXYToQuadKey] (7,5,3) as qKey
GO

qKey
313

I hope this is of use to someone.