Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 2 of 2

Creating Thematic Maps with Virtual Earth and SQL Server 2008

This is a discussion on Creating Thematic Maps with Virtual Earth and SQL Server 2008 within the Bing Maps Blogs & Tweets forums, part of the Blogs category; Introduction Creating thematic maps with Virtual Earth used to be one of the more difficult tasks. It is possible to ...

  1. #1
    Johannes Kebeck's Blog is offline Senior Member Green Belt
    Join Date
    Sep 2007
    Posts
    154

    Creating Thematic Maps with Virtual Earth and SQL Server 2008

    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.

    image

    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.

    image*

    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:

    image

    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.

    image

    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:

    image

    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>

    image

    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 Clas
    s

    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("Hannes Demo")
    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("" </span>+ geomRdr.GetValue(0).ToString + <span style="color:#a31515">"")
    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("An Error Occurred{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 Clas
    s

    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:

    image

    The sample code is available here:





    Click here to view the article.

  2. #2
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: Creating Thematic Maps with Virtual Earth and SQL Server 2008

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (5/6)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-22-2008, 08:13 AM
  2. Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (4/6)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-03-2008, 12:51 PM
  3. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (3/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  4. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (2/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  5. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (1/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132