Data Visualization with Bing Maps

Introduction

Presenting your data effectively is often a challenging task. The most comprehensive information is usually stored in tables – often in databases. However, the more detailed this information is the more difficult it becomes to get a quick overview. There are many ways how you can provide drill downs but then you loose the big picture. Data that relates to geographies can be well presented on a map and in previous blogs I have described how to create heatmaps or thematic maps. The thematic maps sample uses the UMN MapServer to create a Bing Maps tile layer on the fly and implements a callback-function that retrieves the details for the location you clicked on from a database. In this walk-through I will have a different approach and create a static Bing Maps tile layer using Safe FME. I will also enhance the detailed view for this location by using the Microsoft Chart Controls:

image

The application will embed the Bing Maps AJAX control and load it along with the background maps from a Microsoft data centre. The tile layer with the thematic maps is hosted in an environment that you control – for example a virtual directory on your web server. We will attach an event to the map that captures a right-click, calculates the latitude and longitude of the location we clicked on and creates an asynchronous AJAX call to a web service. The web service will query the database, determines the country you clicked on, retrieves the detailed information, creates a pie chart and returns a VEShape-object in its response to the AJAX call. The AJAX call has been waiting for this response and adds the VEShape-object to the map.

image

For this example I use the following components

We will use some statistical information around the Gross Domestic Product (GDP) as available on the GEO Data Portal of the Unites Nations Environment Programme (UNEP) and go through the following steps in detail

  1. Create a Bing Maps tile layer using Safe FME
  2. Create the Bing Maps application to visualize the thematic map
  3. Load the database with our spatial and business data using Safe FME
  4. Create a pie chart to visualize the detailed information for a country
  5. Create a callback function that retrieves the details when we right-click on a map

Step 1: Creating the Bing Maps Tile Layer

For starters we need the country boundaries in a spatial data format and some statistical information that we can easily visualize through colour-coded maps and charts. A good source for this type of data is the GEO Data Portal of the Unites Nations Environment Programme (UNEP). For this example I searched for GDP and downloaded the following data sets on the national level for the year 2005 as ESRI Shape-files:

  1. Gross Domestic Product
  2. Gross Domestic Product – per Capita
  3. Gross Domestic Product – Annual Percentage Growth Rate
  4. Agriculture Value Added – Percent of GDP
  5. Industry Value Added – Percent of GDP
  6. Manufacturing Value Added – Percent of GDP
  7. Services Value Added – Percent of GDP

The data is compressed into tar.gz files and I used 7Zip to extract the archives. We only need the files starting with “GEO” from each archive.

image_thumb11

Now I use Safe FME to create my Bing Maps Tile Layer as follows:

image

We start with a source data set pointing to our ESRI Shape-file for the Gross Domestic Product per Capita. This file describes the coordinates already in the WGS84 coordinate system we use in Bing Maps but has a geographic extend that exceeds the addressable area in Bing Maps (green pattern in the image below); hence we need to clip it to the area that we can use. This limitation is due to the Mercator projection we use in Bing Maps and which is reasonable accurate only from -85.05112878 to 85.05112878 (for more details see this article).

image_thumb15

For the clipping we create a new polygon using the Creator; this polygon will then be used as the Clipper-attribute in the Clipper-Transformer. The geometries in the Shape-file are fed into this transformer as Clippees. The result is a set of geometries that covers only the addressable area in Bing Maps.

image_thumb17

Next we define the colours by sorting the countries based on the value for the GDP per Capita into buckets. For each bucket we assign a fill-colour and for all of them we use the same colour (white) for the boundaries.

image_thumb20

Now that we have the colours defined we go on and re-project the data into the Worldwide Mercator projection using the Reprojector with EPSG:3785 as output-projection. Next we rasterize the vector data. The VirtualEarthTiler can use parameters for minimum and maximum zoom-level but for best results I suggest to have a rasterizer for each Bing Maps zoom-level that you want to create. This will guarantee a even thickness for the country boundaries. In our example I render Bing Maps tiles for the zoom-levels 1 to 7 and use the following values for the number of cells in the rasterizer:

Zoom Pixel
1 512
2 1024
3 2048
4 4096
5 8192
6 16384
7 32768

 

Now we use the transformer VirtualEarthTiler to cut the raster into tiles and finally we write them as PNGRASTER to the file system using the quadkey-attribute that is created automatically by the VirtualEarthTiler as fanout-attribute. For more information on the Bing Maps tile system see this article.

image

We repeat the same for the GDP data set and have now 2 tile sets that we can use as layers for Bing Maps.

After FME has done it’s work create a virtual directory on your web server and point it to the location where you have the tiles.

Step 2: Create the Bing Maps Application for the Thematic Map

Our web page that implements our data visualization is a simple HTML-page. We reference the Bing Maps AJAX control in the header along with the script that contains our own JavaScript-functions. In the body we have a div-element that will host the map and another div-element with some checkboxes that allow us to switch the thematic layers on or off. When we activate the checkboxes we will fire a JavaScript-function and pass a couple of parameters into it:

  • the name of the control, e.g. ‘cbGDP’
  • the name of the tile layer, e.g. ‘GDP;
  • the latitudes and longitudes of the Northwest and the Southeast corner of the bounding box for which we want to show the layer, e.g. 90,-180,-90,180
  • the url that points to our virtual directory with the tiles with a ‘/%4.png’ at the end, e.g. ‘http://hannesvestorage.blob.core.windows.net/vetiles/GDP/%4.png’. The %4.png will make sure that the control searches in the virtual directory for tiles that have the same quadkey (filename) as the ones that are in the current map view.
  • the minimum and maximum zoom-level where we want to show this layer, e.g. 1,7
  • the opacity we want to use, e.g. 0.7
  • the z-index of the layer.
<!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>Bing Maps Demos</title>
    <link rel="shortcut icon" href="IMG/favicon.ico" /> 
    <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
    <script src="JS/MyScript.js" type="text/javascript"></script>
    <link href="CSS/MyStyles.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <div style="position:absolute; top:0px; left:0px; width:100%; height:50px;" class="header">
        <table>
            <tr>
                <td style="width:100px; text-align:left"><img src="IMG/BingMaps.png" alt="Bing Maps Logo" style="margin-left:5px; margin-top:5px" /></td>
                <td style="width:100%; text-align:center; white-space:nowrap">Data Visualization</td>
                <td style="width:100px; text-align:right"><img src="IMG/SQL08.png" alt="SQL Server 2008 Logo" style="margin-right:5px;" /></td>
            </tr>
        </table>
    </div>
    <div id="divCtrl" style="position:absolute; top:65px; left:10px; width:200px; height:300px;" class="ctrl">
        <div style="position:absolute; top:5px; left:5px; right:7px; width:90%">
            <input id="cbGDP" type="checkbox" onclick="AddTileLayer('cbGDP','GDP',90,-180,-90,180,'http://hannesvestorage.blob.core.windows.net/vetiles/GDP/%4.png',1,7,0.7,100)" />GDP<br />
            <input id="cbCapita" type="checkbox" onclick="AddTileLayer('cbCapita','Capita',90,-180,-90,180,'http://hannesvestorage.blob.core.windows.net/vetiles/GDP_Capita/%4.png',1,7,0.7,100)" />GDP per Capita<br />
            <div id="divLegend" style="position:absolute; left:5px; width:100%"></div>
        </div>
    </div>
    <div id="divMap" style="position:absolute; top:65px; left:220px; width:300px; height:300px;" class="ctrl"></div>
    <div style="position:absolute; bottom:0px; left:0px; width:100%; height:20px;" class="footer">
        <a href="http://johanneskebeck.spaces.live.com" target="_blank">My Blog</a>&nbsp;|
        <a href="http://talkingdonkey.info" target="_blank">My Office Live</a>&nbsp;|
        <a href="http://twitter.com/JohannesKebeck" target="_blank" >Twitter</a>&nbsp;|
        <a href="http://www.linkedin.com/in/johanneskebeck" target="_blank" >Linked In</a>&nbsp;|
        <a href="https://www.xing.com/profile/Johannes_Kebeck" target="_blank" >Xing</a>&nbsp;|
        <a href="http://www.facebook.com/people/Johannes-Kebeck/719916893" target="_blank" >Facebook</a>
    </div>
</body>
</html>

In our JavaScript we create window-level events that load the map when the browser loads the HTML-document and resizes it when the size of the browser-window changes. We also attach an event that fires after we zoomed the map and prevents us from zooming to a level where we don’t have any more data. Remember we rendered our tile layer only down to level 7. Finally we provide a function that allows us to add or remove the tile layer. This is the one that is triggered by the checkboxes in our HTML-document.

window.onload = GetMap;
window.onresize = Resize;

//Global Parameters
var map = null;
var mapWidth = null;
var mapHeight = null;

function GetMap()
{
    map = new VEMap('divMap');

    //Load and resize the map
    map.LoadMap(new VELatLong(0, 0), 2, 'r', false);
    Resize();

    //Map Events
    map.AttachEvent("onendzoom", EventEndZoom);
}

//Resize map and controls whenever the size of the browser window changes
//Also load the minimap
function Resize()
{
    var mapDiv = document.getElementById("divMap");
    var ctrlDiv = document.getElementById("divCtrl");
    var windowWidth = document.body.clientWidth;
    var windowHeight = document.body.clientHeight;
    mapWidth = windowWidth - 230;
    mapHeight = windowHeight  - 95;
    mapDiv.style.width = mapWidth + "px";
    mapDiv.style.height = mapHeight + "px";
    ctrlDiv.style.height = (windowHeight - 95) + "px";
    map.Resize(mapWidth, mapHeight);
    map.ShowMiniMap(mapWidth-205, 13, VEMiniMapSize.Large);
}

//Restrict Zoom-Level
function EventEndZoom(e) {
    if (e.zoomLevel > 7) {
        map.SetZoomLevel(7);
    }
}

//Tile Layer
function AddTileLayer(control, layer, maxlat, maxlon, minlat, minlon, url, minlvl, maxlvl, opac, zindex) {
    if (document.getElementById(control).checked == false) {
        map.DeleteTileLayer(layer);
        document.getElementById("divLegend").innerHTML = "";
    }
    else {
        var bounds = [new VELatLongRectangle(new VELatLong(maxlat, maxlon), new VELatLong(minlat, minlon))];
        var tileSourceSpec = new VETileSourceSpecification(layer, url);
        tileSourceSpec.Bounds = bounds;
        tileSourceSpec.MinZoomLevel = minlvl;
        tileSourceSpec.MaxZoomLevel = maxlvl;
        tileSourceSpec.Opacity = opac;
        tileSourceSpec.ZIndex = zindex;
        map.AddTileLayer(tileSourceSpec);
        if (control == "cbGDP") {
            document.getElementById("divLegend").innerHTML = "<br><hr><br><b>Million USD (2005)</b><br><table border=0 cellspacing=0 cellpadding=0><tr><td style='background-color:White;width:10px;height:10px'></td><td>&nbsp;No Data</td></tr><tr><td style='background-color:Red;width:10px;height:10px'></td><td>&nbsp;1..49,999</td></tr><tr><td style='background-color:#FF5400;width:10px;height:10px'></td><td>&nbsp;50,000..99,999</td></tr><tr><td style='background-color:#FFAA00;width:10px;height:10px'></td><td>&nbsp;100,000..499,999</td></tr><tr><td style='background-color:#FFFF00;width:10px;height:10px'></td><td>&nbsp;500,000..999,999</td></tr><tr><td style='background-color:#AAFF7E;width:10px;height:10px'></td><td>&nbsp;1,000,000..4,999,999</td></tr><tr><td style='background-color:#00FF00;width:10px;height:10px'></td><td>&nbsp;5,000,000..</td></tr></table><br><br><i>Right-click on a country to retrieve details.</i>";
        }
        else{
            document.getElementById("divLegend").innerHTML = "<br><hr><br><b>USD per Person (2005)</b><br><table border=0 cellspacing=0 cellpadding=0><tr><td style='background-color:White;width:10px;height:10px'></td><td>&nbsp;No Data</td></tr><tr><td style='background-color:Red;width:10px;height:10px'></td><td>&nbsp;1..4,999</td></tr><tr><td style='background-color:#FF5400;width:10px;height:10px'></td><td>&nbsp;5,000..9,999</td></tr><tr><td style='background-color:#FFAA00;width:10px;height:10px'></td><td>&nbsp;10,000..19,999</td></tr><tr><td style='background-color:#FFFF00;width:10px;height:10px'></td><td>&nbsp;20,000..29,999</td></tr><tr><td style='background-color:#AAFF7E;width:10px;height:10px'></td><td>&nbsp;30,000..39,999</td></tr><tr><td style='background-color:#00FF00;width:10px;height:10px'></td><td>&nbsp;40,000..</td></tr></table><br><br><i>Right-click on a country to retrieve details.</i>";
        }
    }
}

At this point we can already run our application and overlay the thematic map.

Step 3: Load the database with our Spatial and Business Data

For this task we use again Safe FME. We load all of our 7 ESRI Shape-files as source data sets in the workbench and use the “SQL Server Spatial” format as destination.

image

We also make sure that the coordinate system for the destination is set to EPSG:4326.

image

Once we have loaded the data we run the following queries from our SQL Server Management Studio to create indexes and validate the geometries:

alter table GDP alter column ID int not null;
alter table GDP add constraint PK_GDP primary key clustered (ID);
update GDP set GEOM=GEOM.MakeValid();
CREATE SPATIAL INDEX SPATIAL_GDP ON GDP(GEOM) USING GEOMETRY_GRID WITH( 
  BOUNDING_BOX  = ( xmin  = -180, ymin  = -90, xmax  = 180, ymax  = 90), 
  GRIDS  = ( LEVEL_1  = MEDIUM, LEVEL_2  = MEDIUM, LEVEL_3  = MEDIUM, LEVEL_4  = MEDIUM), 
  CELLS_PER_OBJECT  = 16);

We also create a view that contains all of the relevant business and spatial data:

CREATE VIEW V_GDP
AS
SELECT t1.NAME, 
       t1.Y_2005 AS GDP, 
       t2.Y_2005 AS Capita, 
       t3.Y_2005 AS Growth, 
       t4.Y_2005 AS Agr, 
       t5.Y_2005 AS Ind, 
       t6.Y_2005 AS Man, 
       t7.Y_2005 AS Ser, 
       t1.GEOM
FROM   GDP AS t1 INNER JOIN
       Capita AS t2 ON t1.ID = t2.ID INNER JOIN
       Growth AS t3 ON t1.ID = t3.ID INNER JOIN
       Agr AS t4 ON t1.ID = t4.ID INNER JOIN
       Ind AS t5 ON t1.ID = t5.ID INNER JOIN
       Man AS t6 ON t1.ID = t6.ID INNER JOIN
       Ser AS t7 ON t1.ID = t7.ID;

Step 4: Create the Pie Chart

For the pie chart we use the Microsoft Chart Control which is available for free download (Chart Controls for Microsoft .NET Framework 3.5, Chart Controls Add-on for Microsoft Visual Studio 2008, Documentation, Samples).

Note: this control requires the .NET Framework 3.5 and it must be installed on the web server. If you intend to use it in a hosted environment you need to ask your hosting provider to install it for you. If you just want to copy the DLL into the hosting environment you need to make sure that you have set the ASP.NET environment to full trust which is not really advisable. The control will also be shipped as part of the .NET Framework 4.0. Unfortunately the chart control doesn’t work on Azure yet. This is a known issue and a fix is on the way but there is no ETA yet.

Once you have installed the Chart Control and the Visual Studio Add-On you can create a new web form and start designing the chart.

image

We intend to pass the parameters for the values in the URL when we call the chart so we add some code to the Page_Load event that retrieves the URL-parameters, creates a series of data points and adds a tooltip with the value for each point.

Partial Class Chart
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'set culture to en-UK to avoid potential problems with decimal-separators
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

        Dim agr As Double = Math.Round(CDbl(Page.Request.Params("agr")), 1)
        Dim ind As Double = Math.Round(CDbl(Page.Request.Params("ind")), 1)
        Dim man As Double = Math.Round(CDbl(Page.Request.Params("man")), 1)
        Dim ser As Double = Math.Round(CDbl(Page.Request.Params("ser")), 1)

        Dim yValues As Double() = {agr, ind, man, ser}
        Dim xValues As String() = {"Agriculture", "Industry", "Manufacturing", "Service"}
        Chart1.Series("Default").Points.DataBindXY(xValues, yValues)
        Chart1.Series("Default")("PieLabelStyle") = "Disabled"
        Chart1.Series("Default").ToolTip = "#VALX: #VALY%"
    End Sub
End Class

Step 5: Create a callback function that retrieves the details when we right-click on a map

Here we have a couple of things to do.

  • we have to implement an event that captures the right-click in Bing Maps,
  • we have to create the AJAX-call that goes to the web service
  • we have to create the web service itself
  • we have to create a stored procedure in the database that is called by the web service, executes the spatial query for us and returns the business data for the country we clicked on

Let’s start with the JavaScript

In the function GetMap we define our new event and we also clear the default styles for the info-box that pops up when we mouse over a VEShape-object on the map. This is reasonable since we want to have more space for our chart.

function GetMap()
{
    …
    //Capture Right-click
    map.AttachEvent("onclick", RightClick);

    
    //Set Style for InfoBox
    map.ClearInfoBoxStyles();
}

Once we have cleared the default style the map will use the custom styles that we defined in our style sheet:

.customInfoBox-previewArea 
{
    width:250px;
    height:400px;
}

The function that is fired when we click on the map comes next. If the mouse-click was a right-click we determine the latitude and longitude of the location we clicked on and fire our AJAX-call GetDetails with the location as input-parameter.

function RightClick(e) {
    if (e.rightMouseButton == true) {
        //var pixel = new VEPixel();
        var loc = map.PixelToLatLong(new VEPixel(e.mapX, e.mapY));
        GetDetails(loc);
    }
}

The AJAX-call goes to our web service asynchronously passing the latitude and longitude of the clicked location as URL-parameter. Once it receives a response it executes it using the eval-function.

//Get Data from SQL Server
function GetDetails(loc) {
    //Delete existing Shapes
    map.DeleteAllShapes();
    
    //Build the URL
    var url = "./DataService.ashx?lat=" + loc.Latitude + "&lon=" + loc.Longitude;

    //Get the appropriate XMLHTTP object for the browser
    var xmlhttp = GetXmlHttp();

    //if we have a valid XMLHTTP object
    if (xmlhttp) {
        xmlhttp.open("GET", url, true); //varAsynx = true

        //set the callback
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readystate == 4) //4 is a success
            {
                //server code creates JavaScript "on the fly" for us to
                //execute using eval()
                var result = xmlhttp.responseText;
                eval(result);
            }
        }
        xmlhttp.send(null);
    }
}

//Helper function
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;
}

Before we go to the web service let’s prepare the database. We actually need to execute a spatial query in the database to determine on which country we clicked. This can be done by passing the latitude and longitude of the clicked location to a stored procedure which then creates a spatial object of type POINT from these numeric information and runs the STContains-method to determine which country covers this point. Let’s do all of that in a stored procedure. In our SQL Server Management Studio we run the following statement:

CREATE PROCEDURE GetCountryData @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, GDP, Capita, Growth, Agr, Ind, Man, Ser FROM V_GDP WHERE (GEOM.STContains(@click) = 1);

Finally we come to our web service. In this example I implement it as generic web handler and call it DataService. The data service retrieves the URL-parameters with the clicked location. It then sets up the connection to the database that we defined in the web.config and creates a SqlCommand that calls our stored procedure with parameters for the latitude and longitude. The response from the stored procedure is now parsed into a VEShape-object. In the VEShape.Description-property we have a couple of alphanumeric data and an iframe that embeds our chart control. In this iframe we use some of the detailed data from our database records to define the data points for the pie chart. Finally we append JavaScript-statements to add the VEShape-object to the map and open the info-box before we send the response back to the AJAX-call.

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

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

Public Class DataService : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        'set culture to en-UK to avoid potential problems with decimal-separators
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

        'Retrieve the URL-parameter
        Dim myLat As String = context.Request.Params("lat")
        Dim myLon As String = context.Request.Params("lon")

        'Retrieve Database Setting from web.config
        Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("GDP")
        Dim myConn As New SqlConnection(settings.ConnectionString)
        myConn.Open()

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

        'Specify the stored procedure name as the command text
        cmd.CommandText = "GetCountryData"
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        
        'Read the DataReader to process each row
        Dim myPin As String = ""
        While reader.Read()
            myPin = "var shape=new VEShape(VEShapeType.Pushpin, new VELatLong(" + myLat + ", " + myLon + "));" + _
                "shape.SetCustomIcon('./IMG/blue.png');" + _
                "shape.SetTitle('" + reader.Item(0) + "');" + _
                "shape.SetDescription('GDP (Mio USD): " + CDbl(reader.Item(1)).ToString("N1", CultureInfo.InvariantCulture) + _
                "<br>GDP/Capita (USD): " + CDbl(reader.Item(2)).ToString("N1", CultureInfo.InvariantCulture) + _
                "<br>Growth Rate (%): " + CDbl(reader.Item(3)).ToString("N1", CultureInfo.InvariantCulture) + _
                "<br><iframe frameborder=0 width='250px' height='300px' scrolling='no' src='./Chart.aspx?agr=" + _
                reader.Item(4).ToString + "&ind=" + _
                reader.Item(5).ToString + "&man=" + _
                reader.Item(6).ToString + "&ser=" + _
                reader.Item(7).ToString + "'></iframe><br><i>Note: if the chart area is empty there are no detailed information for this country.</i>');" + _
                "map.AddShape(shape);" + _
                "map.ShowInfoBox(shape);"
        End While
        reader.Close()
        myConn.Close()
        
        context.Response.Write(myPin)
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

That’s it. You will find the complete sample code including the database the SQOL scripts and the FME workspaces here:

Advertisements
This entry was posted in Bing Maps. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s