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; ALTER TABLE JapanRoads0319 ADD CONSTRAINT PK_JapanRoads0319 PRIMARY KEY CLUSTERED (MyID); 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  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) AS BEGIN DECLARE @bbox geometry; SET @bbox = geometry::STPolyFromText('POLYGON(('+@nwLon+' '+@nwLat+', '+@nwLon+' '+@seLat+', '+@seLon+' '+@seLat+', '+@seLon+' '+@nwLat+', '+@nwLon+' '+@nwLat+'))', 4326); SELECT GEOM, Color FROM JapanRoads0319 WHERE (GEOM.STIntersects(@bbox) = 1) END;
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
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 myBaseUriCDN As String = http://YOUR_WINDOWS_AZURE_CDN_TOKEN.vo.msecnd.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" Try 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) myConn.Open() 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) Next '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) Next End While myReader.Close() myConn.Close() 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" MyBlob.UploadFromStream(writeStream) End If writeStream.WriteTo(outStream) image.Dispose() End Sub
We also need a helper function to access the Windows Azure Blob Storage.
Private Sub EnsureContainerExists() Dim container = GetContainer() container.CreateIfNotExist() Dim permissions = container.GetPermissions() permissions.PublicAccess = BlobContainerPublicAccessType.Container container.SetPermissions(permissions) 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.
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.