Examining 3D Terrain of Bing Maps Tiles with SQL Server 2008 and WPF (Part 2)

In the last post, I downloaded some elevation data from the GTOPO digital elevation model and loaded it into a SQL Server table. In this post, I’m now going to select a subset of that data in order to create a terrain map of a particular Bing Maps tile.

The Bing Maps Tile System

As you probably already know, Bing Maps are constructed from a set of raster “tiles” – prerendered 256px x 256px gif, jpg, or png images that form the background of each map. There are different sets of tiles that correspond to the different styles of map.

Here’s a few examples:

r120200223312

Road Map Tile

h120200223312

Aerial Map Tile

r120200223312

Ordnance Survey Map Tile

Every tile is numbered with a quadkey – a unique identifier that exactly describes the position and zoom level of where that tile should be placed. I’m not going to explain the quadkey system here, because there’s already an excellent article on MSDN.

The important fact is that, by knowing only the quadkey of a tile, it is possible to determine the exact coordinate boundaries of every corner of that tile. In SQL Server, that means we can construct a POLYGON that represents the extent of that tile, and retrieve any data that lies on the tile (or, in this example, the elevation data that corresponds to the terrain of that tile). The following method demonstrates how to return a SqlGeography polygon from a Bing Maps quadkey:

public static SqlGeography GeographyPolygonFromQuadKey(string quadKey) {

      int tilex = 0, tiley = 0;

      int tilewidth = 256, tileheight = 256;

      int zoomLevel = quadKey.Length;

      // Work out the x and y (grid) position of this tile

      for (int i = zoomLevel; i > 0; i--)

      {

        int mask = 1 << (i - 1);

        switch (quadKey[zoomLevel - i])

        {

                case '0':

                    break;

                case '1':

                    tilex |= mask;

                    break;

                case '2':

                    tiley |= mask;

                    break;

                case '3':

                    tilex |= mask;

                    tiley |= mask;

                    break;

                default:

                    throw new ArgumentException("Invalid QuadKey digit sequence.");

            }

        }

 

        // From the grid position and zoom, work out the min and max Latitude / Longitude values of this tile

        float minLongitude = (float)(tilex * tilewidth) * 360 / (float)(tilewidth * Math.Pow(2, zoomLevel)) - 180;

        float maxLatitude = (float)Math.Asin((Math.Exp((0.5 - (tiley * tileheight) / (tileheight) / Math.Pow(2, zoomLevel)) * 4 * Math.PI) - 1) / (Math.Exp((0.5 - (tiley * tileheight) / 256 / Math.Pow(2, zoomLevel)) * 4 * Math.PI) + 1)) * 180 / (float)Math.PI;

        float maxLongitude = (float)((tilex + 1) * tilewidth) * 360 / (float)(tilewidth * Math.Pow(2, zoomLevel)) - 180;

        float minLatitude = (float)Math.Asin((Math.Exp((0.5 - ((tiley + 1) * tileheight) / (tileheight) / Math.Pow(2, zoomLevel)) * 4 * Math.PI) - 1) / (Math.Exp((0.5 - ((tiley + 1) * tileheight) / 256 / Math.Pow(2, zoomLevel)) * 4 * Math.PI) + 1)) * 180 / (float)Math.PI;

 

        // Create a new SqlGeography instance representing the extent of this tile

        SqlGeographyBuilder gb = new SqlGeographyBuilder();

        gb.SetSrid(4326);

        gb.BeginGeography(OpenGisGeographyType.Polygon);

        gb.BeginFigure(minLongitude, maxLatitude);

        gb.AddLine(minLongitude, minLatitude);

        gb.AddLine(maxLongitude, minLatitude);

        gb.AddLine(maxLongitude, maxLatitude);

        gb.AddLine(minLongitude, maxLatitude);

        gb.EndFigure();

        gb.EndGeography();

 

      return gb.ConstructedGeography;

    }

 

Now, I just need to choose a particular tile whose terrain to examine…. I’ve chosen the tile at zoom level 6 that portrays most of Scotland – my ancestral home and also home to some interesting geographic features (i.e. mountains and lochs!). Here’s the tile viewed using Bing Maps’ road map style:

r031133

The quadkey for this tile is 031133 and, using the method above, I can calculate the corresponding SqlGeography as:

POLYGON((-5.625 55.7765730186677, 0 55.7765730186677, 0 58.8137417157078, –5.625 58.8137417157078, -5.625 55.7765730186677))

Therefore, we can retrieve the relevant GTOPO30 elevation data for this tile from SQL Server using the following query:

SELECT * FROM W020N90 WHERE geog4326.STIntersects(geography::STPolyFromText(‘POLYGON((-5.625 55.7765730186677, 0 55.7765730186677, 0 58.8137417157078, –5.625 58.8137417157078, -5.625 55.7765730186677))’, 4326) = 1;

Here’s the results:

image

In the next post, I’ll use this elevation data to create a 3d surface onto which to overlay the Bing Maps tile image.

This entry was posted in Bing Maps, SQL Server and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s