Publishing to Windows Azure

In the previous 2 parts we have created a Photo-Map with the Bing Maps Silverlight Control and implemented server-side clustering. Now we are going to publish this application to Windows Azure.

Let’s start with the photos. I use SpaceBlock which is available on CodePlex to upload the photos into my Windows Azure Blob Storage.

In this example we don’t want to deal with access control so we just set public access rights on the root-folder:

In the SQL Azure Server Administration Tool we create now a new database.

We will also need to set the firewall rules. In order to allow our Windows Azure application to access the database we need at least “Allow Microsoft Services”. This can be done by simply ticking the checkbox. Since we also want to access the database from our local machine we need to grant access to our own IP address as well. In order to do that we click on “Add Rule”. The web site automatically determines our IP-address. We copy this address and paste it in the start- and end- of the IP range. Then we submit the new rule.

We should now have 2 firewall rules.

Now we need to create the table and populate it with our data. There are a couple of migration tools out there but if we use SQL Server 2008 R2 we already have all we need. In the SQL Server Management Studio we right-click on your database and select from the context menu “Tasks” and then “Generate Scripts”.

In the wizard we select the objects that we want to script and then in the advanced options we select that we want to script for a SQL Azure database and that we want both the schemas and the data.

Now we change the database connection in our SQL Server Management Studio to our SQL Azure database. We will get the details for the connection from the SQL Azure Server Administration Page.

Finally we execute the script that was generated with the wizard before:

All right, the data is already in the cloud now we need to do the same with our application. In the application we need to switch the baseURL in the MainPage.xaml.vb from our local machine to the Windows Azure Blob Storage:

    'Public Shared baseURL As String = "http://localhost"
    Public Shared baseURL As String = http://hannesve.blob.core.windows.net

In the web.config we need to modify the connection string. We can generate a connection string in the SQL Azure Server Administration Page but that one didn’t quite work for me. In order to make it work I had to append @ServerName to the User ID:

<add name="TalkingDonkeyAzure" connectionString="Server=tcp:siljplpmxm.database.windows.net;Database=TalkingDonkey;User ID=jkebeck@siljplpmxm;Password=YOUR_PWD;Trusted_Connection=False;" providerName="System.Data.SqlClient" />

Once we have made the changes we can build our application again and create a new or open an existing Cloud Service project in Visual Studio. In my example I have an existing application with a couple of samples and I want to add this one to it. In the Solution Explorer I create a new folder under the Web Role project and add the xap-file as well as the htm- and js-files as existing items from the project that we have been working on. The web service goes into the root of this web role project since that’s where our code expects the service endpoint.

Once we have tested the project we can right-click on the Azure-Project and select “Publish” from the context menu:

This will package the solution, open Windows Explorer at the folder where the package was created and it will also open the Windows Azure Administration page where you can upload the package to a staging environment and test it before you swap it with the production environment.

That’s it. Ciao for today :-)

Adding the Map and Extending the Navigation Control

Now that we have set up the database and created the web service we can move on to the Silverlight application. First we add the Bing Maps Silverlight control as reference to our Silverlight project:

Next we add a service reference pointing to the web service that we have created before. Click on discover to find the service and give it a name.

Particularly when we work with Windows Azure we must be able to modify the endpoint of the service dynamically and hence we add the following code to the public sub new method of the MainPage.xaml.vb

'Reset service reference endpoint (important when port or domain changes)
Dim wsURL As String = "http://" + HtmlPage.Document.DocumentUri.Host + ":" + _
  HtmlPage.Document.DocumentUri.Port.ToString + "/svcPhotos.svc"
svc.Endpoint.Address = New ServiceModel.EndpointAddress(wsURL)

In the MainPage.xaml we add the namespace for the Bing Maps Silverlight Control and the map itself.

We also add a mini-map that provides an overview. In this overview-map we remove all components that might obstruct the view on the map itself.

To add additional elements to the navigation control we add a handler right after we initialized the component:

Public Sub New()
    InitializeComponent()

    ' Add additional controls to the navigation bar
    AddHandler MyMap.MapForeground.TemplateApplied, AddressOf MapForeground_TemplateApplied

We are going to add 3 additional control elements

• toggle mini map
• toggle full-screen and
• show/hide photos

#Region "Navigation"
    Private Sub MapForeground_TemplateApplied(ByVal sender As Object, ByVal e As EventArgs)
        AddHandler MyMap.MapForeground.NavigationBar.TemplateApplied, _
          AddressOf NavigationBar_TemplateApplied
    End Sub

    Private Sub NavigationBar_TemplateApplied(ByVal sender As Object, ByVal e As EventArgs)
        Dim myNavCtrl As NavigationBar = MyMap.MapForeground.NavigationBar

        ' Add custom command buttons
        myNavCtrl.HorizontalPanel.Children.Add(New CommandSeparator())
        Dim btnToggleMiniMap As New CommandToggleButton( _
          New ToggleMiniMap(), "Mini-Map", "Toggle Mini-Map")
        myNavCtrl.HorizontalPanel.Children.Add(btnToggleMiniMap)

        myNavCtrl.HorizontalPanel.Children.Add(New CommandSeparator())
        Dim btnPhotos As New CommandToggleButton( _
          New TogglePhotos(), "Photos", "Show/Hide Photos")
        myNavCtrl.HorizontalPanel.Children.Add(btnPhotos)

        Dim imgFullScreen As New Image
        imgFullScreen.Source = New BitmapImage( _
          New Uri("FullScreen.png", UriKind.RelativeOrAbsolute))
        imgFullScreen.Width = 15
        Dim btnFullScreen = New CommandButton( _
          New ToggleFullscreen(), imgFullScreen, "Toggle Fullscreen")
        myNavCtrl.VerticalPanel.Children.Add(btnFullScreen)
    End Sub
#End Region

The functions that are being executed when we click these control-elements are in separate classes and I have simply added them at the end of MainPage.xaml.vb

#Region "Additional Commands in Dashboard"

Class ToggleMiniMap
    Inherits NavigationBarCommandBase

    Public Sub New()
    End Sub

    Public Overloads Overrides Sub Execute(ByVal map As MapBase)
        Dim status As NavigationBarCommandStatus = Me.GetStatus(map)

        If status = NavigationBarCommandStatus.Checked Then
            MainPage.myMiniMap.Visibility = Visibility.Collapsed
        ElseIf status = NavigationBarCommandStatus.Normal Then
            MainPage.myMiniMap.Visibility = Visibility.Visible
        End If
    End Sub

    Public Overloads Overrides Function GetStatus(ByVal map As MapBase) _
      As NavigationBarCommandStatus
        Dim status As NavigationBarCommandStatus = NavigationBarCommandStatus.Normal
        If MainPage.myMiniMap.Visibility = Visibility.Visible Then
            status = NavigationBarCommandStatus.Checked
        End If
        Return status
    End Function
End Class

Class TogglePhotos
    Inherits NavigationBarCommandBase

    Public Sub New()
    End Sub

    Public Overloads Overrides Sub Execute(ByVal map As MapBase)
        Dim status As NavigationBarCommandStatus = Me.GetStatus(map)

        If status = NavigationBarCommandStatus.Checked Then
            MainPage.slPhotos.Visibility = Visibility.Collapsed
            MainPage.slPhotos.Children.Clear()
            RemoveHandler map.ViewChangeEnd, AddressOf MainPage.MyMap_ViewChangeEnd
        ElseIf status = NavigationBarCommandStatus.Normal Then
            MainPage.slPhotos.Visibility = Visibility.Visible

            AddHandler map.ViewChangeEnd, AddressOf MainPage.MyMap_ViewChangeEnd

            AddHandler MainPage.svc.GetClusterInViewCompleted, _
              AddressOf MainPage.svc_GetClusterInViewCompleted
            Dim bounds As LocationRect = map.BoundingRectangle
            MainPage.svc.GetClusterInViewAsync(bounds.Northwest.Latitude, _
              bounds.Northwest.Longitude, bounds.Southeast.Latitude, _
              bounds.Southeast.Longitude, CInt(map.TargetZoomLevel), _
              MainPage.mapWidth, MainPage.mapHeight)
        End If
    End Sub

    Public Overloads Overrides Function GetStatus(ByVal map As MapBase) _
      As NavigationBarCommandStatus
        Dim status As NavigationBarCommandStatus = NavigationBarCommandStatus.Normal
        If MainPage.slPhotos.Visibility = Visibility.Visible Then
            status = NavigationBarCommandStatus.Checked
        End If
        Return status
    End Function
End Class

Class ToggleFullscreen
    Inherits NavigationBarCommandBase

    Public Sub New()
    End Sub

    Public Overloads Overrides Sub Execute(ByVal map As MapBase)
        Application.Current.Host.Content.IsFullScreen = _
         Not Application.Current.Host.Content.IsFullScreen
    End Sub
End Class

#End Region

 

Preparing the Photo-Viewer

The Photo-Viewer is basically a ChildWindow that comes as part of the Silverlight Toolkit for Visual Studio 2008 SP1 and is out-of-the-box part of Visual Studio 2010 Beta 2. So if you are using Visual Studio 2010 Beta 2 just add a new item of type ChildWindow

The basic xaml is quite simple since we add most of child-elements dynamically through code…

<controls:ChildWindow x:Class="BM_SL_PhotoMap.PhotoShow"
           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
           xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"
           Width="400" Height="300"
           Title="PhotoShow">

    <Grid x:Name="LayoutRoot" Margin="2">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition />
            <RowDefinition Height="Auto" />
        </Grid.RowDefinitions>

        <Image x:Name="imgCurrentPhoto" Stretch="Uniform" Grid.Column="0" Grid.Row="0" 
               Grid.RowSpan="3"/>

        <StackPanel x:Name="spPhotos" 
                Background="#CC646464"
                Opacity="0.8"
                Orientation="Horizontal" 
                VerticalAlignment="Bottom" 
                HorizontalAlignment="Left"
                Width="800"
                Height="130"
                Grid.Row="2" Grid.Column="0" 
                MouseEnter="spPhotos_Enter" 
                MouseLeave="spPhotos_Leave" >
            <Border CornerRadius="5" BorderThickness="3" BorderBrush="White" 
              Margin="5" Height="120" Width="60">
                <StackPanel Orientation="Vertical" Height="120">
                    <Image x:Name="imgPlay" Source="Play.png" Width="40" Stretch="Uniform" 
                       Margin="0,5,0,0" VerticalAlignment="Top" />
                    <Image x:Name="imgPause" Source="Pause.png" Width="40" Stretch="Uniform" 
                       Margin="0,25,0,0" />
                </StackPanel>
            </Border>
            <Image Source="PanLeft.png" Width="50" Stretch="Fill" Height="120" 
               MouseLeftButtonDown="PanLeft_MouseLeftButtonDown"/>
            <ScrollViewer x:Name="svThumbs" HorizontalScrollBarVisibility="Hidden" 
               VerticalScrollBarVisibility="Hidden" Width="600" BorderBrush="{x:Null}" >
                <StackPanel x:Name="spThumbs" Orientation="Horizontal" Margin="-5,-5,0,0" >
                </StackPanel>
            </ScrollViewer>
            <Image Source="PanRight.png" Width="50" Stretch="Fill" Height="120" 
               MouseLeftButtonDown="PanRight_MouseLeftButtonDown"/>
        </StackPanel>

        <StackPanel x:Name="spTitle" 
                Background="#CC646464"
                Opacity="0.8"
                Orientation="Horizontal" 
                VerticalAlignment="Top" 
                HorizontalAlignment="Left"
                Width="800"
                Height="30"
                Grid.Row="0" Grid.Column="0" 
                MouseEnter="spPhotos_Enter" 
                MouseLeave="spPhotos_Leave" >
            <StackPanel x:Name="spTitleText" Orientation="Horizontal" Margin="5">
            </StackPanel>
        </StackPanel>
    </Grid>
</controls:ChildWindow>

…but I have changed the style and added storyboards in Expression Blend in order to show the title and thumbnails when you mouse over the regions and hide them when you don’t:

 

Calling the Web Service

Remember when we created the additional controls in our dashboard we also created a class to show or hide the photos. When activated this element adds a handler for the ViewChangeEnd event and queries our database for the first time.

The ViewChangeEnd event calls our web service asynchronously and adds a handler that processes the result.

Public Shared Sub MyMap_ViewChangeEnd(ByVal sender As Object, ByVal e As MapEventArgs)
        Dim map As Map = DirectCast(sender, Map)
        Dim bounds As LocationRect = map.TargetBoundingRectangle

        AddHandler svc.GetClusterInViewCompleted, AddressOf svc_GetClusterInViewCompleted
        svc.GetClusterInViewAsync(bounds.Northwest.Latitude, _
          bounds.Northwest.Longitude, bounds.Southeast.Latitude, _
          bounds.Southeast.Longitude, CInt(map.TargetZoomLevel), mapWidth, mapHeight)
    End Sub

When we process the result we first delete all pins in the layer before we loop through the items in our list-object and create new pins. In the tag of each pin we add the name of the photo and also any other information that we want to show in the title. We also add a handler that fires whenever we click on the pin.

    Public Shared Sub svc_GetClusterInViewCompleted(ByVal sender As Object, _
      ByVal e As GetClusterInViewCompletedEventArgs)
        slPhotos.Children.Clear()
        If e.Error Is Nothing Then
            For i = 0 To e.Result.Count - 1
                Dim myPin As New Pushpin
                myPin.Location = New Location(e.Result(i)._Lat, e.Result(i)._Lon)
                myPin.Tag = e.Result(i)._Names
                slPhotos.Children.Add(myPin)
                AddHandler myPin.MouseLeftButtonDown, AddressOf PhotoShow
            Next
        Else
            MessageBox.Show(e.Error.Message)
        End If
    End Sub

When we click on the pin we split its tag into a string-array the element 0 and all items with an even number contain the file-name of the photo (without the extension (.jpg). All odd-number elements contain the information that we want to show in the title.

Now we cycle through this string array and create child elements for our Photo-Viewer. The thumbnail images will have a handler attached that allows us to click on it in order to display a larger image in the main window and and set the title.

    Public Shared Sub PhotoShow(ByVal sender As Object, _
      ByVal e As System.Windows.Input.MouseButtonEventArgs)
        cwPhotoShow.spThumbs.Children.Clear()
        cwPhotoShow.spTitleText.Children.Clear()
        Dim myPin As Pushpin = DirectCast(sender, Pushpin)
        Dim myNameArray() As String = Split(myPin.Tag.ToString, ",")
        For i = 0 To (myNameArray.Length / 2) - 1
            If i = 0 Then
                cwPhotoShow.imgCurrentPhoto.Source = New BitmapImage( _
                  New Uri(baseURL + "/digicam/h600/" + myNameArray(i) + ".jpg", UriKind.Absolute))
                Dim myTitleTxt As New TextBlock With {
                .Text = Replace(myNameArray(i * 2 + 1), "|", ", "),
                .Foreground = New SolidColorBrush(Colors.White),
                .HorizontalAlignment = Windows.HorizontalAlignment.Center}
                cwPhotoShow.spTitleText.Children.Add(myTitleTxt)
            End If

            Dim myImg As New Image With {
                .Source = New BitmapImage( _
                   New Uri(baseURL + "/digicam/h100/" + myNameArray(i * 2) + ".jpg", _
                   UriKind.Absolute)),
                .Stretch = Stretch.None,
                .Tag = myNameArray(i * 2 + 1)}
            AddHandler myImg.MouseLeftButtonDown, _
              AddressOf BM_SL_PhotoMap.PhotoShow.Thumb_MouseLeftButtonDown

            Dim myLocArray() As String = Split(myNameArray(i * 2 + 1), "|")
            Dim myTxt As New TextBlock With {
                .Text = myLocArray(0),
                .Foreground = New SolidColorBrush(Colors.White),
                .HorizontalAlignment = Windows.HorizontalAlignment.Center}

            Dim mySP As New StackPanel With {
                .Orientation = Orientation.Vertical,
                .VerticalAlignment = Windows.VerticalAlignment.Center}
            mySP.Children.Add(myTxt)
            mySP.Children.Add(myImg)

            Dim myBorder As New Border With {
                .CornerRadius = New CornerRadius(5),
                .BorderThickness = New Thickness(3),
                .BorderBrush = New SolidColorBrush(Colors.White),
                .Margin = New Thickness(5),
                .Height = 120}
            myBorder.Child = mySP

            cwPhotoShow.spThumbs.Children.Add(myBorder)
        Next
        BM_SL_PhotoMap.PhotoShow.numPhotos = cwPhotoShow.spThumbs.Children.Count
        cwPhotoShow.Show()
    End Sub

Finally we add a little bit of code to the Photo-Viewer in order to scroll through the thumbnails, display the photo in the main window and also provide a slide-show function. We won’t go into more detail here but it’s all in the sample code and you can explore it for yourself.

Well that brings us to an end of part 2. The application is ready for your local environment.

You can have a look at it here and download the sample code here.

If you want to learn more about publishing the application to Windows Azure have a look at part 3.

Introduction

There may be very good reasons why you don’t want to cluster your points of interest on a map e.g. if you want to show the density of points in a certain area. An example for this is the Bing Maps World Tour which has been developed by our partner Earthware and which visualizes the highlights of the latest Bing Maps data updates.

The downside of this is though that the map looks cluttered and you can’t access the points at the bottom of the pile anymore. In addition the number of points that you render on a map also affects the performance. The performance aspect is certainly much more relevant for the AJAX control since the Silverlight performance in general is much better but after a few thousand points you might reach a level where the user experience suffers. The Bing Maps AJAX control has client-side clustering build into the API and in a previous post I have compared the performance of client- and server-side clustering for the AJAX control.

The Bing Maps Silverlight control does not provide build-in clustering and hence we will have a look mainly at the clustering but we will also:

• Extend the build-in navigation control and
• Publish the application to Windows Azure and SQL Azure 

In this example we will build a photomap. On low zoom-levels, i.e. when we look at the whole world, all the photos for let’s say Sardinia are represented by a single pushpin…

…but we still have access to all the photos that are represented by this pin:

On higher zoom-levels we break up the cluster and show accurate positions for the photos. In the screenshot below you also see, that we extended the build-in navigation control with some custom elements to show and hide the photo-layer, toggle the mini-map and toggle full-screen mode.

You can see the application here and download the sample code here

Note: You will have to enter your own Bing Maps Key in the MainPage.xaml and modify the connection string in the web.config

 

What we need

• A Bing Maps Key. You can generate your keys here.
• The Bing Maps Silverlight Control. The download is here; the full online reference is here; an interactive SDK is here.
  Tip: When you right-click on the interactive SDK you can install an out-of-browser version of the application on your desktop
• I use the Visual Studio 2010 Beta 2 but the code is written in .NET 3.5 and will work with Visual Studio 2008 SP1 as well. If you use the Visual Studio 2008 you will need the SP1 and the Silverlight 3 Tools for Visual Studio 2008 SP1. The photos will show in a ChildWindow which is already part of the Visual Studio 2010 Beta 2 but if you use Visual Studio 2008 SP1 you will also need the Silverlight Toolkit.
• For the design of the Silverlight components and animations it is helpful to have Expression Blend 3.
• The meta-data for the photos are stored in a database. I use the SQL Server 2008 R2 November CTP but any SQL database will do.
• If you want to publish the application to Windows Azure you will also need
  • the Windows Azure Tools for Microsoft Visual Studio (November 2009)
  • A Windows Azure and a SQL Azure account. Follow the instructions here to get the invitation.

 

The Concept

Before we actually start coding let’s hold on for a sec and think about the concept. The Bing Maps Silverlight control supports a variety of events. Since we don’t want to load all the photos but only those that are in the current map view we use these events to determine the bounding box and a couple of other parameters for our database query whenever we finish zooming or panning the map. At a first glance it might appear that the event TargetViewChanged might be appropriate but that would be a mistake since the target view changes with the zoom-level – so far so good – but also with every frame when you pan the map. That would create quite a lot of database queries and we certainly want to avoid that. More appropriate is the ViewChangeEnd event. We will add a handler for this event to the map and call a web service asynchronously. The web service will query our database, create a cluster of points and return a List-object that we can then process in our Silverlight application.

In order to cluster the photos (or points of interest) we will create in the web service a grid – let’s say with a width of 40 pixel – and determine the photos that are within each grid-cell. For each grid-cell that has one or more photo in it we will return a pin and tag it with some ID’s that identify the individual photos.

Once the Silverlight application receives the response we will process the data by adding pins to the map and dynamically creating child-elements for our photo-viewer. In this example we will do it by adding thumbnail images to a StackPanel in the ChildWindow. OK, let’s do it.

 

The Local Database

The database will be quite simple. We just have one table where me maintain the meta-data for our photos. The script for the table is listed below.

CREATE TABLE [Photos](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Title] [varchar](250) NULL,
    [Name] [varchar](50) NULL,
    [Date] [date] NULL,
    [Loc1] [varchar](250) NULL,
    [Loc2] [varchar](250) NULL,
    [Loc3] [varchar](250) NULL,
 CONSTRAINT [PK_Photos] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

We need columns for the latitudes and longitudes of the locations where the photo was taken, the name of the file (the part before the extension .jpg) and optionally some more information that describe the location.

The photos themselves are accessible through a web server and for starters we’re going to use our local Internet Information Service to create a virtual directory that points to the location of the photos.

 

The Web Service

Let’s first create the web service that accesses the database. In our Visual Studio we create a new Silverlight application:

And choose to host the application in a new website.

In the web-project we create a new Silverlight-enabled WCF Service. Let’s call it svcPhotos.

If you consider using Windows Azure you must be aware of a bug that really puzzled me for a while. Everything worked well when I had my stand-alone application but once I wanted to use it within a Windows Azure project – no matter if it as in the development fabric or in the live environment – I received a weird error message: “cannot be processed at the receiver, due to an AddressFilter mismatch at the EndpointDispatcher. Check that the sender and receiver's EndpointAddresses agree.”. Since I was actually re-writing the endpoint in my code I was pretty sure that the address was correct and finally I found out that the error can be resolved by adding the following ServiceBehaviour:

In our service we create a DataContract that describes the list-items in the list-object we want to return as well as an OperationsContract that queries the database, clusters the points and returns the list-object. The OperationsContract will take as input-parameters

• latitudes and longitudes of the bounding box of the current map view
• The width and height of the map in the browser
• and the zoom-level

For the clustering we will also nee some helper functions that allow us to convert latitudes and longitudes into pixel coordinates. These functions are explained in more detail here. The full code of the web service is listed below:

Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Data.SqlClient
Imports System.Runtime.Serialization
Imports System.Globalization

<ServiceContract(Namespace:="svcPhotos")>
<ServiceBehavior(AddressFilterMode:=AddressFilterMode.Any)>
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)>
Public Class svcPhotos
    'Constants for the Clustering
    '(addressable area in Bing Maps)
    Private Const MinLatitude As Double = -85.05112878
    Private Const MaxLatitude As Double = 85.05112878
    Private Const MinLongitude As Double = -180
    Private Const MaxLongitude As Double = 180

    'Grid-Size
    Private Const GridSize As Integer = 40

    'Clips a number to the specified minimum and maximum values
    Private Shared Function Clip(ByVal n As Double, ByVal minValue As Double, _
      ByVal maxValue As Double) As Double
        Return Math.Min(Math.Max(n, minValue), maxValue)
    End Function

    'Determine the offset off the map
    Public Shared Function Offset(ByVal lvl As Integer) As UInt32
        Return 256 << lvl
    End Function

    'Convert Latitude and Longitude to VEPixel
    Public Shared Sub LatLongToPixel(ByVal latitude As Double, ByVal longitude As Double, _
      ByVal lvl As Integer, ByRef pixelX As Integer, ByRef pixelY As Integer)
        latitude = Clip(latitude, MinLatitude, MaxLatitude)
        longitude = Clip(longitude, MinLongitude, MaxLongitude)

        Dim x As Double = (longitude + 180) / 360
        Dim sinLatitude As Double = Math.Sin(latitude * Math.PI / 180)
        Dim y As Double = 0.5 - Math.Log((1 + sinLatitude) / (1 - sinLatitude)) / (4 * Math.PI)

        Dim mapSize As UInt32 = Offset(lvl)
        pixelX = CType(Clip(x * mapSize + 0.5, 0, mapSize - 1), Integer)
        pixelY = CType(Clip(y * mapSize + 0.5, 0, mapSize - 1), Integer)
    End Sub

    <OperationContract()> _
    Public Function GetClusterInView(ByVal NWlat As Double, ByVal NWlon As Double, _
      ByVal SElat As Double, ByVal SELon As Double, ByVal lvl As Integer, _
      ByVal mapWidth As Integer, ByVal mapHeight As Integer) As List(Of ClusterPoint)
        'set culture to en-UK to avoid potential problems with decimal-separators
        System.Threading.Thread.CurrentThread.CurrentCulture = _
          System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

        'Set up a grid for the Clustering
        Dim numXCells = CInt(Math.Ceiling(mapWidth / GridSize))
        Dim numYCells = CInt(Math.Ceiling(mapHeight / GridSize))
        Dim numCells As Integer = numXCells * numYCells - 1
        Dim gridCells()() As Object = New Object(numCells)() {}

        'Determine PixelX and PixelY of upper left corner
        Dim ulTotalX As Integer
        Dim ulTotalY As Integer
        LatLongToPixel(NWlat, NWlon, lvl, ulTotalX, ulTotalY)

        'Query database(s) and create JavaScript
        Dim poiTotalX As Integer
        Dim poiTotalY As Integer
        Dim poiMapX As Integer
        Dim poiMapY As Integer
        Dim settings As ConnectionStringSettings
        'Connection String is set in web.config

        settings = ConfigurationManager.ConnectionStrings("TalkingDonkeyLocal")

        Dim myConn As New SqlConnection(settings.ConnectionString)
        myConn.Open()
        Dim myQuery As String = "SELECT Lat, Lon, Name, Loc1, Loc2, Loc3, Date “ _
          + ”FROM Photos WHERE (Lat BETWEEN " + SElat.ToString + " AND " _
          + NWlat.ToString + ") AND (Lon BETWEEN " + NWlon.ToString _
          + " AND " + SELon.ToString + ")"
        Dim myCMD As New SqlCommand(myQuery, myConn)
        Dim myReader As SqlDataReader = myCMD.ExecuteReader()
        While myReader.Read()
            'Determine PixelX and PixelY of POI
            LatLongToPixel(myReader(0), myReader(1), lvl, poiTotalX, poiTotalY)
            poiMapX = poiTotalX - ulTotalX
            poiMapY = poiTotalY - ulTotalY
            'Populate the array with clustered pins
            For x = 0 To numXCells - 1
                If (x * GridSize <= poiMapX) And (poiMapX < (x + 1) * GridSize) Then
                    For y = 0 To numYCells - 1
                        If (y * GridSize <= poiMapY) And (poiMapY < (y + 1) * GridSize) Then
                            Dim myClusteredPin(4) As Object
                            If gridCells(x * y) Is Nothing Then
                                myClusteredPin(0) = 1
                                myClusteredPin(1) = myReader(0)
                                myClusteredPin(2) = myReader(1)
                                myClusteredPin(3) = myReader(2).ToString + "," _
                                  + myReader(3).ToString + "|" + myReader(4).ToString _
                                  + "|" + myReader(5).ToString + "|" _
                                  + CDate(myReader(6)).ToString("d", New CultureInfo("en-GB"))
                            Else
                                myClusteredPin = gridCells(x * y)
                                myClusteredPin(0) = myClusteredPin(0) + 1
                                myClusteredPin(3) = myClusteredPin(3) + "," _
                                  + myReader(2).ToString + "," + myReader(3).ToString _
                                  + "|" + myReader(4).ToString + "|" + myReader(5).ToString + "|" _
                                  + CDate(myReader(6)).ToString("d", New CultureInfo("en-GB"))
                            End If
                            gridCells(x * y) = myClusteredPin
                        End If
                    Next
                End If
            Next
        End While
        myReader.Close()
        myConn.Close()

        'Create the pins
        Dim myPins As New List(Of ClusterPoint)
        For i = 0 To numCells
            If gridCells(i) IsNot Nothing Then
                Dim myClusteredPin = gridCells(i)
                Dim myPin As New ClusterPoint(myClusteredPin(1), myClusteredPin(2), _
                  myClusteredPin(3))
                myPins.Add(myPin)
            End If
        Next

        Return myPins
    End Function

End Class

<DataContract()> _
Public Class ClusterPoint
    <DataMember()> _
    Private _Lat As Double
    Public Property Lat() As Double
        Get
            Return _Lat
        End Get
        Set(ByVal value As Double)
            _Lat = value
        End Set
    End Property

    <DataMember()> _
    Private _Lon As Double
    Public Property Lon() As Double
        Get
            Return _Lon
        End Get
        Set(ByVal value As Double)
            _Lon = value
        End Set
    End Property

    <DataMember()> _
    Private _Names As String
    Public Property Names() As String
        Get
            Return _Names
        End Get
        Set(ByVal value As String)
            _Names = value
        End Set
    End Property

    Public Sub New(ByVal _Lat As Double, ByVal _Lon As Double, ByVal _Names As String)
        Lat = _Lat
        Lon = _Lon
        Names = _Names
    End Sub
End Class

 

Developing with Bing Maps is great fun but not everybody wants to fire up his Visual Studio to get a map on his website. With the latest launch – Chris Pendleton blogged about it here, here, here and here – we also introduced embedded maps. Embedded maps in it’s most simple form can just be generated from our consumer facing site http://bing.com/maps. Just navigate the map to the place that you want to show and then click on the “Share” button.

This will bring up a dialog where you can copy a link that will get you back to http://bing.com/maps and recreate the current map-view. That is not really new, it has been there before but there is now also another area where you can copy a piece of HTML-code that you can paste into your website in order to embed a static or interactive map.

The link “Customize and preview” brings up another dialog that allows you to further customize this piece of HTML-code:

As you can see this gives you plain maps with the roadmap or aerial images but in order to see the nice Bird’s Eye view you have to use the link underneath the map that get’s you back to http://bing.com/maps.

In our Bing Maps AJAX SDK we have however now also a couple of changes and one of these allows you to create more comprehensive embedded maps by using additional URL-parameters.

Parameter Name	Description
mapMode	The style of the map. Valid values are: Road, Aerial, AerialWithLabels, Birdseye, BirdseyeWithLabels
zoomLevel	An int indicating a valid zoom level for the map style.
center	Two doubles separated by an underscore that are the latitude and longitude values of the coordinate of the center of the map.
heading	A double specifying the orientation of the map in degrees.
pushpins	Two doubles separated by an underscore that are the latitude and longitude values of the coordinate of the pushpin. Multiple pushpin locations are separated by a ~.
culture	A valid culture string. The default value is “en-us”.

So this iFrame for example…

<iframe height="400" 
        marginheight="0" 
        src="http://dev.virtualearth.net/embeddedMap/v1/ajax/Birdseye?
          zoomLevel=19&amp;
          center=51.46130379870268_-0.9260680852445592&amp;
          heading=180" 
        frameborder="0" 
        width="100%" 
        marginwidth="0" 
        scrolling="no">
</iframe>

…gives as a nice Birds Eye view of the Microsoft office in Reading facing south:

Since we also launched a Bing Maps Silverlight control we have the same approach for embedded maps available for Silverlight too. Here is the iFrame:

<iframe height="400" 
        marginheight="0" 
        src="http://dev.virtualearth.net/embeddedMap/v1/silverlight/Aerial?
          zoomLevel=17&amp;
          center=51.461600455334924_-0.9259327501058578&amp;
          pushpins=51.461550321682765_-0.9256913512945175" 
        frameborder="0" 
        width="100%" 
        marginwidth="0" 
        scrolling="no">
</iframe>

and here is the result:

Last week we launched a new wave of Bing Maps. My esteemed colleague Chris Pendleton has blogged about some of the changes here, here, here and here. One of the main announcements has been the launch of the Bing Maps Silverlight Control v1. You will find the interactive SDK here, the full reference on MSDN here and the download here. This control has been available as a Community Technology Preview (CTP) for quite a while but those of you who have been waiting for a fully supported version before getting involved may not be too familiar with it yet. If you did work with the CTP please be aware that it is time-bombed and will stop working by the end of the year; so please consider updating your application soon.

If you have been working with the Bing Maps AJAX Control before you may actually be missing a few things that came in handy if you wanted to display additional information like for a example a Photosynth collection. This was quite simple in the AJAX control since you could simply generate an iFrame in Photosynth and just add it to a VEShape-object using the VEShape.SetDescription-method. You can look at a sample here and download the source here).

Since pushpins in the Silverlight control don’t have an info-box like the ones in the AJAX-control the approach here is quite different but it still works very well. The major obstacle appear to be that we cannot directly integrate HTML-code into our Silverlight application. The solution is actually to use the HTML Bridge in order to lay a HTML DIV-element on top of the Silverlight control and arrange it appropriately. Of course you can write everything from the scratch but the guys from divelements have created a Silverlight-Control HtmlHost for us that you can download for free and use to add and control HTML content directly from your Silverlight application. Let’s have a crack at it. For this walkthrough we will need

• Visual Studio 2008 SP1 or higher
• The Silverlight 3 Tools for Visual Studio 2008 SP1
• The free Silverlight Toolkit
• The free Silverlight Tools from divelements
• Expression Blend 3
• The Bing Maps Silverlight SDK
• A Bing Maps Key. If you don’t have one you can create it here.

First we create a new Silverlight application and add references to our Bing Maps Silverlight Control

In the MainPage.xaml we add a reference to our Bing Maps Silverlight control…

xmlns:m="clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl"

 

…and the the map itself with a pushpin at the location where we our Photosynth collection is. We also attach an event to the pushpin that fires when we click on it.

<m:Map CredentialsProvider="YOUR BING MAPS KEY"
       Mode="AerialWithLabels"
       Center="32.362980181127874,-64.71483707427978"
       ZoomLevel="17">
       <m:Pushpin Name="pinMartelloTower"
                  Location="32.362980181127874,-64.71483707427978"
                  MouseLeftButtonDown="pinMartelloTower_MouseLeftButtonDown"/>
</m:Map>

 

If you had done something like this with the CTP before you will notice a few differences:

• The namespace for the Bing Maps Silverlight Control has changed
• We now need to provide a CredentialsProvider in order to authenticate. This is basically the Bing Maps Key that we generated before.
• We have now a Pushpin-object that we can use as a child of a map

In my sample here I chose to bring up the Photosynth collection as part of a ChildWindow. A ChildWindow is a Silverlight control that comes with the Silverlight Toolkit. So let’s add a ChildWindow as new object to our Silverlight-project. I call it Photosynth. The trouble with a ChildWindow and HTML-DIV-elements on top is that the ChildWindow has an animation and explodes when you open it. If you would just add the HTML-DIV-element when the ChildWindow opens it would appear in the bottom-right quadrant of the screen. In order to avoid this we can simply remove the animation by changing the template in Expression Blend. In Visual Studio just right-click on the ChildWindow and select “Open in Expression Blend” from the context menu.

In Expression Blend right-click on the ChildWindow-object and select “Edit Template” => “Edit a Copy”.

Now we switch to code-view and remove the whole VisualStateManager that handles the StoryBoards for opening and closing the window. By default this would start around line 119:

While we’re at it we can optionally make a few more changes to adjust the design:

Now we can leave Expression Blend and go back to Visual Studio. In order to add the HtmlHost from divelements right-click on the Toolbox, select “Choose Items” from the context menu and add the Divelements.SilverlightTools.dll from the location where you extracted the download.

Now drag&drop the control on your ChildWindow. This will automatically add the required namespace you only need to add a Name-property to the HtmlHost. Your XAML-should now look like this

<controls:ChildWindow x:Class="SilverlightApplication2.Photosynth"
           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
           xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"
           xmlns:my="clr-namespace:Divelements.SilverlightTools;assembly=Divelements.SilverlightTools"  
           Width="400" Height="300"
           Title="Photosynth">
    <controls:ChildWindow.Resources>
…
    </controls:ChildWindow.Resources>
    <controls:ChildWindow.Style>
        <StaticResource ResourceKey="ChildWindowStyle"/>
    </controls:ChildWindow.Style>
    
    <Grid x:Name="LayoutRoot" Margin="2">
      <Grid.RowDefinitions>
        <RowDefinition />
        <RowDefinition Height="Auto" />
      </Grid.RowDefinitions>
        <my:HtmlHost Name="htmlHost"></my:HtmlHost>
    </Grid>
</controls:ChildWindow>

Finally we can start coding. The only code we need is actually in the MainPage.xaml.vb. When we initialize the page we also add a handler which resizes the ChildWindow whenever the size of the browser window changes.

When we click on the pushpin we set the title for the ChildWindow as well as the source for the HtmlHost. In this case we just add the iFrame which we can copy directly from Photosynth…

…and make to minor changes:

• we change the width and height of the iFrame to 100%
• we set the URL-parameter delayLoad from true to false. By default the Photosynth viewer would show a static image and only load the collection after you clicked on this image. By changing this parameter we load the collection immediately.

Imports System.Windows.Browser

Partial Public Class MainPage
    Inherits UserControl

    Public myHeight As Integer
    Public myWidth As Integer

    Private WithEvents cwPhotosynth As New Photosynth

    Public Sub New()
        InitializeComponent()

        AddHandler LayoutRoot.SizeChanged, AddressOf Page_SizeChanged
    End Sub

    Private Sub Page_SizeChanged(ByVal sender As Object, ByVal e As SizeChangedEventArgs)
        myWidth = CInt(e.NewSize.Width)
        myHeight = CInt(e.NewSize.Height)

        cwPhotosynth.Width = e.NewSize.Width * 0.8
        cwPhotosynth.Height = e.NewSize.Height * 0.8
    End Sub

    Private Sub pinMartelloTower_MouseLeftButtonDown _
      (ByVal sender As System.Object, ByVal e As System.Windows.Input.MouseButtonEventArgs)
        cwPhotosynth.Title = "Martello Tower"
        cwPhotosynth.htmlHost.SourceHtml = _
          "<iframe frameborder='0' src='http://photosynth.net/embed.aspx?cid=ba12ab48-6899-4d7f-b28c-624f5f7ff4f0” + _
            &delayLoad=false&slideShowPlaying=false' width='100%' height='100%'></iframe>"
        cwPhotosynth.Show()
    End Sub

End Class

Well that’s already it:

You can have a look at the sample here and download the source code here.

Note: if you download the sample code you need to create a Bing Maps Key and enter it into the XAML in order to load the Bing Maps. Also note that the solution was build with Visual Studio 2010 Beta 2, however all methods work as well in Visual Studio 2008 SP1

On behalf of our friends from Borchert GeoInfo and the Bing Maps team I would like to thank everybody who visited us at the booth during the TechEd in Berlin or attended one of my sessions. Some of you asked for my presentations and the sample code. You will find everything here on my SkyDrive:

Have a look at the Readme.pdf if you are unsure about what you need.

Tomorrow the TechEd Europe opens it’s gates in Berlin and Bing Maps will be represented as well. Chris Pendleton will come over from Redmond and join us for the week and I have the privilege of presenting 2 sessions on Bing Maps:

1. WIA02-IS Bing Maps Silverlight Control, Location Intelligence, and Microsoft SQL Server 2008
   Tue 10th of November 13:30-14:45 Interactive Theatre 2 – Orange
   
2. WIA306 Enhancing the Mapping Experience with Microsoft Bing Maps
   Thu 12th of November 17:00-18:15 New York 3 - Hall 7-1a

Here is a quick Bing Maps Collection with some MapCruncher layers that shows the venue and helps you find the Bing Maps booth and the locations for my sessions :-) You can also jump straight into a 3D-tour.

I’m looking forward to see some of you in Berlin.

SQL Server 2008 R2 is the next generation of the Microsoft SQL Server platform. The release is planned for the first half of calendar year 2010 but for those who can’t wait there is as always a community technology preview (CTP). The August CTP has lots of new features and from the mapping perspective the most interesting one is the integration of a maps in SQL Server Reporting Services. With just a few mouse-clicks you can generate thematic maps from spatial data stored as GEOMTRY or GEOGRAPHY data types in SQL Server 2008 or from ESRI SHP-files and you can use Bing Maps roads, aerial or hybrid images as background data.

If you are as nosy as me, you can download the CTP here. Check it out it’s really incredible simple.

If you have been using the “Explore Collections” feature in the consumer facing implementation of Bing Maps before you may have wondered if it is possible to get this feature into your own Bing Maps implementation as well. Indeed that is possible and there is a quite simple approach. In the following walkthrough we will get specifically Wikipedia content into our Bing Maps.

Let’s start with a closer look how the consumer side does it:

If we go to Bing Maps and search for a location like “Tower of London” we’ll find that we can explore collections for this location.

These collections are basically a whole lot of community content that was created in Bing Maps collections, is available as GeoRSS, KML, KMZ or GPX on the internet and was found by the crawlers or is integrated from Wikipedia and Photosynth. We can filter this content, apply different sort criteria such as distance and then we could subscribe to an RSS-feed with the results.

A closer look at the RSS-feed will show that it is in fact a GeoRSS-feed and we know of course that we can import GeoRSS-feeds into Bing Maps using the VEMap.ImportShapeLayerData-method.

What we really want is however not a static feed, we want to update the results when we pan or zoom the map so let’s have a closer look at the URL of the feed:

http://www.bing.com/maps/GeoCommunity.asjx?action=retrieverss&mkt=en-gb&ss=&bbox=-1.0073968023061534,51.42229465956134,-0.8444901555776242,51.50004927438254&startindex=0&order=distance&tag=Wikipedia

So in fact we are calling a web service that generates the GeoRSS-feed dynamically and the parameter bbox contains the bounding box with the South-West and North-East corner of the area for which we want to retrieve the data. Well that is simple enough to implement but there is one more thing to consider: If we call a GeoRSS-feed that is in a different domain we get an annoying security warning from our browser:

To avoid this security warning we can set up a proxy as described by Mike McDougall here.

Our HTML- and JavaScript code could look like this:

<!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://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
      <script type="text/javascript">
          var map = null;

          //VEShapeLayer
          var slGeoRSS = new VEShapeLayer();

          function GetMap() {
              map = new VEMap('myMap');
              map.LoadMap(new VELatLong(51.508145,-0.07626), 17, 'h', false);
          }

          function AddShape(control) {
              if (document.getElementById(control).checked == false) {
                  //Delete all Shapes
                  slGeoRSS.DeleteAllShapes();

                  //Detach Map-Events
                  map.DetachEvent("onendpan", LoadData);
                  map.DetachEvent("onendzoom", LoadData);
              }
              else {
                  //Attach Map-Events
                  map.AttachEvent("onendpan", LoadData);
                  map.AttachEvent("onendzoom", LoadData);
                  LoadData();
              }
          }

          function LoadData() {
              map.DeleteAllShapes();

              //Retrieve the boundaries of the mapview
              var nePixel = new VEPixel(600, 0); //North-East corner of the map view
              var swPixel = new VEPixel(0, 400); //South West corner of the map view
              var neLatLon = map.PixelToLatLong(nePixel);
              var neLat = neLatLon.Latitude;
              var neLon = neLatLon.Longitude;
              var swLatLon = map.PixelToLatLong(swPixel);
              var swLat = swLatLon.Latitude;
              var swLon = swLatLon.Longitude;

              //Build URL to call the server
              var url = "./GeoRSS-Proxy.ashx?source=http://www.bing.com/maps/GeoCommunity.asjx?";
              url += "action=retrieverss&mkt=en-gb&ss=&bbox=";
              url += swLon + ",";
              url += swLat + ",";
              url += neLon + ",";
              url += neLat;
              url += "&startindex=0&order=distance&tag=Wikipedia";

              var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, url, slGeoRSS);
              map.ImportShapeLayerData(veLayerSpec, onGeoRSSLoad, false);
          }

          function onGeoRSSLoad(a, b) {
              var numShapes = slGeoRSS.GetShapeCount();
              var numPoints = 0;
              for (var i = 0; i < numShapes; ++i) {
                  var s = slGeoRSS.GetShapeByIndex(i);
                  s.SetCustomIcon("IMG/wikipedia.gif");
              }
          }
      </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="cbGeoRSS" type="checkbox" onclick="AddShape('cbGeoRSS')" /><a>Wikipedia</a><br />
      </div>
   </body>
</html>

And here is the proxy implemented as a Generic WebHandler

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

Imports System
Imports System.Web
Imports System.Net
Imports System.IO

Public Class GeoRSS_Proxy : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim myUrl As String = ""
        myUrl = context.Request.QueryString(0)
        For i = 1 To context.Request.QueryString.Count - 1
            myUrl = myUrl + "&" + context.Request.QueryString.AllKeys(i) + "=" + context.Request.QueryString(i)
        Next
        'Dim source As String = context.Request.QueryString("source")
        context.Response.ContentType = "text/xml"
        context.Response.ContentEncoding = System.Text.Encoding.UTF8

        Dim request As HttpWebRequest = DirectCast(HttpWebRequest.Create(myUrl), HttpWebRequest)
        Dim response As HttpWebResponse = DirectCast(request.GetResponse(), HttpWebResponse)
        Dim stream As StreamReader = New StreamReader(response.GetResponseStream(), Encoding.ASCII)
        context.Response.Write(stream.ReadToEnd())
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

The sample code is available here:

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 :-)

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:

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.

For this example I use the following components

• SQL Server 2008 
• Bing Maps for Enterprise AJAX Control
• Microsoft Chart Control
• Safe FME
• Visual Studio 2008

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.

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

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).

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.

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.

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.

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.

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

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.

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:

Recently I had the pleasure to work with MSN Norway on a Bing Maps Travel Game. The game is sponsored by VisitNorway.no and went officially live today. Although it is easily localized since all text is received either dynamically through a web service or loaded at the application-start from a xml-file only those of us who speak Norwegian will really be able to enjoy it and have a chance to win a great price at the end. So, if you do speak Norwegian give it a shot but be aware that speed is everything. The faster you find the next lead the more points you get. You can start the game from MSN Norway’s homepage…

…or jump to it directly here.

The game is based on the Bing Maps Silverlight Control. When you start the game a number of randomly chosen questions is selected for you and the time to find the first lead ticks down. Now jog your brain and move the map as fast as possible to the location described in the lead. Zoom in to see the sponsor logo and then mouse over it to stop the timer and receive the credit. The faster you are the higher the credit.

Once in the game and only once you can cheat and use the help button to guide you to the destination but be aware: this will cost you 500 points and reduces your chances to win the price.

After you acknowledged the credit the timer ticks for the next question.

When you are through with all questions that have been selected for you you can enter your name and email-address to participate in the drawing. You can also send updates to Twitter or Facebook to share the game with your friends. Have fun :-)

Trek Wireless specialises in the creation and application of geotagged 2D and 3D digital images for mapping, navigation and commercial applications. Recently they demonstrated the integration of their great POSIPIX images into Virtual Earth. Here you see an animated 360 degree photo of the Central Hall Westminster…

…but it get’s even better when you look at The Maltings in Ely. Here you can follow the arrows to walk through the rooms and use your mouse to discover each room in the full 360 degree angle.

You have certainly heard about Office Live and maybe you have heard about Office Live Small Business as well?

Office Live Workspace and Office Live Small Business are complementary services. Office Live Workspace is for all Office users - it’s your place for online collaboration, to save and share documents and other files. Office Live Small Business, available in select countries, provides additional features that a small business needs to take their business online, including a professional website, domain name, company-branded emails, and online tools for managing customers and projects.

With the Office Live Small Business Web Design Tool you can easily create and design you websites; it even comes with a module to add Maps and Directions to your office:

Unfortunately this module uses the MapPoint Web Service. That is certainly good enough to find your office but for a great visual experience we can definitely do better.

There is indeed an HTML module which allows us to enter our own HTML content and luckily this module also support JavaScript. Not only that, it even allows to reference JavaScript from other sites and therefore we can easily copy and paste our own Virtual Earth web site directly into this module:

Now resize the module to the size that you prefer…

…and there you go:

You find my Office Live Small Business web site here and below is the HTML-page that I copied into the module:

<!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://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2>
    </script>
    <script type="text/javascript">
        var map = null;

        function GetMap(){
        map = new VEMap('myMap');
        var options = new VEMapOptions();
        options.BirdseyeOrientation = VEOrientation.South;
        map.LoadMap(new VELatLong(51.461189,-0.925943), 19, 'o', false, 
                    VEMapMode.Mode2D, true, 0, options);
        }   
    </script>
</head>
<body onload="GetMap();">
    <div id='myMap' 
         style="position:absolute; top:0px; left:0px; width:600px; height:600px;">
    </div>
</body>
</html>

 

Great news on the performance front. As of now the Content Delivery Network (CDN) for the Virtual Earth Platform is live!!!

The Microsoft CDN is a key pillar of the Microsoft cloud computing strategy and is one of the primary investments the Virtual Earth team is making to increase overall platform performance. CDN is composed of multiple geo-distributed data centres (throughout the Americas, Europe and Asia) that allow Virtual Earth to host content closer to customers and end users.

As a result of CDN, delivery speeds of Virtual Earth content have been improved up to 82 percent for both the Virtual Earth AJAX Map Control and Virtual Earth Silverlight Map Control. This first release is part of an on-going program that will expand the network later this year.

To begin utilizing CDN, you will need to make an opt-in, non-breaking change to your AJAX map control code; no action is required for Silverlight Map Control users, as CDN logic is already built-in. You will find some documentation here but it is really very simple. You just need to add 4 little characters in the script-reference and change from:

<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>

UMapper has created a great heatmap based on Virtual Earth to visualize the outbreak of Swine Flue. This map is being updated several times a day using a variety of sources.

The BBC provides also a great website where you find video- and audio-files as well as well as reports and comments from those who have been affected: