MapForums

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;In  tegrated 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("")

        rssOutput.AppendLine(")

        rssOutput.AppendLine("xmlns:georss='http://www.georss.org/georss'")

        rssOutput.AppendLine("xmlns:gml='http://www.opengis.net/gml'>")

        rssOutput.AppendLine("")

        rssOutput.AppendLine("Population")

        rssOutput.AppendLine("")

        rssOutput.AppendLine("" + System.DateTime.Now + "")

        rssOutput.AppendLine("")

        rssOutput.AppendLine("SQL Server 2008")

        rssOutput.AppendLine("")



        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  element for this row

                            rssOutput.AppendLine("")



                            'Set title and description

                            rssOutput.AppendLine("")

                            rssOutput.AppendLine("" + _
                              geomRdr.GetValue(1).ToString + " People per Square Kilometer]]>")



                            'Add a  element

                            rssOutput.AppendLine("")

                            

                            '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.Replace("gml:/", "/gml:")



                            'Add the  elements to the output XML

                            rssOutput.AppendLine(gml)

                            

                            'Close  and  elements

                            rssOutput.AppendLine("")

                            rssOutput.AppendLine("")

                        End While

                    End Using

                End Using

            End Using



            'Close the  document and send it as the response

            rssOutput.Append("")

            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("{0}2007-12-06T18:30:02ZSQL Server 2008urn:uuid:60a76c80-d399-11d9-b93C-0003939e0af6", 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:
 

 

 
Technorati Tags: SQL Server 2008,Virtual Earth,UMN MapServer,Thematic Map,Business Intelligence

 
 Click here to view the article.

OpenLayers is probably a simpler way of getting MapServer and Virtual Earth to work together.

Pulling data from a database is something I haven't done in the OL/MPS/VE environment yet. For the Costa Rica stuff I simply used a KML file (another OpenLayers layer) which was edited "in the field" and uploaded in the evening. (there aren't many databases in the rain forest lol!)



Richard

__________________
Winwaed Software Technology LLC
http://www.winwaed.com
See http://www.mapping-tools.com for MapPoint Tools
See the Geoweb Guru for online mapping