Introduction

Creating thematic maps with Virtual Earth used to be one of the more difficult tasks. It is possible to to create a VEShapeLayer and group VEShape-objects of type point, line or polygon in it but the more points you have in a polygon the longer the rendering will take and if you want to create a map for example with colour-coded countries we are talking about some hundred-thousand points at least. This wouldn’t perform well in a VEShapeLayer at all.

As an alternative you can create a VETileLayer. Rather than using vector-data and rendering them in the MapControl we can render the data in advance into a raster-format and superimpose them as a layer on top of the base-map. Creating these tile-layers is often done with the MapCruncher (see also this posting). While the MapCruncher is simple to use, it only supports image-formats and PDF-documents but not other spatial data formats. I have already praised Safe FME a couple of times as the ‘Swiss Army Knife’ for loading, extracting and transforming spatial data and it is a powerful tool to create static VETileLayer from more than 200 different spatialdata-formats. However, it will only create a static layer and is not the ideal tool if you have frequently changing data.

More dynamic tile layers can be created through OGC-compliant WMS and you will find a posting about this here but even then you overlay static images and loose the metadata. In order to maintain the metadata and create the tile-layer on the fly we need to keep an interactive connection to the data source.

This blog posting will guide you through an example where we create a VETileLayer from SQL Server 2008 and a call-back-function that allows us to retrieve the metadata. In order to connect the ‘Beauty and the Beast’ we will use the UMN MapServer. “MapServer is an Open Source development environment for building spatially-enabled internet applications. It is not a full-featured GIS system, nor does it aspire to be. Instead, MapServer excels at rendering spatial data (maps, images, and vector data) for the web.” MapServer was originally developed by the University of Minnesota in cooperation with NASA and the Minnesota Department of Natural Resources. It is now a project of OSGeo. Since version 5.2 MapServer provides a connector for SQL Server 2008 as well as a tilemode which supports Virtual Earth directly. A packaged solution for the Windows environment is available as ‘MapServer for Windows’ or short MS4W.

Our architecture will look like this:

Loading the Database

First let’s choose some data that we can use for a thematic map. On the ‘Geo Data Portal’ of the United Nations Environment Program you will find lot’s of statistical information. I chose the Population Density and downloaded the data as ESRI Shapefile. To load the data into SQL Server 2008 we could use Safe FME but since the downloaded data are already in a coordinate system that we can use in Virtual Earth (WGS 84) and the source format is an ESRI Shapefile we can also use Morten Nielsen’s SQL Spatial Tools. The tool will automatically create a spatial index for you. Make sure to set the Spatial Reference ID (SRID) to 4326.

To validate the data run the following SQL statement:

update pop_density set geom=geom.MakeValid()

We can preview the data already in the SQL Server Management Studio:

Configuring the Layer in UMN MapServer

The UMN MapServer is configured through a simple text-file with the extension *.map. Let’s assume we create a file sql.map in the subdirectory C:\ms4w\apps\sql and paste the following configuration. The interesting part starts at the layer-definition where we define the database connection and the styles depending on the value.

MAP

 # Prefix attached to map, scalebar and legend GIF filenames 
 # created using this MapFile. It should be kept short
 NAME ms_sql

 # Color to initialize the map with (i.e. background color). 
 # When transparency is enabled (TRANSPARENT ON) for the 
 # typical case of 8-bit pseudocolored map generation, this 
 # color will be marked as transparent in the output file 
 # palette. Any other map components drawn in this color will 
 # also be transparenct, so for map generation with transparency 
 # it is best to use an otherwise unused color as the background 
 # color.
 IMAGECOLOR 255 255 255

 # default output image dimensions
 SIZE 256 256

 # Is the map active? Sometimes you may wish to turn this off to 
 # use only the reference map or scale bar.
 STATUS ON

 # set top level projection
 PROJECTION
  "init=epsg:4326"
 END

 # image format options
 OUTPUTFORMAT
  NAME png
  DRIVER "GD/PNG"
  MIMETYPE "image/png"
  IMAGEMODE RGB
  EXTENSION "png"
  TRANSPARENT ON
 END

 EXTENT -180 -90 180 90 # World

# start of layer definitions
LAYER 
NAME "PopDens2007" 
CONNECTIONTYPE PLUGIN 
PLUGIN "C:/ms4w/Apache/specialplugins/msplugin_mssql2008.dll"
CONNECTION "server=jkebeck1;uid=sa;pwd=not4all;database=UMN;Integrated Security=false" 
DATA "GEOM from pop_density using SRID=4326"
TYPE polygon
CLASSITEM "Y_2007"
STATUS ON 
PROJECTION 
  "init=epsg:4326" 
END 
CLASS 
  EXPRESSION ([Y_2007]>400)
  STYLE 
    COLOR 255 0 0
    OUTLINECOLOR 255 255 255
  END 
END 
CLASS 
  EXPRESSION (([Y_2007]>250)AND([Y_2007]<=400))
  STYLE 
    COLOR 255 255 0
    OUTLINECOLOR 255 255 255
  END 
END 
CLASS 
  EXPRESSION (([Y_2007]>100)AND([Y_2007]<=250))
  STYLE 
    COLOR 0 153 0
    OUTLINECOLOR 255 255 255
  END 
END 
CLASS 
  EXPRESSION (([Y_2007]>0)AND([Y_2007]<=100))
  STYLE 
    COLOR 102 255 102
    OUTLINECOLOR 255 255 255
  END 
END 
CLASS 
  EXPRESSION ([Y_2007]<=0)
  STYLE 
    COLOR 255 255 255
    OUTLINECOLOR 255 255 255
  END 
END 
END 

END

This is already enough to test the service. Try the following call: http://localhost:8081/cgi-bin/mapserv.exe?map=/ms4w/apps/sql/sql.map&layers=PopDens2007&mode=tile&tilemode=ve&tile=1

This should return a Virtual Earth tile like this:

Creating the Virtual Earth Application

The Virtual Earth application is very simple to create. You can follow the sample for the ‘Custom Tile Layers’ from the Interactive SDK. The URL for the VETileSourceSpecification needs to point to the UMN MapServer as shown below.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
   <head>
      <title></title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
      <script type="text/javascript">
         var map = null;
         
        function GetMap()
        {
            map = new VEMap('myMap');
            map.LoadMap(new VELatLong(0, 0), 0 ,'s' ,false);
        }

        function AddMSTileMode(control, layer) {
            if (document.getElementById(control).checked == false) {
                map.DeleteTileLayer(layer);
            }
            else {
                var url = "http://localhost:8081/cgi-bin/mapserv.exe?";
                url += "map=/ms4w/apps/sql/sql.map&";
                url += "layers=PopDens2007&";
                url += "mode=tile&";
                url += "tilemode=ve&";
                url += "tile=%4";
                var tileSourceSpec = new VETileSourceSpecification(layer, url);
                tileSourceSpec.Opacity = 0.5;
                map.AddTileLayer(tileSourceSpec, true);
            }
        }
      </script>
   </head>
   <body onload="GetMap();">
      <div id='myMap' style="position:absolute; top:0px; left:0px; width:600px; height:400px;"></div><br />
      <div id='divCtrl' style="position:absolute; top:400px; left:0px; width:600px;" >
        <input id="cbMSTileMode" type="checkbox" onclick="AddMSTileMode('cbMSTileMode', 'tlMSTileMode')" />
          Population Density 2007 (People per Square Kilometer)<br />
      </div>
   </body>
</html>

Adding a Cache

To reduce the load on the SQL Server and increase performance we will implement a cache. Rather than pointing directly to our UMN MapServer we will call a Generic WebHandler:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
   <head>
      <title></title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
      <script type="text/javascript">
        var map = null;
         
        function GetMap()
        {
            map = new VEMap('myMap');
            map.LoadMap(new VELatLong(0, 0), 0 ,'s' ,false);
        }

        //Tile Layer
        function AddMSTileMode(control, layer, url, opac) {
            if (document.getElementById(control).checked == false) {
                map.DeleteTileLayer(layer);
            }
            else {
                var tileSourceSpec = new VETileSourceSpecification(layer, url);
                tileSourceSpec.Opacity = opac;
                map.AddTileLayer(tileSourceSpec);
            }
        }
      </script>
   </head>
   <body onload="GetMap();">
      <div id='myMap' style="position:absolute; top:0px; left:0px; width:600px; height:400px;"></div><br />
      <div id='divCtrl' style="position:absolute; top:400px; left:0px; width:600px;" >
        <input id="cbMSTileMode" type="checkbox" onclick="AddMSTileMode('cbMSTileMode', 'tlMSTileMode', 
          'TileCache.ashx?id=%4', 0.5)" />Population Density 2007 (People per Square Kilometer)<br />
      </div>
   </body>
</html>

The WebHandler will test if we have a pre-generated tile in a cache directory and if we don’t it will call the UMN MapServer and save a copy in the cache directory as well.

<%@ WebHandler Language="VB" Class="TileCache" %>

Imports System
Imports System.Web
Imports System.Drawing
Imports System.IO

Public Class TileCache : Implements IHttpHandler

    Public requestParam As String

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        'Fetch URL-parameter
        requestParam = context.Request.Params("ID")

        'Is file in disk-cache?
        If File.Exists("c:/tmp/TileCache/" + requestParam + ".png") Then
            Dim myImage As New Bitmap(System.Drawing.Image.FromFile("c:/tmp/TileCache/" + requestParam + ".png"))
            WritePngToStream(myImage, context.Response.OutputStream)
        Else
            Dim myURL As String = ""
            myURL = "http://localhost:8081/cgi-bin/mapserv.exe?map=/ms4w/apps/sql/sql.map&layers=PopDens2007" + _
              "&mode=tile&tilemode=ve&tile=" & requestParam
            
            Dim myRequest As System.Net.WebRequest
            myRequest = System.Net.WebRequest.Create(myURL)
            Dim myResponse As System.Net.WebResponse
            myResponse = CType(myRequest.GetResponse, System.Net.WebResponse)

            Dim myImage As New Bitmap(System.Drawing.Image.FromStream(myResponse.GetResponseStream))
            WritePngToStream(myImage, context.Response.OutputStream)
        End If
    End Sub
    
    Private Sub WritePngToStream(ByVal image As Bitmap, ByVal outStream As Stream)
        Dim writeStream As New MemoryStream()
        image.Save(writeStream, Imaging.ImageFormat.Png)
        If Not File.Exists("c:/tmp/TileCache/" + requestParam + ".png") Then
            image.Save("c:/tmp/TileCache/" + requestParam + ".png", Imaging.ImageFormat.Png)
        End If
        writeStream.WriteTo(outStream)
        image.Dispose()
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

Creating a GetFeature-Call to retrieve the Meta-Data

All right, now we have our thematic map and a good performance as well. In the final step we will create a callback-function that allows us to click on the map and get the meta data to that clicked location. In our Virtual Earth application we add an event that captures a click in the map. We determine the clicked location and create a call to another Generic Web Handler (GetFeature.ashx).

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
   <head>
      <title></title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
      <script type="text/javascript">
          var map = null;
          var slFeature = new VEShapeLayer();
         
        function GetMap()
        {
            map = new VEMap('myMap');
            map.LoadMap(new VELatLong(0, 0), 0 ,'s' ,false);
        }

        //Tile Layer
        function AddMSTileMode(control, layer, url, opac) {
            if (document.getElementById(control).checked == false) {
                map.DeleteTileLayer(layer);
                map.DetachEvent("onclick", RightClick);
                slFeature.DeleteAllShapes();
            }
            else {
                var tileSourceSpec = new VETileSourceSpecification(layer, url);
                tileSourceSpec.Opacity = opac;
                map.AddTileLayer(tileSourceSpec);
                map.AttachEvent("onclick", RightClick);
            }
        }

        function RightClick(e) {
            if (e.rightMouseButton == true) {
                var x = e.mapX;
                var y = e.mapY;
                pixel = new VEPixel(x, y);
                var LL = map.PixelToLatLong(pixel);
                var Lat = LL.Latitude;
                var Lon = LL.Longitude;
                LoadGeoRSS(Lat, Lon);
            }
        }

        function LoadGeoRSS(Lat, Lon) {
            slFeature.DeleteAllShapes();

            //Build URL to call the server
            var url = "./GetFeature.ashx?";
            url += "Lat=" + Lat;
            url += "&Lon=" + Lon;

            var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, url, slFeature);
            map.ImportShapeLayerData(veLayerSpec, onGeoRSSLoad, false);
        }

        function onGeoRSSLoad() {
            var numShapes = slFeature.GetShapeCount();
            for (var i = 0; i < numShapes; ++i) {
                var s = slFeature.GetShapeByIndex(i);
                //s.SetLineColor(new VEColor(255, 0, 0, 1));
                //s.SetFillColor(new VEColor(255, 0, 0, 0.5));
                s.SetCustomIcon("IMG/blue.png");
                map.ShowInfoBox(s);
            }
        }
      </script>
   </head>
   <body onload="GetMap();">
      <div id='myMap' style="position:absolute; top:0px; left:0px; width:600px; height:400px;"></div><br />
      <div id='divCtrl' style="position:absolute; top:400px; left:0px; width:600px;" >
        <input id="cbMSTileMode" type="checkbox" onclick="AddMSTileMode('cbMSTileMode', 'tlMSTileMode', 
          'TileCache.ashx?id=%4', 0.5)" />Population Density 2007 (People per Square Kilometer)<br />
      </div>
   </body>
</html>

As you can see we are expecting the web handler to return a GeoRSS-feed. This makes a lot of sense since SQL Server 2008 can return results as GML. GML is the basis for one of the GeoRSS-specifications that is supported by Virtual Earth and we will only need to add a few tags at the beginning and the end.

<%@ WebHandler Language="VB" Class="GetFeature" %>

Imports System
Imports System.Web
Imports System.Data.SqlClient

Public Class GetFeature : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        'Fetch URL-parameter
        Dim Lat As String = context.Request.Params("Lat")
        Dim Lon As String = context.Request.Params("Lon")

        'Retrieve Database Setting from web.config
        Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("UMN")

        context.Response.ContentType = "text/xml"
        
        'Build the GeoRSS feed
        Dim rssOutput As New System.Text.StringBuilder("<?xml version='1.0' encoding='utf-8'?>")
        rssOutput.AppendLine("<feed xmlns='http://www.w3.org/2005/Atom'")
        rssOutput.AppendLine("xmlns:georss='http://www.georss.org/georss'")
        rssOutput.AppendLine("xmlns:gml='http://www.opengis.net/gml'>")
        rssOutput.AppendLine("<title>Hannes Demo</title>")
        rssOutput.AppendLine("<subtitle>Population</subtitle>")
        rssOutput.AppendLine("<link href='http://johanneskebeck.spaces.live.com'/>")
        rssOutput.AppendLine("<updated>" + System.DateTime.Now + "</updated>")
        rssOutput.AppendLine("<author>")
        rssOutput.AppendLine("<name>SQL Server 2008</name>")
        rssOutput.AppendLine("</author>")

        Try
            Using myConn As New SqlConnection(settings.ConnectionString)

                'Open a connection to the database
                myConn.Open()

                Using cmd As New SqlCommand()
                    'Set SQL Parameters
                    cmd.Connection = myConn
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add(New SqlParameter("Lat", Lat))
                    cmd.Parameters.Add(New SqlParameter("Lon", Lon))

                    'Specify the stored procedure name as the command text
                    cmd.CommandText = "GetFeatureGML"
                    Using geomRdr As SqlDataReader = cmd.ExecuteReader()
                        'Read the DataReader to process each row
                        While (geomRdr.Read())
                            'Create an <entry> element for this row
                            rssOutput.AppendLine("<entry>")

                            'Set title and description
                            rssOutput.AppendLine("<title>" + geomRdr.GetValue(0).ToString + "</title>")
                            rssOutput.AppendLine("<description><![CDATA[Population Density 2007:<br>" + _
                              geomRdr.GetValue(1).ToString + " People per Square Kilometer]]></description>")

                            'Add a <georss:where> element
                            rssOutput.AppendLine("<georss:where>")
                            
                            'Get the geography instance GML from column 2
                            Dim gml As String
                            gml = geomRdr.GetValue(2).ToString()
                            
                            'Append the gml: prefix to all the elements due to VE parsing behavior
                            gml = gml.Replace("<", "<gml:")
                            gml = gml.Replace("gml:/", "/gml:")

                            'Add the <gml:> elements to the output XML
                            rssOutput.AppendLine(gml)
                            
                            'Close <georss:where> and <entry> elements
                            rssOutput.AppendLine("</georss:where>")
                            rssOutput.AppendLine("</entry>")
                        End While
                    End Using
                End Using
            End Using

            'Close the <feed> document and send it as the response
            rssOutput.Append("</feed>")
            context.Response.Write(rssOutput.ToString())
        Catch e As Exception
            OutputError(e.ToString(), context)
        End Try
    End Sub
 
    Public Sub OutputError(ByVal errorMsg As String, ByVal context As HttpContext)
        'Return a feed containing info if an error occurs
        Dim emptyOutput As String = String.Format("<?xml version='1.0' encoding='utf-8'?><feed xmlns='http://www.w3.org/2005/Atom' xmlns:georss='http://www.georss.org/georss' xmlns:gml='http://www.opengis.net/gml'><title>An Error Occurred</title><subtitle>{0}</subtitle><link href='http://localhost/storefindersite/'/><updated>2007-12-06T18:30:02Z</updated><author><name>SQL Server 2008</name></author><id>urn:uuid:60a76c80-d399-11d9-b93C-0003939e0af6</id></feed>", errorMsg)
        context.Response.Write(emptyOutput)
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

The web handler queries the database or more precise it calls a stored procedure which is defined as follows:

CREATE PROCEDURE [GetFeatureGML] @Lat VARCHAR(MAX), @Lon VARCHAR(MAX)
AS

DECLARE @clickString VARCHAR(MAX);
SET @clickString = 'POINT(' + @Lon + ' ' + @Lat + ')';
DECLARE @click GEOMETRY;
SET @click = GEOMETRY::STPointFromText(@clickString, 4326);
SELECT Name, Y_2007, (geography::STGeomFromWKB(GEOM.STCentroid().STAsBinary(), 4326)).AsGml() FROM pop_density 
  WHERE (GEOM.STContains(@click) = 1);

Well, that’s it here is the result:

The sample code is available here: