Hannes's Virtual Earth Blog

OnTerra is a Microsoft Partner specialized on tailored Bing Maps solutions and focussing on but not limited to tracking and fleet management. Recently they launched a free beta version of a Stop Optimization Service. The service allows you to send an unlimited list of stops for your route and receive a string with the order of the stops optimized for the shortest driving distance. Let’s have a quick look at how it works:

Bing Maps supports out-of-the-box Multi-Waypoint Routing for up to 25 stops through the method VEMap.GetDirections. However, the routing algorithm processes the stops always in the order in which they appear in the array of locations. If we want to start for example a trip in the Microsoft Office in Reading and want to visit Swindon, Oxford, Maidenhead and Newbury before we return to the Microsoft office we have to know in which order we want to visit these cities. If we just send the list in the order mentioned above it will guide us from one location to the next in exactly this order and come up with a route that is 185 miles long and takes about 3 hours and 20 minutes of drive time.

The free OnTerra Stop Optimization Service figures out in which order we should drive for the shortest distance. In the example above it will suggest that we go to Maidenhead first, then Oxford, Swindon and Newbury. This will save us 45 miles and about 40 minutes of drive time.

That’s not bad at all but if you would like to use it for example as a dispatcher in a fleet management application you also need to consider the times when you can make a pickup or a delivery, you may want to optimize for shortest time rather than shortest distance or you may need to consider height and weight restrictions that apply to your trucks. This is not part of the free service but in addition to the free stop optimization, OnTerra also offers such advanced features for a fee. If you are interested in this type of advanced service contact routeopt@onterrasys.com for more details.

To use the free stop optimization service you will need to register and apply for a token. It requires 3 parameters:

1. the locations which we want to optimize as a string, The string contains a label for the location and the latitude and longitude separated by a comma. Multiple locations are separated by a ‘#’.
2. a Boolean parameter that indicates weather we do a roundtrip or a one-way trip
3. our token

You see that we need to geocode the locations before we send them to the stop optimization service. In the sample application above I use the Bing Maps AJAX control and use the callback function for the VEMap.Find-method to concatenate a string with the locations as expected by the stop optimization service, e.g. “txtStop1,51.461179,-0.925943#txtStop2,51.561765,-1.781815#txtStop3,51.522375,-0.727256#txtStop4,51.405876,-1.325891#txtStop5,51.756205,-1.259490”.

Now here is one thing so consider: The optimization service splits the locations-string whenever it finds the character ‘#’. Unfortunately there appears to be a bug(?) which doesn’t process the string correctly when you work with the full number of decimal digits that comes back from the Bing Maps geocoder. In order to work around this bug(?) we can truncate the number of decimal digits to 6. This does actually not have a noticeable impact on the precision of the result but solves our problem.

Once we have our last location we call a JavaScript-function StopOpt which actually creates an AJAX-call

//Build String for Route Optimization
function AppendLocations(layer, resultsArray, places, hasMore, veErrorMessage) {
    i = i + 1;
    if (locations.length > 0) {
        locations=locations+"#"
    }
    locations = locations + "txtStop" + i + "," 
      + places[0].LatLong.Latitude.toFixed(6) + ","
      + places[0].LatLong.Longitude.toFixed(6);
    if (i == numStops) {
        StopOpt();
    }
}

The AJAX-call goes to a web handler which will call the stop optimization service and hands over the locations-string as well as a parameter that indicates if we’re doing a roundtrip or a one-way-trip. The optimized order of the result is received as a string and we process it a bit before we call the VEMap.GetDirections method.

function StopOpt() {
    //Build URL to call the server
    var url = "./06-StopOpt.ashx?";
    url += "locations=" + locations;

    if (document.getElementById("cbRoundtrip").checked == true) {
        url += "&roundtrip=true"
    }
    else {
        url += "&roundtrip=false"
    }

    //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
            {
                //web service returns the optimized order of the stops
                var result = xmlhttp.responseText
                var stopArray = result.split(" >> ");
                var stops = new Array();
                var order = "Order (Optimized):<br>";
                for (var i = 0; i < stopArray.length; ++i) {
                    order = order + document.getElementById(stopArray[i]).value + "<br>";
                    stops.push(document.getElementById(stopArray[i]).value);
                }
                if (document.getElementById("cbRoundtrip").checked == true) {
                    order = order + document.getElementById("txtStop1").value;
                    stops.push(document.getElementById("txtStop1").value)
                }

                var options = new VERouteOptions;
                options.RouteCallback = DistTime;

                document.getElementById("pOrder").innerHTML = order;

                map.GetDirections(stops, options);
            }
        }
        xmlhttp.send(null);
    }
}

Finally, here is our web handler that we have been calling with our AJAX-call and which in turn calls the OnTerra stop optimization service:

Imports System.Web
Imports System.Web.Services
Imports BM_Azure_01_WebRole.OnTerra

Public Class _06_StopOpt
    Implements System.Web.IHttpHandler

    Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        'Get the URL-Parameters
        Dim locations As String = context.Request.Params("locations")
        Dim roundTrip As Boolean = CBool(context.Request.Params("roundtrip"))

        Dim token As String = "YOUR TOKEN"

        Dim svcOT As New OnTerra.OnTerraStopOptClient("basicEndPoint")
        Dim output As String
        output = svcOT.GetStopOpt(locations, roundTrip, token)

        context.Response.ContentType = "text/plain"
        context.Response.Write(output)
    End Sub

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

End Class

Step 4: Build the basic Bing Maps Application with the Tile Layer

We start by creating a new Cloud Service Solution. A Web Cloud Service will do for this purpose.

For our development and debugging we will use the development fabric but we will not use the development storage (I actually didn’t manage to get the development table storage to work with binary data types). Hence we can disable the start of development storage services in the properties of our Azure project.

Next we add a new Silverlight application to our WebRole-Project:

Let’s also create a test page and make sure that Silverlight debugging is enabled:

To this project we add a our Bing Maps Silverlight Control as additional reference. The DLL is not in the Global Assembly Cache so you need to browse for it. If you installed the Bing Maps Silverlight Control in the default location you’ll find the DLL in the folder “C:\Program Files\Microsoft Virtual Earth Silverlight Map Control\CTP\Libraries”.

In the Page.xaml we add now the reference to our map control:

<UserControl x:Class="_01_SL_Charts.Page"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
    xmlns:m="clr-namespace:Microsoft.VirtualEarth.MapControl;assembly=Microsoft.VirtualEarth.MapControl" >

…the map itself…

<m:Map HorizontalAlignment="Stretch" VerticalAlignment="Stretch" x:Name="MyMap"  
       Center="0, 0"
       ZoomLevel="2"
       Mode="Road" />

….and other design components as you need it such as checkboxes that allow us to switch the tile layers on and off.

<StackPanel>
    <CheckBox x:Name="cbGDP" Click="cbGDP_Click" >
        <TextBlock Text="GDP"></TextBlock>
    </CheckBox>
    <CheckBox x:Name="cbCapita" Click="cbCapita_Click" >
        <TextBlock Text="GDP per Capita"></TextBlock>
    </CheckBox>
</StackPanel>

At the end of our user control we can also import other user controls that we may want to use as pop-ups. In this example we use user-controls as pop-ups for example to explain the colour codes. Let’s assume we have already created 2 new Silverlight user controls in our Silverlight project. Both of them have show- and close-functions in the code behind. We can import them now for use in our Page.xaml:

<mychart:LegendGDP x:Name="legendGDPPopup" Visibility="Collapsed" />
<mychart:LegendCapita x:Name="legendCapitaPopup" Visibility="Collapsed" />

In my sample I have also added a mini map and buttons to toggle this mini map as well as one to toggle fullscreen mode. You will find the complete code in the sample code at the end of this blog post.

In the extract of the XAML above you see that we define the initial centre-point, zoom-level and map-style directly in the XAML. You also see that we have prepared for 2 functions that will fire when we check or uncheck the checkboxes. So let’s have a look at the code behind in the Page.xaml.vb-file. You will see that we have attached a handler that captures when the target-view of our map changes. That actually happens whenever we pan or zoom the map. when this event occurs we check the target zoom-level and since we rendered the tile layer only for levels 1-7 we make sure that we can’t zoom any closer than that. Next we create functions that overlay our own custom tile layer when we check the checkbox and hides them again when we uncheck it. We also show and hide the legend as part of these functions.

Imports Microsoft.VirtualEarth.MapControl

Partial Public Class Page
    Inherits UserControl

    'Tile Layer
   Public gdpLayer As MapTileLayer
    Public capitaLayer As MapTileLayer

    Public Sub New()
        InitializeComponent()

        ' Set up Main Map Events
       AddHandler MyMap.TargetViewChanged, AddressOf MyMap_TargetViewChanged
    End Sub

    Private Sub MyMap_TargetViewChanged(ByVal sender As Object, ByVal e As Microsoft.VirtualEarth.MapControl.MapEventArgs)
        If MyMap.TargetView.ZoomLevel > 7 Then
           MyMap.SetView(MyMap.Center, 7)
        End If
    End Sub

    Private Sub cbGDP_Click(ByVal sender AsSystem.Object, ByVal e As System.Windows.RoutedEventArgs)
        If cbGDP.IsChecked = True Then
           ' The bounding rectangle that the tile overlay can be placed within.
           Dim boundingRect As New LocationRect(New Location(90, -180), New Location(-90, 180))

            ' Creates a new map layer to add the tile overlay to.
           gdpLayer = New MapTileLayer()

            ' The source of the overlay.
           Dim tileSource As New LocationRectTileSource()
            tileSource.UriFormat = "http://hannesvestorage.blob.core.windows.net/vetiles/GDP/{0}.png"
           ' The zoom range that the tile overlay is visibile within
           tileSource.ZoomRange = New Range(Of Double)(1, 7)
            ' The bounding rectangle area that the tile overaly is valid in.
           tileSource.BoundingRectangle = boundingRect

            gdpLayer.TileSources.Add(tileSource)

            gdpLayer.Opacity = 0.7
            MyMap.Children.Add(gdpLayer)
            legendGDPPopup.Show()
        Else
           MyMap.Children.Remove(gdpLayer)
            legendGDPPopup.Close()
        End If
    End Sub

End Class

Well that’s it for the tile layer. At this point we can run the project and see our statistical information as a thematic Bing Map using the Silverlight control.

Step 5: Implement spatial queries from our Bing Maps application to the Windows Azure Table Storage

First we add a few references to this WebRole project: We need the same StorageClient.dll we used in Step 3 as well as the System.Data.Services.Client from the GAC to access the Windows Azure Table Storage but we also need to Microsoft.SqlServer.Types for the spatial queries. You will have the latter already in your GAC if you have SQL Server 2008 installed otherwise download and install either SQL Server 2008 or the SQL Server CLR Types from the Feature Pack. Make sure both the StorageClient and the SqlServer.Types are copied locally so that they will be packaged and uploaded to Windows Azure.

Now here comes the first tricky bit. The spatial libraries in SQL Server consists actually of an managed and an unmanaged part. We need both of them but with the approach mentioned above we only get the managed piece. Since the other part is unmanaged we could simply copy the SqlServerSpatial.dll from C:\Windows\System32 to the bin-directory of our WebRole, e.g. C:\Users\jkebeck\Documents\Visual Studio 2008\Projects\BM-Azure-01\BM-Azure-01_WebRole\bin.

However keep in mind that Windows Azure runs on 64-bit processors. If your system is like my laptop on 32-bit you need to download the 64-bit version of the SQL Server CLR Types from the feature pack extract the *.msi using a command such as

msiexec /a SQLSysClrTypes_64bit.msi /qb TARGETDIR="C:\MyFolder"

and copy the SqlServerSpatial.dll from there.

Well, by default Windows Azure does not allow you to run native code but since the latest update you can now enable this feature. In order to do so open the ServiceDefinition.csdef in the Azure project and set the enableNativeCodeExecution to true.

Next we need to consider that at least during the development we do cross-domain calls from our Silverlight application in the local development fabric to the Windows Azure Table Storage. Hence we’ll need a crossdomain.xml file. So let’s just create a new xml-file with that name and enter the following:

<access-policy>
  <cross-domain-access>
    <policy>
      <allow-from http-request-headers="*">
        >
        <domain uri="*"/>
      </allow-from>
      <grant-to>
        <resource path="/" include-subpaths="true"/>
      </grant-to>
    </policy>
  </cross-domain-access>
</access-policy>

Now we add the same class GDP.vb that we created in Step 3 as an existing item to our web role. Remember, we need this class to describe the table and entities in our Windows Azure Table Storage.

In our web.config we add next the section for our Windows Azure credentials

<appSettings>
  <add key="AccountName" value="YOUR ACCOUNT NAME" />
  <add key="AccountSharedKey" value="YOUR SHARED KEY" />
  <add key="TableStorageEndpoint" value="http://table.core.windows.net" />
</appSettings>

Finally we get to look at some code again. To connect to the Windows Azure Table Storage from our Silverlight application we create a new Silverlight-enabled WCF Service in our WebRole-project.

In this WCF service we will receive the latitude and longitude of the location we clicked on as input parameters and then construct a geometry of type POINT from it. Now we loop through all the records in our Windows Azure table, retrieve the byte array describing our geometry and convert it into a GEOMETRY data type. Once we have this we can determine if the point that we clicked on is in this particular geometry. If so, we return the entity to the function that called the service.

Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Types

<ServiceContract(Namespace:="DataService")> _
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class DataService

    <OperationContract()> _
    Public Function GetCountry(ByVal myLat As String, ByVal myLon As String) As GDPRecord
        'set culture to en-UK to avoid potential problems with decimal-separators
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

        'Build geometry
        Dim myWKT As New SqlChars(New SqlString("POINT(" + myLon + " " + myLat + ")"))
        Dim myPoint As SqlGeometry
        myPoint = SqlGeometry.STGeomFromText(myWKT, 4326)

        'Query Azure table and compare geometries
        Dim myTable As New GDP
        For Each GDPRecord In myTable.GDPTable
            Dim b As Byte() = GDPRecord.Geom
            Dim g As SqlGeometry
            g = SqlGeometry.STGeomFromWKB(New SqlBytes(b), 4326)
            If g.STContains(myPoint) = 1 Then
                Return GDPRecord
                Exit For
            End If
        Next
    End Function

End Class

Once we have this service we can build the WebRole-project and add a Service Reference to our Silverlight project.

This will not only create the reference but also a ServiceReferences.ClientConfig file. In this file you have to remove the tags for the transport-mode within the security tags so that the file reads similar to:

<configuration>
    <system.serviceModel>
        <bindings>
            <basicHttpBinding>
                <binding name="BasicHttpBinding_DataService" maxBufferSize="2147483647"
                    maxReceivedMessageSize="2147483647">
                    <security mode="None"/>
                </binding>
            </basicHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://dummy/DataService.svc" binding="basicHttpBinding"
                bindingConfiguration="BasicHttpBinding_DataService" contract="DataServiceReference.DataService"
                name="BasicHttpBinding_DataService" />
        </client>
    </system.serviceModel>
</configuration>

The address of the endpoint doesn’t actually matter. It will be different in the development fabric, different in the Windows Azure staging environment and different again in the Windows Azure production environment. Hence we are going to write the URL later in our code.

Step 6: Add a chart using the Microsoft Silverlight Toolkit

First we need to add the assembly as reference to our Silverlight project. If you installed the Silverlight Toolkit in the default directory you’ll find it in the path “C:\Program Files\Microsoft SDKs\Silverlight\v2.0\Toolkit\March 2009\Libraries\System.Windows.Controls.DataVisualization.Toolkit.dll”. Again: make sure that you copy this assembly locally.

Now we create a new Silverlight user control Chart.xaml in our Silverlight project and we will find the Chart in your Visual Studio Toolbox

Let’s prepare the user control to be used as a popup from our main user control Page.xaml and add a couple of more controls to host the details:

<Grid x:Name="LayoutRoot" Width="245" >
    <Popup x:Name="popChart" VerticalAlignment="Stretch" Width="245" Margin="0,0,0,0" HorizontalAlignment="Stretch">
        <Border Width="245" BorderThickness="3,3,3,3" CornerRadius="10,10,10,10" 
                BorderBrush="#FFFFFFFF" Background="#FFFFFFFF">
            <StackPanel Orientation="Vertical" Margin="10,10,10,10" Canvas.ZIndex="0" Width="220">
                <TextBlock x:Name="myName" Margin="5,0,0,0" ></TextBlock>
                <StackPanel Orientation="Horizontal" Margin="5,0,0,0">
                    <TextBlock Text="GDP (Mio USD): "></TextBlock>
                    <TextBlock x:Name="myTotal" VerticalAlignment="Top"></TextBlock>
                </StackPanel>
                <StackPanel Orientation="Horizontal" Margin="5,0,0,0">
                    <TextBlock Text="GDP/Capita (USD): "></TextBlock>
                    <TextBlock x:Name="myCapita" VerticalAlignment="Top"></TextBlock>
                </StackPanel>
                <StackPanel Orientation="Horizontal" Margin="5,0,0,0">
                    <TextBlock Text="Growth Rate (%): "></TextBlock>
                    <TextBlock x:Name="myGrowth" VerticalAlignment="Top"></TextBlock>
                </StackPanel>
                <chart:Chart Height="310" Title="Percentage Added By" x:Name="MyPieChart" Width="220">
                    <chart:Chart.Background>
                        <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                            <GradientStop Color="#FFFCAC34"/>
                            <GradientStop Color="#FFFFFFFF" Offset="1"/>
                        </LinearGradientBrush>
                    </chart:Chart.Background>
                    <chart:PieSeries 
                        IndependentValueBinding="{Binding Path=myName}"
                        DependentValueBinding="{Binding Path=myValue}" 
                        LegendItemStyle="{StaticResource MyLegendItemStyle1}"
                        StylePalette="{StaticResource MyStylePalette}"/>
                </chart:Chart>
                <TextBlock Margin="5,0,0,0" VerticalAlignment="Top" Text="Note: if the chart area is empty…"/>
            </StackPanel>
        </Border>
    </Popup>
</Grid>

If you want to make some major changes in the style of the control, modify the tooltip, etc it is a bit painful but then the beauty of this type of control is that you aren’t being boxed and can do it after all. A good guide on advanced styling for the chart control of the Silverlight Toolkit using Expression Blend is here.

In the code behind we prepare functions to open and close the popup. In the opening function we want to be able to receive a couple of parameters and use them to display various information and add data points to the chart.

Imports System.Windows.Controls.DataVisualization.Charting
Imports System.Globalization

Partial Public Class Chart
    Inherits UserControl

    Public Sub New()
        InitializeComponent()
    End Sub

    Public Sub Close()
        popChart.IsOpen = False
        Me.Visibility = Windows.Visibility.Collapsed
    End Sub

    Public Sub Show(ByVal country As String, ByVal total As Double, ByVal capita As Double, ByVal growth As Double, ByVal agr As Double, ByVal ind As Double, ByVal man As Double, ByVal ser As Double)
        myName.Text = country
        myTotal.Text = CDbl(total).ToString("N1", CultureInfo.InvariantCulture)
        myCapita.Text = CDbl(capita).ToString("N1", CultureInfo.InvariantCulture)
        myGrowth.Text = CDbl(growth).ToString("N1", CultureInfo.InvariantCulture)
        Dim ps As PieSeries = MyPieChart.Series(0)
        Dim myData As New List(Of myDataClass)
        myData.Add(New myDataClass("Agriculture", agr))
        myData.Add(New myDataClass("Industry", ind))
        myData.Add(New myDataClass("Manufacturing", man))
        myData.Add(New myDataClass("Service", ser))
        ps.ItemsSource = myData
        popChart.IsOpen = True
        Me.Visibility = Windows.Visibility.Visible
    End Sub
End Class

Public Class myDataClass
    Private _myName As String
    Public Property myName() As String
        Get
            Return _myName
        End Get
        Set(ByVal value As String)
            _myName = value
        End Set
    End Property

    Private _myValue As Integer
    Public Property myValue() As Integer
        Get
            Return _myValue
        End Get
        Set(ByVal value As Integer)
            _myValue = value
        End Set
    End Property

    Public Sub New(ByVal _myName As String, ByVal _myValue As Integer)
        myName = _myName
        myValue = _myValue
    End Sub
End Class

All right, now let’s string it together. In our Page.xaml we reference this new popup for the chart

…
        <mychart:Chart x:Name="chartPopup" Visibility="Collapsed" />
        <mychart:LegendGDP x:Name="legendGDPPopup" Visibility="Collapsed" />
        <mychart:LegendCapita x:Name="legendCapitaPopup" Visibility="Collapsed" />
        <mychart:PleaseWait x:Name="waitPopup" Visibility="Collapsed"/>
    </Grid>
</UserControl>

In the code behind, i.e. Page.xaml.vb we want to introduce a feature that allows us to double click on a location in the map and then:

1. add a point to the map that marks the clicked location
2. determine the location we clicked on and call the web service we created in step 5
3. The response will be used as parameters when we open the chart and hand over the details

First we declare a new MapLayer in our class

'Chart Layer
Public chartLayer As MapLayer

In the Public Sub New we add a new handler that takes care of a double-click

AddHandler MyMap.MouseDoubleClick, AddressOf MyMap_MouseDoubleClick

The handler will first add the layer to the map if it doesn’t already exist and then add an icon on the clicked position. Then we dynamically build the URL of the endpoint for our web service and call it asynchronously with the latitude and longitude of the clicked location as parameters.

Private Sub MyMap_MouseDoubleClick(ByVal sender As Object, ByVal e As MapMouseEventArgs)
    'Add Layer for chart points
    If Not MyMap.Children.Contains(chartLayer) Then
        chartLayer = New MapLayer
        MyMap.Children.Add(chartLayer)
    End If

    chartLayer.Children.Clear()
    chartPopup.Close()

    Dim loc As Location = MyMap.ViewportPointToLocation(e.ViewportPoint)

    Dim image As New Image()
    image.Source = New BitmapImage(New Uri("/IMG/blue.png", UriKind.Relative))
    image.Stretch = Stretch.None
    Dim location As New Location(loc.Latitude.ToString, loc.Longitude.ToString)
    Dim position As PositionMethod = PositionMethod.Center
    chartLayer.AddChild(image, location, position)

    Dim wsURL As String = "http://" + HtmlPage.Document.DocumentUri.Host + _
      ":" + HtmlPage.Document.DocumentUri.Port.ToString + "/DataService.svc"
    Dim svc As New DataServiceClient()
    svc.Endpoint.Address = New ServiceModel.EndpointAddress(wsURL)
    AddHandler svc.GetCountryCompleted, AddressOf svc_GetCountryCompleted
    svc.GetCountryAsync(loc.Latitude.ToString, loc.Longitude.ToString)

    e.Handled = True
End Sub

When we receive the response we hand the details over to the Chart user control and open it.

Private Sub svc_GetCountryCompleted(ByVal sender As Object, ByVal e As GetCountryCompletedEventArgs)
    chartPopup.Show(e.Result.Name, e.Result.Total, e.Result.Capita, _
      e.Result.Growth, e.Result.Agri, e.Result.Ind, e.Result.Manu, e.Result.Serv)
End Sub

And finally we’re done. We can publish our work to Windows Azure from the context menu of the Azure project.

This is how it looks like

You will find the sample live on Windows Azure here and the source code is here

(the source code has actually a couple of more samples from this site)

Introduction

I have previously blogged about Bing Maps and Windows Azure (Part 1: Introduction, Part 2: Accessing Blob Storage, Part 3: Accessing Table Storage) and since we brought together a mapping application with our operating system for the cloud this is already sort of spatial-enabling but now I want to go a step further. Now I would also like to use spatial data types and spatial functions as we have them in SQL Server 2008. That may sound a bit ambitious but in their infinite wisdom the SQL Server Spatial team has made the spatial data types and spatial functions available for external use in a separate library that comes with SQL Server 2008 but also separately with the SQL Server 2008 Feature Pack. To be more precise you find them in the package “SQL Server System CLR Types”. Well, that’s almost all I need and with a little tweaking I can use this library in a way that I can leverage the spatial data types and spatial functions within Windows Azure.

For this walk-through I’m going to keep it simple. I will store a couple of country-boundaries in Well Known Binary (WKB) format together with business data in the Windows Azure Table Storage. The application will allow me to click on a a country in Bing Maps and retrieve the detailed information for the selected country similar to my previous blog post Data Visualization with Bing Maps. You might wonder why I don’t just you use the reverse-geocoder in Bing Maps or MapPoint Web Service to determine the country that contains the location I clicked on. Indeed you have a valid point. However, it is not very simple to retrieve the country through the reverse-geocoder in Bing Maps. In MapPoint Web Service it is much more straight forward since you can filter the response from the SOAP web service and get only the entities of type “Sovereign” which contain the country-name. From there I could use a simple WHERE-clause to look up the business data. Unfortunately it is not always that simple. In this example I use several data sets around the Gross Domestic Product and the above mentioned approach works well for countries like Germany but if we look for example at France I want to be able to distinguish between mainland France and its overseas dependencies. In that case it will be much simpler to use a spatial query and determine the geography that contains the location.

After all a spatial-enabled Windows Azure will allow me to use the same approach not only on a country level but basically for any geography you can think off (e.g. super output areas, etc) and more important I cannot only use it for simple queries like “in which area is this point” but also for “find points of interest along a route” or “find hotels within 2 miles of Hadrian’s Wall”. Even territory management type queries where I want to aggregate geographies for example into sales territories are possible then.

In the previous blog post on Data Visualization with Bing Maps we used the Bing Maps AJAX Control and the Microsoft Chart Control. 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. So I chose to use the charts in the Microsoft Silverlight Toolkit and because I’m already at Silverlight I’m also using the Bing Maps Silverlight Control. The complete list of tools I used is:

• Bing Maps Silverlight Control
• Microsoft Silverlight Toolkit
• Expression Blend
• Windows Azure 
• SQL Server 2008
• Safe FME
• Visual Studio 2008
• Windows Azure SDK
• Windows Azure Tools for Visual Studio 2008
• Azure Storage Explorer
• SpaceBlock

We will use the same statistical information around the Gross Domestic Product (GDP) from the GEO Data Portal of the Unites Nations Environment Programme (UNEP) as in the previous blog post and go through the following steps

1. Create a Bing Maps Tile Layer for the colour coding of the countries using Safe FME  and upload it to the Windows Azure Blog Storage
2. Load vector data into SQL Server 2008 using Safe FME
3. Migrate the spatial data from our local SQL Server 2008 to the Windows Azure Table Storage
4. Build our basic Bing Maps application which overlays the Tile Layer
5. Implement spatial queries from our Bing Maps application to the Windows Azure Table Storage
6. Add a chart using the Microsoft Silverlight Toolkit

As usual you will find the sample code at the end of this blog for download.

Step 1: Create the Bing Maps Tile Layer and Upload to Windows Azure

Since I already explained the generation of the tile layer using Safe FME in the previous blog post we can keep this short and go straight to the upload into the Windows Azure Blog Storage. I use Spaceblock for this which is available for free download from Codeplex.

Step 2: Load Vector Data into SQL Server 2008

So far we have created a tile layer – basically a set of images – that we can overlay on Bing Maps. This will allow us to create a quite visual colour-coded map but obviously we will loose the meta data and the granularity of the information will depend on the number of colours we use. For example Germany, France, Italy and the UK are all mapped to the the same colour. In our example we want to be able to click on a country and retrieve the detailed information. To do that we will use the original vector data and spatial relationship queries as provided by the spatial functions in SQL Server 2008. Since we will ultimately not deploy the data on SQL Server 2008 but on Windows Azure we will have a couple of constraints. One is that SQL Server 2008 also provides spatial indexing and unfortunately we can’t use that in Windows Azure. More important though is that the Windows Azure Table Storage doesn’t support the SQL Server 2008 spatial data types natively so we have to work around it using the binary data type and that one only supports an array of bytes with a size of up to 64 kB.

Well, the bad news is that a geometry for a country like Canada is much bigger than that but fortunately we can use Safe FME to generalize the geometries. We could actually do something similar with the Reduce-method in SQL Server 2008 as well but FME supports more algorithms and - most important - preserves shared boundaries between countries. Below you find the FME workflow…

…and the settings for the Generalizer I chose:

We do the same loading procedure for all data sets that we have downloaded previously.

When you query the data in SQL Server 2008 using a spatial function such as…

select geom.STArea() from GDP_Capita;

…you will probably get an error message because the generalized data set has self-intersections which lead to invalid geometries.

To validate the data execute the following SQL-statement:

update GDP set GEOM=GEOM.MakeValid();

Finally let’s create a view which joins all the statistical information and the spatial data:

CREATE VIEW V_GDP
AS
SELECT t1.NAME, 
       t1.Y_2005 AS GDP, 
       t2.Y_2005 AS GDP_Capita, 
       t3.Y_2005 AS GDP_Growth_Rate, 
       t4.Y_2005 AS GDP_Agri_Add, 
       t5.Y_2005 AS GDP_Ind_Add, 
       t6.Y_2005 AS GDP_Manu_Add, 
       t7.Y_2005 AS GDP_Service_Add, 
       t8.Y_2005 AS GDP_Trade_Add, 
       t1.GEOM
FROM   GDP AS t1 INNER JOIN
       GDP_Capita AS t2 ON t1.ID = t2.ID INNER JOIN
       GDP_Growth_Rate AS t3 ON t1.ID = t3.ID INNER JOIN
       GDP_Agri_Add AS t4 ON t1.ID = t4.ID INNER JOIN
       GDP_Ind_Add AS t5 ON t1.ID = t5.ID INNER JOIN
       GDP_Manu_Add AS t6 ON t1.ID = t6.ID INNER JOIN
       GDP_Service_Add AS t7 ON t1.ID = t7.ID INNER JOIN
       GDP_Trade_Add AS t8 ON t1.ID = t8.ID
ORDER BY t1.NAME

Step 3: Migrate Data from SQL Server 2008 to Windows Azure

For this step we create a small WinForm-application that reads data from our SQL Server and inserts the records in a Windows Azure table. In order to access the Windows Azure Storage we use the StorageClient Library from the Windows Azure Samples. After we installed the Windows Azure SDK we will find these samples in the folder C:\Program Files\Windows Azure SDK\v1.0. So let’s compile the samples as described in the readme.txt, add the StorageClient.dll as reference to our project and double-check in the properties that “copy local” is set to true:

If we want to use the StorageClient.dll we need to define table objects and entities in a class. Hence we create a new class GDP.vb to define the entities. We will use the same class later in our web application. Note that we define the property that will hold our spatial data in Well Known Binary (WKB) format as byte array.

Imports Microsoft.Samples.ServiceHosting.StorageClient
Imports System.Data.Services.Client

Public Class GDPRecord
    Inherits TableStorageEntity

    Private _Name As String
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

    Private _Total As Double
    Public Property Total() As Double
        Get
            Return _Total
        End Get
        Set(ByVal value As Double)
            _Total = value
        End Set
    End Property

    Private _Capita As Double
    Public Property Capita() As Double
        Get
            Return _Capita
        End Get
        Set(ByVal value As Double)
            _Capita = value
        End Set
    End Property

    Private _Growth As Double
    Public Property Growth() As Double
        Get
            Return _Growth
        End Get
        Set(ByVal value As Double)
            _Growth = value
        End Set
    End Property

    Private _Agri As Double
    Public Property Agri() As Double
        Get
            Return _Agri
        End Get
        Set(ByVal value As Double)
            _Agri = value
        End Set
    End Property

    Private _Ind As Double
    Public Property Ind() As Double
        Get
            Return _Ind
        End Get
        Set(ByVal value As Double)
            _Ind = value
        End Set
    End Property

    Private _Manu As Double
    Public Property Manu() As Double
        Get
            Return _Manu
        End Get
        Set(ByVal value As Double)
            _Manu = value
        End Set
    End Property

    Private _Serv As Double
    Public Property Serv() As Double
        Get
            Return _Serv
        End Get
        Set(ByVal value As Double)
            _Serv = value
        End Set
    End Property

    Private _Geom As Byte()
    Public Property Geom() As Byte()
        Get
            Return _Geom
        End Get
        Set(ByVal value As Byte())
            _Geom = value
        End Set
    End Property

    Public Sub New(ByVal _Name As String, ByVal _Total As Double, ByVal _Capita As Double, _
                   ByVal _Growth As Double, ByVal _Agri As Double, ByVal _Ind As Double, _
                   ByVal _Manu As Double, ByVal _Serv As Double, ByVal _Geom As Byte())
        MyBase.New("Country", String.Format("{0:d10}", DateTime.UtcNow.Ticks))
        Name = _Name
        Total = _Total
        Capita = _Capita
        Growth = _Growth
        Agri = _Agri
        Ind = _Ind
        Manu = _Manu
        Serv = _Serv
        Geom = _Geom
    End Sub

    Public Sub New()
    End Sub
End Class

Public Class GDP
    Inherits TableStorageDataServiceContext

    Public Sub New()
        MyBase.New(StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration())
    End Sub

    Public ReadOnly Property GDPTable() As DataServiceQuery(Of GDPRecord)
        Get
            Return CreateQuery(Of GDPRecord)("GDPTable")
        End Get
    End Property
End Class

Next we create a app.config that will hold our credentials for the Windows Azure Storage

  <appSettings>
    <add key="AccountName" value="Your Account Name"/>
    <add key="AccountSharedKey" value="Your Shared Key”
    <add key="TableStorageEndpoint" value="http://table.core.windows.net"/>
  </appSettings>

In our WinForm we create just 1 button. When we load the form we try to create a new table GDP in our Windows Azure Table Storage. If this table already exists the command will do nothing. When we click the button we will read through our database view, retrieve the alphanumeric data in their normal format and the spatial data as Well Known Binary (WKB) and add each record to our Windows Azure Table

Private Sub btnStartTransfer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
  Handles btnStartTransfer.Click
    BackgroundWorker1.RunWorkerAsync()
End Sub

Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) _
  Handles BackgroundWorker1.DoWork
    Dim myConn As New SqlConnection("Data Source=jkebeck1; Initial Catalog=Statistics; Integrated Security=SSPI;")
    Dim myQ1 As String = "SELECT COUNT(*) FROM V_GDP"
    Dim myC1 As New SqlCommand(myQ1, myConn)
    Dim numRecords As Integer = 0
    myConn.Open()
    numRecords = myC1.ExecuteScalar()

    Dim i As Integer = 0
    Dim myQ2 As String = "SELECT NAME, GDP, GDP_Capita, GDP_Growth_Rate, GDP_Agri_Add, GDP_Ind_Add, GDP_Manu_Add," + _
      "GDP_Service_Add, GEOM.STAsBinary() FROM V_GDP"
    Dim myC2 As New SqlCommand(myQ2, myConn)
    Dim myReader As SqlDataReader = myC2.ExecuteReader()
    While myReader.Read
        Dim svc = New GDP()
        svc.AddObject("GDPTable", New GDPRecord(myReader(0), myReader(1), myReader(2), myReader(3), myReader(4), _
          myReader(5), myReader(6), myReader(7), myReader(8)))
        svc.SaveChanges()
        i = i + 1
        lblStatus.Text = "Transfer Record " + i.ToString + " of " + numRecords.ToString
    End While

    myReader.Close()
    myConn.Close()

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim tables = TableStorage.Create(StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration())
    tables.TryCreateTable("GDPTable")
End Sub

The source code for this little tool is available here

Once we completed the upload we may want to use a tool such as the Azure Storage Explorer to verify everything went well.

Following the announcement of the a UK-wide trial of low carbon and electric cars TrekWireless has created a map of electric car charging points (juice points) in Westminster. The infobox shows the location of electric car charging points in 360 degree POSIPIX images and through their SMS service a mobile user can request a geo-tagged image be sent to his navigation device.

If you haven’t checked it out yet, you need to have a look at Worldmaps. Worldmaps determines through an IP-address lookup where the visitors of your site are located and generates various reports. For starters you can integrate an image in your website…

…but you can also get a Bing Maps application and more detailed reports through the homepage and to put it with their own words: “While it's fun to see where your visitors are coming from, it's more fun to participate in the social. See how your stats rank against your friends, and see who can achieve the highest world domination.” I’m only on rank 97 but then: I only do it for 12 days now and I’ll work on it :-)