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

Following on from my last last post, I thought I’d continue to explore the topic of altitude (or elevation) and how it relates to Bing Maps.

A map is, by definition, two-dimensional – it is an image that has been projected onto a flat surface. However, it is still possible, and on many occasions very important, to portray three-dimensional properties of features on that map. One way of doing this is by presenting an oblique, “birdseye” view rather than a pure top-down view. This is certainly an effective way to present the height of individual distinct features such as buildings, as in the default Bing Maps control. But what about displaying variations in height of the underlying terrain? In this first of a set of posts I’ll look at a way of combining altitude data, SQL Server, and WPF to create a “3D” view of the elevation of terrain on a Bing Maps tile.

Obtaining Altitude Data

Bing Maps does not expose altitude data, so the first step is to acquire this data from somewhere else. There have been various scientific surveys of global elevation data, produced by government agencies and scientific organisations such as NASA. Three common sets of data are as follows:

• GTOPO30 – global elevation grid at resolution of 30 arcseconds (approx 1km).
• SRTM – elevation grid at resolution 3 arcseconds (approx 90m) between latitudes –56 and +60.
• ASTER – elevation grid with resolution 1 arcsecond (approx 30m) between latitudes –83 and 83.

Elevation Webservices

The data from various elevation surveys is exposed via webservices provided by the USGS, Geonames and Google (amongst others). For example, to determine the altitude of the point located at (53.06839056444848, -4.076281785964964), you could call one of the following URLs

• http://ws.geonames.org/gtopo30?lat=53.06839056444848&lng=-4.076281785964964
• http://maps.googleapis.com/maps/api/elevation/xml?locations=53.06839056444848,-4.076281785964964&sensor=false

These services are very useful if you want to retrieve the elevation of only one, or a small number of points. However, for this example I’m going to use several thousand elevation points spread out across the surface of a Bing Maps tile, so these services aren’t really suitable.

Elevation Datasets

Each dataset is also available to download in full. I’ll be using the GTOP30 dataset, which you can download from http://eros.usgs.gov/#/Find_Data/Products_and_Data_Available/gtopo30_info. It’s split into several smaller tiles, so if you only want to download a particular area that’s fine. Tiles are named according to the coordinates at the top left corner. I’m going to use the W020N90 tile, which starts at a longitude of –20 and latitude of 90, and covers most of Western Europe, as highlighted below:

The W020N90 data is provided as a gzip archive, which is about 8.6Mb in size.

Converting and Loading into SQL Server

The GTOPO data is supplied as a DEM image, so the next step is to convert it into a more useable format. I’m going to use the excellent free MicroDEM program to crop a section of the W020N90 DEM file and save it as ASCII text file.

Converting the DEM Binary file to ASCII XYZ

1. Load the DEM data into MicroDEM by selected File —> Open, and selecting the HDR header file from the GTOPO download.
2. Once the data has loaded, use the Subset & zoom tool from the MicroDEM menu bar to select the part of the map to crop (if desired).
3. Select File –> Save DEM –> Current subset, MD DEM.
4. Close the current file and re-open the newly saved cropped file.
5. Save the cropped file in text format by selecting File –> Save DEM –> ASCII XYZ.

Tidying Up the ASCII XYZ file

Once saved out as a ASCII XYZ file, the elevation data is stored as a very simple dataset with three columns as follows:

LAT        LONG        ELEVATION

52.837501  -8.479166   55
53.670834  -8.479166   77
…

The only slightly annoying thing about the XYZ export from MicroDEM is that there isn’t a consistent separator used to delimit the columns – it seems that any length of space is used as a separator. To fix this, I then opened up Visual Studio and performed a quick replace (Ctrl + H) using regular expressions:

• First, to remove leading spaces on some of the lines, search for ^[ \t]+ and replace with an empty string.
• Then, to replace the variable-length spaces between columns, search for [ ][ ]* and replace with ,

We’ve now got a nice comma-separated CSV file that is easy to import into SQL Server:

LAT,LONG,ELEVATION

52.837501,-8.479166,55
53.670834,-8.479166,77
…

Importing into SQL Server

Once you’ve imported the CSV file into a new table in SQL Server (Use the import/export wizard), we can add a geography column to that table, and populate it with a Point in each row. Since we want our Points to contain Z values, we have to use one of the WKT static methods (the geography Point() method, the xxxFromWKB() and GeomFromGml() methods can only be used to create two-dimensional coordinates):

ALTER TABLE W020N90

ADD GEOG4326 geography;

UPDATE W020N90

SET GEOG4326 = geography::STPointFromText('POINT(' + LONG + ' ' + LAT + ' ' + ELEVATION + ')', 4326);

(note that WKT requires longitude/latitude/altitude coordinate ordering, not latitude/longitude/altitude as you might expect).

At this stage, you can check the GTOPO elevation point data (well, the first 5,000 rows of it, anyway) by executing a SELECT query from the SQL Server table and checking the Spatial Results tab in SQL Server Management Studio, as shown below:

In the next post, I’ll examine how you can convert this table of points into a set of polygonal faces that can be used as the basis for a 3D terrain model for any particular geographic area.