Spatial-Enabled Windows Azure (Part 1)

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.

image

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:

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

image

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.

image

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…

image

…and the settings for the Generalizer I chose:

image

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.

image

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 FilesWindows Azure SDKv1.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:

image

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.

image

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

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s