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


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


image




Click here to view the article.