MapForums

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.GetDefaultTableStorageAcco  untFromConfiguration())

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

        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.GetDefaultT  ableStorageAccountFromConfiguration())

    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.

 
 

 
Technorati Tags: Virtual Earth,Bing Maps,Windows Azure,SQL Server 2008,Safe FME,Silverlight,Silverlight Toolkit
 

 
 Click here to view the article.