Creating Chloropleth Maps with Virtual Earth

Submitted by Webmaster on Sun, 11/09/2008 - 09:49

This page gives a practical example demonstrating a number of different techniques introduced in the book. The objective is to use SQL Server and Virtual Earth to create a chloropleth map of the United States, where each state is shaded blue or red according to whether the Democratic Party or Republican Party won most votes in that state in the 2008 US presidential election.

The finished product

The key steps involved are as follows:

Obtain and combine two sets of publicly-available data (US State outlines and Election Data)
Reproject US State data into the correct SRID
Import Shapefile data into SQL Server 2008 using Shape2SQL
Create a stored procedure that retrieves the information
Create a .NET handler that executes the stored procedure, retrieves the results, and manipulates them into the equivalent constructor methods for Virtual Earth
Create an HTML page that contains a Virtual Earth map object, and add the necessary JavaScript functions to call the .NET handler

All of these techniques are covered in detail in "Beginning Spatial with SQL Server 2008", so I'll just outline the required code.

1. Obtain the required data

You can get a zipfile containing the cartographic boundary files representing all 50 US States from the US government census website, here: http://www.census.gov/geo/cob/bdy/st/st00shp/st99_d00_shp.zip You also need to get the voting data for each state. There are lots of sources of this on the internet. I used the table of results by state on the following wikipedia page: http://en.wikipedia.org/wiki/United_States_presidential_election,_2008#R...

2. Reproject the shapefile data

The US Census Shapefile data is supplied using EPSG:4269 (NAD83). However, we want to display the results of Virtual Earth, for which we need to supply coordinates using EPSG:4326 (WGS 84). Therefore, we first need to reproject the shapefile. Unfortunately SQL Server 2008 doesn't natively support reprojection of spatial data, but there are a number of external tools that you can use to do so. I recommend either Safe FME (commercial) or FWTools .

3. Importing the data

Firstly, you need to import the converted EPSG:4326 data using Shape2SQL. Since we won't be performing any spatial operations on the data other than simply retrieving the WKT representation to pass to Virtual Earth, it doesn't matter whether you store the state outlines using a geometry or a geography column. Seeing as EPSG:4326 is a geographic coordinate system, it's best practice to use geography though. Once you have imported the shapefile, you need to add a new column to contain the result of the electoral vote. For this example, I'll just use a simple CHAR(1) column called ELEC_RESULT that will either contain the value 'D' or 'R' (depending on whether the Democrats or Republicans won that state). You could, of course, maintain the exact number of votes cast for each party and use that to shade the results accordingly. If you want, you can download the following SQL script to create the table for you. US Election Data script.

4. Create the stored procedure

The stored procedure will retrieve the results from the table and pass them to the .NET handler. There's no special magic done by the stored procedure - it just retrieves the election result and the WKT representation of the shape of each state. It is the role of the .NET handler to take the results returned by the stored procedure and turn them into the appropriate constructor functions for Virtual Earth.

CREATE PROCEDURE [dbo].[uspUSElection2008] AS
BEGIN
SELECT
  STATE_SHAPE.STGeometryType() AS GeometryType,
  STATE_SHAPE.STAsText() AS WKT,
  STATE_NAME AS Title,
  ELEC_RESULT
FROM
  Election_08
END
GO

5. Create the .NET handler

The .NET handler creates a connection to SQL Server, executes the stored procedure, and loops through the resultset. It manipulates the WKT representation of each state to the appropriate syntax required to create a Virtual Earth polygon representating that state. It then sets the fill color of each polygon based on whether the value in the election result column was 'D' or 'R'. Website -> Add New Item -> Generic Handler For this example, I'll use Visual Basic .NET and call the handler USElection2008.ashx

<%@ WebHandler Language="VB" Class="USElection2008" %>
 
Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text
 
Public Class USElection2008 : Implements IHttpHandler
 
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
 
    'The response will be javascript
    context.Response.ContentType = "text/javascript"
 
    'Tell the browser not to cache the results
    context.Response.CacheControl = "no-cache"
    context.Response.Expires = 0
 
    'Define the variables
    Dim JS As String = "" 'The javascript we will build and send back
    Dim WKT As String 'The original WKT representation
    Dim VE As String 'The VE equivalent representation
    Dim VEShapeType As String 'The type of VE geometry
    Dim shapeid As Integer = 0 'Shape counter
    Dim FillColour As String = ""
    Dim LineColour As String = ""
 
    'Set up a connection to the server
    Dim myConn = New SqlConnection("server=ecco;" & _
                                   "Trusted_Connection=yes;" & _
                                   "database=Spatial")
 
    'Open the connection
    myConn.Open()
 
    'Define the stored prcedure to execute
    Dim myQuery As String = "dbo.uspUSElection2008"
    Dim myCMD As New SqlCommand(myQuery, myConn)
    myCMD.CommandType = Data.CommandType.StoredProcedure
 
    'Create a reader for the resultset
    Dim myReader As SqlDataReader = myCMD.ExecuteReader()
 
    'Go through the results
    While myReader.Read()
 
      Select Case myReader("ELEC_RESULT").ToString
        Case "D"
          FillColour = "new VEColor(0,0,255,0.7)"
          LineColour = "new VEColor(255,255,255,0.7)"
        Case "R"
          FillColour = "new VEColor(255,0,0,0.7)"
          LineColour = "new VEColor(255,255,255,0.7)"
      End Select
 
      Select Case myReader("GeometryType").ToString
        Case "Polygon"
          'The Virtual Earth equivalent of a POINT is a Pushpin
          VEShapeType = "VEShapeType.Polygon"
          'Get the WKT representation of the object
          WKT = myReader("WKT").ToString
          'Replace the double brackets which surround the co-ordinate point pairs
          WKT = Replace(WKT, "POLYGON ((", "")
          'Remove the last double brackets
          WKT = Replace(WKT, "))", "")
          'Create an array of each point in the polygon   
          Dim PointArray() As String = Split(WKT, ",")
          'Build the VE definition
          VE = ""
          Dim i As Integer = 0
          While i <= PointArray.Length - 1
            Dim Coords() = Split(Trim(PointArray(i)), " ")
            VE = VE + "new VELatLong(" + Coords(1) + "," + Coords(0) + "),"
            i = i + 1
          End While
          'Remove the last trailing comma
          VE = Left(VE, VE.Length - 1)
 
          'Add the constructor for the new shape
                    JS += "var shape" + shapeid.ToString & " = new VEShape(" + VEShapeType + ", [" + VE + "]);"
          'Don't show the icon for polygons
          JS += "shape" + shapeid.ToString + ".HideIcon();"
          'Set the line and fill colour
          JS += "shape" + shapeid.ToString & ".SetLineColor(" + LineColour + ");"
          JS += "shape" + shapeid.ToString & ".SetFillColor(" + FillColour.ToString + ");"
          'Add this shape to the map
          JS += "map.AddShape(shape" + shapeid.ToString + ");"
 
        Case "MultiPolygon"
          'The Virtual Earth equivalent of a POINT is a Pushpin
          VEShapeType = "VEShapeType.Polygon"
          'Get the WKT representation of the object
          WKT = myReader("WKT").ToString
          'Replace the triple brackets which surround the co-ordinate point pairs
          WKT = Replace(WKT, "MULTIPOLYGON (((", "")
          'Remove the last triple brackets
          WKT = Replace(WKT, ")))", "")
          'Create an array of each polygon
          Dim PolyArray() As String = Split(WKT, ")), ((")
          Dim k As Integer = 0
          While k < PolyArray.Length
            'Create an array of each point in the polygon   
            Dim PointArray() As String = Split(PolyArray(k), ",")
            'Build the VE definition
            VE = ""
            Dim i As Integer = 0
            While i <= PointArray.Length - 1
              Dim Coords() = Split(Trim(PointArray(i)), " ")
              VE = VE + "new VELatLong(" + Coords(1) + "," + Coords(0) + "),"
              i = i + 1
            End While
            'Remove the last trailing comma
            VE = Left(VE, VE.Length - 1)
            'Add the constructor for the new shape
            JS += "var shape" + shapeid.ToString & " = new VEShape(" + VEShapeType + ", [" + VE + "]);"
            'Don't show the icon for polygons
            JS += "shape" + shapeid.ToString + ".HideIcon();"
            'Set the line and fill colour
            JS += "shape" + shapeid.ToString & ".SetLineColor(" + LineColour + ");"
            JS += "shape" + shapeid.ToString & ".SetFillColor(" + FillColour.ToString + ");"
            'Add this shape to the map
            JS += "map.AddShape(shape" + shapeid.ToString + ");"
            'Go to the next polygon in the multipolygon collection
            k = k + 1
          End While
 
      End Select
 
      'Increment the shape counter
      shapeid = shapeid + 1
 
    End While
 
    'Close the reader
    myReader.Close()
    'Close the connection
    myConn.Close()
 
    'Output all of the javascript to create all the objects
    context.Response.Write(JS)
 
  End Sub
 
  Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
    Get
      Return False
    End Get
  End Property
 
End Class

6. Create the HTML page and JavaScript

Finally, we need to create the webpage that will contain the Virtual Earth control, and get it to call the .NET handler (via AJAX), and eval() the results to add them to the map. Website -> Add New Item -> HTML Page -> USElection2008.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>US Presidential Election 2008 Chloropleth Map</title>
  <script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2" type="text/javascript"></script>
  <script src="USElection2008.js" type="text/javascript"></script>
</head>
<body>
  <div id="divMap" style="position:absolute; width:640px; height:480px;"></div>
</body>
</html>

// Declare the global map object
var map = null;
// Set the Load callback
if (window.addEventListener) { window.addEventListener("load", getMap, false); }
else if (window.attachEvent) { window.attachEvent("onload", getMap); }
// Set the unLoad callback
if (window.addEventListener) { window.addEventListener("unload", disposeMap, false); }
else if (window.attachEvent) { window.attachEvent("onunload", disposeMap); }
 
// This function is called when the page has been loaded
function getMap() {
    // Create a new map object in the divMap container
    map = new VEMap('divMap');
    // Configure the initial map view
    map.LoadMap(new VELatLong(40, -100), 3, VEMapStyle.Road);
    // Call the loadCountryData function when the user clicks the map
    loadElectionData();
}
 
// This function is called when the page is unloaded
function disposeMap() {
    // Release all resources assigned to the map
    map.Dispose();
    // Unset the map variable
    map = null;
}
 
function loadElectionData() {
    //Get the appropriate XMLHTTP object for the browser
    var xmlhttp = GetXmlHttp();
    // If we have a valid XMLHTTP object
    if (xmlhttp) {
        // Open an XmlHTTP request to the .NET handler
        xmlhttp.open("GET", "./USElection2008.ashx", true);
        // Fire this when the readyState of the request changes
        xmlhttp.onreadystatechange = function() {
            // readystate 4 indicates that the request is complete  
            if (xmlhttp.readyState == 4) {
                // Read in the javascript response from the handler
                var result = xmlhttp.responseText;
                // Update the status message
                window.status = 'Loading Data...';
                try {
                    // Execute the dynamically created javascript
                    eval(result);
                    // Update the status message
                    window.status = 'Data Loaded!';
                }
                catch (e) {
                    // If the response cannot be evaluated
                    window.status = 'Data could not be loaded.';
                    alert(e.description);
                }
            }
        }
        // Send the request
        xmlhttp.send(null)
    }
}
 
// This function creates a cross-browser AJAX object
function GetXmlHttp() {
    var x = null;
    try { x = new ActiveXObject("Msxml2.XMLHTTP"); }
    catch (e) {
        try { x = new ActiveXObject("Microsoft.XMLHTTP"); }
        catch (e) { x = null; }
    }
    if (!x && typeof XMLHttpRequest != "undefined") {
        x = new XMLHttpRequest();
    }
    return x;
}

Comments