Dynamic Tile-Layers with Windows Azure and SQL Azure


Recently I had the honour to support our friends and colleagues in Japan in the development of a service that shows the status of roads following the tragic earthquake and the even more disastrous tsunami. Setting up this service in the cloud took only a few hours and in the following I will walk through the components of this solution and the thoughts the led to this particular implementation path.

You will find the sample website here.


I have also created a Bing Map App here.


Components and Reasoning to Choose this Implementation Path

Bing Maps is a cloud-based web mapping service that guarantees high availability and scalability. It is accessible through a consumer site and through a set of APIs which include SOAP and REST web services as well as more interactive AJAX and Silverlight Controls. The AJAX and the Silverlight controls allow you to overlay your own data in vector format or rasterized into tile-layers. Since version 7 the AJAX control is not only supported on the PC and Mac but also on the iPhone, Android and Blackberry browsers. You will find a list of supported browsers here.
In order to remain independent from browser plugins and also to support multiple platforms we settled for the Bing Maps AJAX Control version 7.

When you have a large amount of vector data it might be advisable to rasterize these data into tile layers before overlaying them on your web mapping solution. In this case the source data (provided by Honda) was available as a KMZ-file. The uncompressed size of this data is 40 MB and even compressed it is still 8.7 MB. Downloading such an amount of data would take a while. Particularly if you also intend to support mobile devices it might not be the best option. In addition you have to consider the amount of objects that you intend to render. In this example the data contained 53,566 polylines and rendering would take a while, deteriorating the user experience even further.
Therefore we decided to rasterize the data and overlay them as a tile layer.

After the decision was made to rasterize the data and visualize them as a tile layer on Bing Maps the question was, if we create a static tile layer or rasterize the data on the fly. In this example the data covers 75,628 road-kilometres and an area of 156,624 square kilometres. Creating a complete tile-set down to level 19 would result in 61,875,766 tiles. Rendering all these tiles would take many hours and daily updates would not have been practical. On the other side it is not likely that all these tiles will be needed. There might be regions that people wouldn’t look at at all zoom-levels. In fact it turned out that only a few thousand different tiles are being retrieved every day.
Therefore we decided to set up a solution that creates the tiles on demand and implement a tile-cache in order to enhance performance.

When we started the project we had no idea what traffic we had to expect. Therefore we wanted to be able to scale he solution up and down depending on the traffic. We also wanted to make sure that data and services are held close to the end-users in order to keep latency times low.
Therefore we chose to deploy the solution on Windows Azure and enable the Content Delivery Network (CDN) for the tile cache. Vector data will be stored as spatial data types in SQL Azure.

The last ingredient was the Spatial ETL tool that allows us to load the KMZ-file into SQL Azure and we chose Safe FME for this task. Safe supports hundreds of spatial data formats – including KML/KMZ and SQL Server Spatial data formats.

On a high level the components of the proposed solution look like this.


The solution is accessible as a Map App from the Bing Maps consumer website as well as a “regular” website from PC, Mac and mobile devices.


Loading the Data into SQL Azure

As mentioned above we use Safe FME to load the data into SQL Azure. We only need an OGCKML-reader and the MSSQL_SPATIAL writer.


At the reader we expose the kml_style_url Format Attribut because this holds the information that allows us to distinguish between road segments that were open to traffic and those where traffic had not been observed.


On the writer we create a new User Attribute Color and link the kml_style_url from the reader to it.


Also on the writer we define under the tab parameters a SQL script for the Spatial Index Creation.


alter table JapanRoads0319 add MyID int identity;
CREATE SPATIAL INDEX SI_JapanRoads0319 ON JapanRoads0319(GEOM) USING GEOMETRY_GRID WITH( BOUNDING_BOX  = ( xmin  = 122.935256958008, ymin  = 24.2508316040039, xmax  = 153.965789794922, ymax  = 45.4863815307617), GRIDS  = ( LEVEL_1  = MEDIUM, LEVEL_2  = MEDIUM, LEVEL_3  = MEDIUM, LEVEL_4  = MEDIUM), CELLS_PER_OBJECT  = 16);

In the navigator on the left hand side of the FME workbench we also define a SQL statement that runs after we completed the loading process and validates potentially invalid geometries.


update JapanRoads0319 set geom=geom.MakeValid();

Now that we have the data in SQL Azure we can already start to analyse and find out how many road-kilometres are open to traffic and on how many road-kilometres traffic has not been verified, e.g.

SELECT color, sum(geography::STGeomFromWKB(GEOM.STAsBinary(), 4326).STLength())/1000 as [1903] from JapanRoads0319 group by color

If we keep doing this with each of the daily data updates the results allow us to create graphs showing the improvements over time.


Tile Rendering and Caching

The tile rendering and cache handling is implemented in a Generic Web Handler (SqlTileServer.ashx). When you overlay a tile layer on top of Bing Maps the control will send for each tile in the current map view a HTTP-GET-Request to a virtual directory or in our case a web service. The request passes the quadkey of the tile as a parameter. In our service we will need to

  • determine if we have already cached the tile and if so retrieve it from the cache and return it to the map
  • If we haven’t cached the tile
    • determine the bounding box of the tile. This is where we use a couple of functions as listed in this article.
    • query the database and find all spatial objects that intersect this bounding box.
    • create a PNG image
    • write this image to cache and also
    • return the image to the map

In order to keep the communication between SQL Azure and the web service efficient we transport the data as binary spatial data types. This requires then of course that we decode the binary data in the web service. Fortunately the SQL Server Spatial team provides us with the means to do that. As part of the SQL Server 2008 R2 Feature Pack you will find the “Microsoft System CLR Types for SQL Server 2008 R2” which include the spatial data types and spatial functions and can be integrated in .NET applications and services even if you don’t have SQL Server installed.


Note: The package is available in 32bit, 64bit and Itanium versions. Windows Azure is based on Windows Server 2008 R2 64bit. If you want to deploy your solution on Windows Azure make sure you use the 64bit version of the “Microsoft System CLR Types for SQL Server 2008 R2”.

In the database we have a stored procedure that executes our spatial query.

CREATE PROCEDURE [dbo].[GetJapanRoads]
@nwLon nvarchar(10),
@nwLat nvarchar(10),
@seLon nvarchar(10),
@seLat nvarchar(10)
DECLARE @bbox geometry;
SET @bbox = geometry::STPolyFromText('POLYGON(('+@nwLon+' '+@nwLat+', '+@nwLon+' '+@seLat+', '+@seLon+' '+@seLat+', '+@seLon+' '+@nwLat+', '+@nwLon+' '+@nwLat+'))', 4326);
FROM JapanRoads0319
WHERE (GEOM.STIntersects(@bbox) = 1)

In order to convert geographic coordinates into pixel coordinates for the Bing Maps Tile System we use a couple of functions as listed in this article.

Since we want to cache the tiles in the Windows Azure Blob Storage we will also need to add references to the assemblies

  • Microsoft.WindowsAzure.ServiceRuntime and
  • Microsoft.WindowsAzure.StorageClient

Both assemblies are part of the Windows Azure SDK and the Windows Azure Tools for Microsoft Visual Studio, which includes the Windows Azure SDK.

Once we have the stored procedure in SQL Azure, the 3 assemblies (2 for Azure, 1 for SQL Server Spatial) and the helper functions for Bing Maps in place the code for the tile rendering looks like this.

Imports System.Web
Imports System.Web.Services
Imports Microsoft.SqlServer.Types
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.IO
Imports System.Drawing.Drawing2D
Imports Microsoft.WindowsAzure.StorageClient
Imports Microsoft.WindowsAzure
Imports Microsoft.WindowsAzure.ServiceRuntime
Imports System.Net

Public Class SqlTileServer
Implements System.Web.IHttpHandler

Public quadkey As String
Public lvl As Integer
Public tileX As Integer
Public tileY As Integer
Public nwX As Integer
Public nwY As Integer
Public nwLon As Double
Public nwLat As Double
Public seLon As Double
Public seLat As Double
Public myPixelX As Integer
Public myPixelY As Integer
Public myBaseUri As String = http://YOUR_WINDOWS_AZURE_ACCOUNT.blob.core.windows.net/YOUR_WINDOWS_AZURE_CONTAINER
Public myBlobUri As String
Public isCached As Boolean = False

Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
'Fetch URL-Parameters
quadkey = context.Request.Params("quadkey")

'Determine Zoom-Level
lvl = quadkey.Length

'Is file cached?
myBlobUri = myBaseUriCDN + "/" + quadkey + ".png"

Dim req As WebRequest = WebRequest.Create(myBlobUri)
Dim response As HttpWebResponse = DirectCast(req.GetResponse(), HttpWebRe-sponse)
Dim myImage As New Bit-map(System.Drawing.Image.FromStream(response.GetResponseStream))
isCached = True
WritePngToStream(myImage, context.Response.OutputStream)
Catch ex As Exception
isCached = False
'Get TileXY-Coordinates
QuadKeyToTileXY(quadkey, tileX, tileY, lvl)

'Get PixelXY of the North-West Corner of the tile
TileXYToPixelXY(tileX, tileY, nwX, nwY)

'Get Latitude and Longitude of the North-West Corner
PixelXYToLatLong(nwX, nwY, lvl, nwLat, nwLon)
PixelXYToLatLong(nwX + 256, nwY + 256, lvl, seLat, seLon)

'Retrieve Database Setting from web.config
Dim settings As ConnectionStringSettings = ConfigurationManag-er.ConnectionStrings("azure")

'Open a connection to the database
Dim myConn As New SqlConnection(settings.ConnectionString)
Dim cmd As New SqlCommand()

'Set SQL Parameters
cmd.Connection = myConn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("nwLon", nwLon))
cmd.Parameters.Add(New SqlParameter("nwLat", nwLat))
cmd.Parameters.Add(New SqlParameter("seLon", seLon))
cmd.Parameters.Add(New SqlParameter("seLat", seLat))

'Specify the stored procedure name as the command text
cmd.CommandText = "GetJapanRoads"

'Create a new image
Dim myBitmap As New Bitmap(256, 256, Imaging.PixelFormat.Format32bppArgb)

'Read data and draw image
Dim myReader As SqlDataReader = cmd.ExecuteReader()
While myReader.Read()
'Get the Geometry
Dim myGeom As SqlGeometry = myReader(0)

'Deteremine the number of Geometries in the object
Dim numGeom As Integer = myGeom.STNumGeometries
For j = 1 To numGeom
Dim curGeom As SqlGeometry = myGeom.STGeometryN(j)
Dim numPoints As Integer = curGeom.STNumPoints
Dim myPointArray(numPoints - 1) As Point
For i = 1 To numPoints
Dim myPoint As SqlGeometry = curGeom.STPointN(i)
Dim myLon As Double = myPoint.STX
Dim myLat As Double = myPoint.STY
LatLongToPixelXY(myLat, myLon, lvl, myPixelX, myPixelY)
myPointArray(i - 1) = New Point(myPixelX - nwX, myPixelY - nwY)

'Draw the Graphics
Dim g As Graphics = Graphics.FromImage(myBitmap)
Dim myBrush As New SolidBrush(Color.Transparent)
Dim myPen As Pen
Select Case myReader(1).ToString
Case "#blue"
myPen = New Pen(Brushes.Green)
Case Else
myPen = New Pen(Brushes.Gray)
End Select
myPen.Width = 3
g.DrawLines(myPen, myPointArray)
End While
WritePngToStream(myBitmap, context.Response.OutputStream)
End Try
End Sub

Private Sub WritePngToStream(ByVal image As Bitmap, ByVal outStream As Stream)
Dim writeStream As New MemoryStream()
image.Save(writeStream, Imaging.ImageFormat.Png)
If isCached = False Then
writeStream.Seek(0, SeekOrigin.Begin)
Dim MyBlob = Me.GetContainer().GetBlobReference(quadkey + ".png")
MyBlob.Properties.ContentType = "image/x-png"
End If
End Sub

We also need a helper function to access the Windows Azure Blob Storage.

Private Sub EnsureContainerExists()
Dim container = GetContainer()
Dim permissions = container.GetPermissions()
permissions.PublicAccess = BlobContainerPublicAccessType.Container
End Sub

Private Function GetContainer() As CloudBlobContainer
' Get a handle on account, create a blob storage client and get container proxy
Dim account = CloudStorageAc-count.Parse("DefaultEndpointsProtocol=http;AccountName=YOUR_WINDOWS_AZURE_ACCOUNT_NAME;AccountKey=YOUR_WINDOWS_AZURE_ACCOUNT_KEY")
Dim client = account.CreateCloudBlobClient()
Return client.GetContainerReference("YOUR_CONTAINER")
End Function

Calling the Service from Bing Maps

Adding a tile layer in Bing Maps is very simple. Below you find the source code for a complete web page that calls our service.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<title>Bing Maps - v7.0</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=7.0&mkt=ja-JP"></script>
<script type="text/javascript">
var map = null;
var MM = Microsoft.Maps;
function GetMap() {
map = new MM.Map(document.getElementById("myMap"), {
credentials: "YOUR_BING_MAPS_KEY",
center: new MM.Location(38.80131086471941, 139.5055539160967),
mapTypeId: "r",
zoom: 8,
enableClickableLogo: false,
enableSearchLogo: false

new MM.TileLayer({
mercator: new MM.TileSource({
uriConstructor: http://YOUR_WINDOWS_AZURE_ACCOUNT.cloudapp.net/BM-AJ-Japan/SqlTileServer.ashx?quadkey={quadkey}
}), opacity: .7
<body onload="GetMap();">
<div id='myMap' style="position:absolute; top:0px; left:0px; width:100%; height:100%;"></div><br />

And that’s already it. In a very short time we were able to develop a service and deploy it as a scalable and highly available service in the cloud.

One final tip: If you also plan to use this service from other Silverlight applications in domains outside your Windows Azure environment you will need to publish a ClientAccessPolicy.xml file to the root of your Windows Azure hosted service. In our case that was necessary since we will access the service not only from the website itself but also from a Bing Map App.

About these ads
This entry was posted in AJAX, Bing Maps, Spatial, SQL Azure, Windows Azure and tagged , , , , , , . Bookmark the permalink.

4 Responses to Dynamic Tile-Layers with Windows Azure and SQL Azure

  1. Brian Norman says:

    I assume with the new azure cdn support for webroles you could flip this round and actually make the initial request from the client to the cdn direct, which if not cached there would make the request to you webrole’s SqlTileServer.ashx (if you moved it to the dir “CDN” and then cache the tile before returning it. Would save on a few round trips and reduce some latency


  2. pascal says:

    nice work, Johannes!

    I’ve realized a similar website with OpenStreetMap data, you can find my blog post here: http://neis-one.org/2011/03/japan-osm/ and the OSM Japan Editing Stats here: http://neis-one.org/2011/03/edit-stats-osm-japan/

  3. Pingback: Sites Tuesday, 22 March 2011-Bing Maps Japan imagery before/after-Expression Web 4 SP1-IE9’s tracking protection-Printing in Office-Motor sport circuit guide-Kinect roundup-More « webDotWiz talks Windows Live

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