Creating a Bing Map Tile Server from Windows Azure
[This article was contributed by the SQL Azure team.]
In this example application, I am going to build a Bing Maps tile server using Windows Azure that draws push pins on the tiles based on coordinates stored as spatial data in SQL Azure. Bing Maps allows you to draw over their maps with custom tiles; these tiles can be generated dynamically from a Windows Azure server. The overlay that this example produces corresponds to latitude and longitude points stored in a SQL Azure database.
There is another option for drawing pushpins, using JavaScript the Bing Map Ajax Controls allow you draw pushpins. However, after about 200 push pins on a map, the performance slows when using JavaScript. So if you have a lot of pushpins, draw them server side using a tile server.
Windows Azure is a perfect solution to use when creating a tile server, since it can scale to meet the traffic needs of the web site serving the map. SQL Azure is a perfect match to hold the latitude and longitude points that we want to draw as push pins because of the spatial data type support.
The tricky part of writing about this application is assembling all the knowledge about these disjointed technologies in the space of a blog post. Because of this, I am going to be off linking to articles about Bing Maps, Windows Azure, and writing mostly about SQL Azure.
Bing Maps
In order to embed a Bing Map in your web page you need to get a Bing Map Developer Account and Bing mapping keys, You can do that here at the Bing Map Portal. Once you have your keys, read this article about getting started with the Bing Maps AJAX control. This is what I used in the example to embed the Bing Map in my web page. Since we are going to build our own Bing Map Tile Server, you will want to read up on how Bing organizes tiles, and the JavaScript you need to add to reference a tile server from an article entitled: Building Your Own Tile Server. Or you can just cut and paste my HTML for a simple embedded map looks like this:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="https://www.w3.org/1999/xhtml">
<head>
<title>My Virtual Earth</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="https://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3">
</script>
<script type="text/javascript">
var map = null;
function OnPageLoad() {
map = new VEMap('myMap');
map.SetCredentials
("...");
map.LoadMap(new VELatLong(0, 0), 1, 'r', false);
var opacity = 0.9;
var tileSourceSpec = new VETileSourceSpecification(
"sqltiles",
"https://127.0.0.1:81/tile.aspx?%4");
tileSourceSpec.NumServers = 1;
tileSourceSpec.MinZoom = 1;
tileSourceSpec.MaxZoom = 18;
tileSourceSpec.Opacity = opacity;
tileSourceSpec.ZIndex = 100;
map.AddTileLayer(tileSourceSpec, true);
map.ShowTileLayer("sqltiles");
}
</script>
</head>
<body onload="OnPageLoad();">
<div id='myMap' style="position: relative; width: 512px; height: 512px;">
</div>
</body>
</html>
Notice that I am using my local server for the tile server. This allows me to quickly test the tile server in the Windows Azure development fabric without having to deploy it to Windows Azure every time I make a programming change to the ASP.NET code.
Windows Azure
We are going to use a Windows Azure web role to serve our titles to the Bing Map Ajax Control. If you haven’t created a web role yet, you need to read: Walkthrough: Create Your First Windows Azure Local Application. When you are ready to deploy you will need to visit the Windows Azure portal and create a new Hosted Service to serve your tiles to the Internet. However, for now you can serve your tiles from the Windows Azure Development Fabric; it is installed when you install the Windows Azure Tools. The download can be found here.
Once you have created your web role in your cloud project, add an ASP.NET page entitled tile.aspx. Even though an ASP.NET page typically serves HTML, in this case we are going to serve an image as a PNG from the ASP.NET page. The Bing Map AJAX Control calls the tile.aspx page with the quadkey in the query string to get a tile (which is an image); this is done roughly 4 times with 4 different tile identifiers for every zoom level displayed. More about the quadkey can be found here. The example ASP.NET code uses the quadkey to figure out the bounding latitude/longitude of the tile. With this information we can make a query to SQL Azure for all the points that fall within the bounding latitude and longitude. The points are stored as a geography data type (POINT) in SQL Azure and we use a Spatial Intersection query to figure out which ones are inside the tile.
SQL Azure
Here is a script for the table that I created on SQL Azure for the example:
CREATE TABLE [dbo].[Points](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LatLong] [geography] NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PrimaryKey_2451402d-8789-4325-b36b-2cfe05df04bb] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
It is nothing fancy, just a primary key and a name, along with a geography data type to hold my latitude and longitude. I then inserted some data into the table like so:
INSERT INTO Points (Name, LatLong) VALUES
('Statue of Liberty',
geography::STPointFromText('POINT(-74.044563 40.689168)', 4326))
INSERT INTO Points (Name, LatLong) VALUES
('Eiffel Tower',
geography::STPointFromText('POINT(2.294694 48.858454)', 4326))
INSERT INTO Points (Name, LatLong) VALUES
('Leaning Tower of Pisa',
geography::STPointFromText('POINT(10.396604 43.72294)', 4326))
INSERT INTO Points (Name, LatLong) VALUES
('Great Pyramids of Giza',
geography::STPointFromText('POINT(31.134632 29.978989)', 4326))
INSERT INTO Points (Name, LatLong) VALUES
('Sydney Opera House',
geography::STPointFromText('POINT( 151.214967 -33.856651)', 4326))
INSERT INTO Points (Name, LatLong) VALUES
('Taj Mahal',
geography::STPointFromText('POINT(78.042042 27.175047)', 4326))
INSERT INTO Points (Name, LatLong) VALUES
('Colosseum',
geography::STPointFromText('POINT(41.890178 12.492378)', 4326))
The code is only designed to draw pinpoint graphics at points, it doesn’t handle the other shapes that the geography data type supports. In other words make sure to only insert POINTs into the LatLong column.
One thing to note is that SQL Azure geography data type takes latitude and longitudes in the WKT format which means that Longitudes are first and then Latitudes. In my C# code, I immediately retrieve the geography data type as a string, strip out the latitude and longitude and reverse it for my code.
Now that we have a table and data, the application needs to be able to query the data, here is the stored procedure I wrote:
CREATE PROC [dbo].[spInside](
@southWestLatitude float,
@southWestLongitude float,
@northEastLatitude float,
@northEastLongitude float)
AS
DECLARE @SearchRectangleString VARCHAR(MAX);
SET @SearchRectangleString = 'POLYGON((' +
CONVERT(varchar(max),@northEastLongitude) + ' ' +
CONVERT(varchar(max),@southWestLatitude) + ',' +
CONVERT(varchar(max),@northEastLongitude) + ' ' +
CONVERT(varchar(max),@northEastLatitude) + ',' +
CONVERT(varchar(max),@southWestLongitude) + ' ' +
CONVERT(varchar(max),@northEastLatitude) + ',' +
CONVERT(varchar(max),@southWestLongitude) + ' ' +
CONVERT(varchar(max),@southWestLatitude) + ',' +
CONVERT(varchar(max),@northEastLongitude) + ' ' +
CONVERT(varchar(max),@southWestLatitude) + '))'
DECLARE @SearchRectangle geography;
SET @SearchRectangle = geography::STPolyFromText(@SearchRectangleString, 4326)
SELECT CONVERT(varchar(max),LatLong) 'LatLong'
FROM Points
WHERE LatLong.STIntersects(@SearchRectangle) = 1
The query takes two corners of a box, and creates a rectangular polygon, then is queries to find all the latitude and longitudes that are in the Points table which fall within the polygon. There are a couple subtle things going on in the query, one of which is that the rectangle has to be drawn in a clock-wise order, and that the first point is the last point. The second thing is that the rectangle must only be in a single hemisphere, i.e. either East or West of the dateline, but not both and either North or South of the equator, but not both. The code determines if the rectangle (called the bounding box in the code) is a cross over and divides the box into multiple queries that equal the whole.
The Code
To my knowledge there is only a couple complete, free code examples of a Bing tile server on the Internet. I borrowed heavily from both Rob Blackwell’s “Roll Your Own Tile Server” and Joe Schwartz’s “Bing Maps Tile System”.
I had to make a few changes, one of which is to draw on more than one tile for a single push pin. The push pin graphic is both wide and tall and if you draw it on the edge of a tile, it will be cropped by the next tile over in the map. That means for push pins that land near the edges, you need to draw it partially on both tiles. To accomplish this you need to query the database for push pins that are slightly in your tile, as well as the ones in your tiles. The downloadable code does this.
Caching
The whole example application could benefit from caching. It is not likely that you will need to update the push pins in real time, which means that based on your application requirements, you could cache the tiles on both the server and the browser. Querying SQL Azure, drawing bitmaps in real-time for 4 tiles per page would be a lot of work for a high traffic site with lots of push pins. Better to cache the tiles to reduce the load on Windows Azure and SQL Azure; even caching them for just a few minutes would significantly reduce the workload.
Another technique to improve performance is not to draw every pin on the tile when there are a large number of pins. Instead come up with a cluster graphic that is drawn once when there are more than X numbers of pins on a tile. This would indicate to the user that they need to zoom in to see the individual pins. This technique would require that you query the database for a count of rows within the tile, to determine if you are need to use the cluster graphic.
You can read more about performance and load testing with Bing Maps here.
Summary
Find bugs? Have questions, concerns, comments? Post them below and we will try to address them.