Article by: 
John O'Brien.
Virtual Earth is an amazing platform to visualise location based data over a simple web connection. By combining the images provided by Microsoft’s platform with your own data, geo-coded to its real world location, you can create an engaging, interactive experience for your users. You can also visually represent vast sets of data in ways impossible to do otherwise.
SQL Server 2008 is scheduled for release early next year. This article gives a first impression on the CTP5 release, the first release available for preview by the public including the new spatial features of SQL Server 2008.
So what are these new features?
SQL 2008 provides two new data types, geometry and geography. The geometry type is for data on an arbitrary plane, that is, a flat surface with user defined bounds. The geography type is for data placed on the Earth. Points defined for the geography type are supplied in latitude and longitude. With this data type the curvature of the Earth is considered and measurements are accurate.
For Virtual Earth our data type is a simple choice - Geography.
There are a set of spatial functions that can be executed against these data types. In this introductory article I will only focus on one very applicable for Virtual Earth, STIntersects().
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((47.653 -122.358,
47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326);
SET @h = geography::STGeomFromText('POINT(47.656 -122.350)', 4326);
select @h.STIntersects(@g);
This function will allow me to find points within the bounds of the current map view. It must be noted that this function is designed to provide a highly accurate match of intersections, for this trivial example there is no performance gain but it paves the way for more complex examples.
Creating a Spatial Field
The geography data type is built into SQL2008, so it is as simple as adding a new field to an existing table or in this case we create a new table to store our basic Australian postcode data. As used in previous articles, this data is a list of Australian postcodes, their name and their Latitude and Longitude.
CREATE TABLE [dbo].[Postcodes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Postcode] [nvarchar](4) NOT NULL,
[Title] [nvarchar](200) NULL,
[Location] [geography] NOT NULL
Importing Data
Before we can query this table we need some data. I have this data stored in an XML format, so with an insert stored procedure called from a simple console application we can query this file, and insert the records.
CREATE PROCEDURE dbo.InsertPostcode
@Postcode nvarchar(4),
@Title nvarchar(200),
@WKT text
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Postcodes]
([Postcode]
,[Title]
,[Location])
VALUES
(@Postcode,
@Title,
geography::STPointFromText(@WKT, 4326)
)
END
GO
Integration with Virtual Earth
Virtual Earth is our presentation layer. It is coded in JavaScript and operates in the client browser. Although we could produce a static data file in some XML format here I will show how to build a middle tier between our data and presentation.
I will use Visual Studio 2008 RC and the new .net 3.5 runtime to do this. An AJAX enabled web service for communication with my client JavaScript presentation tier is now integrated into the platform.
I’ll begin with my client side JavaScript code for Virtual Earth and work backwards to the actual database query itself.
Default.aspx
We need a simple XHTML template to send to the client, we set the correct doctype (XHTML), meta elements (UTF8) and add a script manager to reference our JavaScript files (for demonstration purposes I’ll keep them as separate files) and the Virtual Earth Version 6 file. WE make a simple Div, "myMap", to hold our map.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www./TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www./1999/xhtml">
<head>
<title>SQL 2008 Virtual Earth Part 1</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="myScriptManager" runat="server">
<Scripts>
<asp:ScriptReference Path="http://dev./mapcontrol/mapcontrol.ashx?v=6" />
<asp:ScriptReference Path="~/scripts/Default.aspx.js" />
<asp:ScriptReference Path="~/scripts/SoulSolutions.Demo.Map.js" />
<asp:ScriptReference Path="~/scripts/SoulSolutions.Demo.MapArgs.js" />
</Scripts>
<Services>
<asp:ServiceReference Path="~/services/MapService.asmx" />
</Services>
</asp:ScriptManager>
<div id="myMap" style="position: relative; width: 980px; height: 600px; overflow:hidden"></div>
</form>
</body>
</html>
Default.aspx.js
My JavaScript used in this demonstration is built using Object Orientated (OO) concepts. This file is essentially the "code behind" for our template page. It will create event handlers for the page and create two objects, the "mapArgs" and the demo "map". This OO approach to JavaScript is worth your time to investigate further. For enterprise level applications you are going to want to encapsulate your client side JavaScript logic into classes. The benefits include:
- more readable and understandable code,
- better code reuse,
- safe variable name and namespaces, and
- it’s easier to work as a team and with source control (store classes in separate .js files, combine and compress as a deployment step)
//the map object
var map = null;
//load map
function Page_Load() {
var mapArgs = new SoulSolutions.Demo.MapArgs("myMap", new VELatLong(-27.5, 137), 4, VEMapStyle.Hybrid, false, VEMapMode.Mode2D, VEDistanceUnit.Kilometers);
map = new SoulSolutions.Demo.Map(SoulSolutions.Demo.MapService, mapArgs);
}
//Clean up all objects
function Page_Unload() {
if (map!=null) {
map.Dispose();
map = null;
}
}
//set page event handlers
if (window.attachEvent) {
window.attachEvent("onload", Page_Load);
window.attachEvent("onunload", Page_Unload);
} else {
window.addEventListener("DOMContentLoaded", Page_Load, false);
window.addEventListener("unload", Page_Unload, false);
}
if (typeof(Sys) !== "undefined") Sys.Application.notifyScriptLoaded();
MapArgs.js
This is a simple class I use to set all those configurable settings for VE in once place.
Type.registerNamespace("SoulSolutions.Demo");
SoulSolutions.Demo.MapArgs = function(divID, center, zoomlevel, style, fixed, mode, scale) {
/// <summary>
/// Virtual Earth Arguments
/// </summary>
/// <param name="divID">The ID of the div to create the map</param>
/// <param name="center">The centre of the initial map view - type VELatLong</param>
/// <param name="zoomlevel">The initial zoom level 1-19</param>
/// <param name="style">The map style - type VEMapStyle</param>
/// <param name="fixed">Is the map fixed = not interactive</param>
/// <param name="mode">The map mode - type VEMapMode</param>
/// <param name="scale">The map scale - type VEDistanceUnit </param>
this.DivID = divID;
this.Center = center;
this.Zoomlevel = zoomlevel;
this.Style = style;
this.Fixed = fixed;
this.Scale = scale;
}
SoulSolutions.Demo.MapArgs.registerClass('SoulSolutions.Demo.MapArgs');
if (typeof(Sys) !== "undefined") Sys.Application.notifyScriptLoaded();
Map.js
Ok, this is where all the excitement happens. We create our VE map, set all the properties we defined earlier and bind to the map’s "onchangeview" event.
_init: function() {
/// <summary>
/// Initialises the Map.
/// </summary>
//setup map
this._map = new VEMap(this._mapArgs.DivID);
this._map.LoadMap(this._mapArgs.Center,this._mapArgs.Zoomlevel,this._mapArgs.Style,this._mapArgs.Fixed,this._mapArgs.Mode);
this._map.SetScaleBarDistanceUnit(this._mapArgs.Scale);
this._layer = new VEShapeLayer();
this._map.AddShapeLayer(this._layer);
//setup the function to get new data whenever the map changes
this._OnMapChangeDelegate = Function.createDelegate(this, this._GetPinData);
this._map.AttachEvent("onchangeview", this._OnMapChangeDelegate);
//get the data for the default view
this._GetPinData();
},
Why? We will be demonstrating only retrieving the data we need for the current map view. In our data tier we will also limit the total number of points to ensure our performance is satisfactory over our internet connection. This demo does not apply any clustering technology, pins will overlap and at higher altitude zoom levels, some will simply never be rendered.
Essentially we make a call to our web service passing the current bounds of the map, this varies in 2D, 3D and birds-eye. Here we hit our first limitation of the new platform. To supply a Polygon in the Well Known Text (WKT) format it must not only follow strict layout but also:
- Must have a end point supplied matching the start point
- Must be supplied in counter-clockwise order
- Must not exceed area of half the Earth.
We apply some logic to generate a possible two polygons to represent the map bounds based on these conditions. Note I have no implemented this for 3D mode as we would need to account for rotation, feel free to extend this functionality yourself.
_GetPinData: function() {
/// <summary>
/// Get the latest map data from the webservice.
/// </summary>
var WKTBoundsEast = "";
var WKTBoundsWest = "";
var viewrect;
if (this._map.GetMapStyle() == VEMapStyle.Birdseye) {
var be = this._map.GetBirdseyeScene();
viewrect = be.GetBoundingRectangle();
}else {
viewrect = this._map.GetMapView();
}
if (this._map.GetMapMode() == VEMapMode.Mode3D) {
//we have four corners to use, to support view greater then half earth we would need to impliment logic as per 2D.
WKTBoundsEast = "POLYGON((" + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomLeftLatLong.Latitude + " " + viewrect.BottomLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopRightLatLong.Latitude + " " + viewrect.TopRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + "))";
}else {
//only have two points to use. Need to break into polygon no greater then half the Earth.
if (viewrect.TopLeftLatLong.Longitude < 0) {
if (viewrect.BottomRightLatLong.Longitude < 0) {
WKTBoundsWest = "POLYGON((" + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + "))";
}else {
WKTBoundsWest = "POLYGON((" + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + 0 + ", " + viewrect.TopLeftLatLong.Latitude + " " + 0 + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + "))";
}
}
if (viewrect.BottomRightLatLong.Longitude > 0) {
if (viewrect.TopLeftLatLong.Longitude > 0) {
WKTBoundsEast = "POLYGON((" + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.TopLeftLatLong.Longitude + "))";
}else {
WKTBoundsEast = "POLYGON((" + viewrect.TopLeftLatLong.Latitude + " " + 0 + ", " + viewrect.BottomRightLatLong.Latitude + " " + 0 + ", " + viewrect.BottomRightLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + viewrect.BottomRightLatLong.Longitude + ", " + viewrect.TopLeftLatLong.Latitude + " " + 0 + "))";
}
}
}
//call webservice
this._service.GetMapData(WKTBoundsEast, WKTBoundsWest, Function.createDelegate(this, this._OnMapDataSucceeded), Function.createDelegate(this, this._OnFailed));
},
When the data is received successfully from the service it is supplied as a JSON object. The beauty of this format is it is more compact then XML and can be directly accessed as a native JavaScript object reflecting our .Net class. Using the bulk addshape() method we add the pins to the map using a custom pushpin.
_OnMapDataSucceeded: function(results) {
/// <summary>
/// Receive data for map.
/// </summary>
/// <param name="result">The webservice result object - JSON</param>
//delete old data
this._layer.DeleteAllShapes();
//add new pins
var newShapes = new Array();
for(x = 0; x < results.length; x++) {
var newShape = new VEShape(VEShapeType.Pushpin, new VELatLong(results[x].Latitude, results[x].Longitude));
newShape.SetTitle(results[x].Title);
newShape.SetDescription(results[x].Description);
newShape.SetCustomIcon("pin.png");
newShapes.push(newShape);
}
this._layer.AddShape(newShapes);
},
MapService.cs
Our web service consists of one method calling our business logic class to get the data for the current view.
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public List<Pushpin> GetMapData(string WKTBoundsEast, string WKTBoundsWest)
{
return PostcodeLogic.GetPostcodesWithinBounds(WKTBoundsEast, WKTBoundsWest);
}
PostCodeLogic.cs
This static class will call our stored procedure passing the bounds in the Well Known Text (WKT) format to be parsed by the database into a geography object. It will loop through the data and populate our Pushpin class consisting of a title, description, latitude and longitude. We make use of the new constructor and class syntax in .Net 3.5.
For the simple "POINT" type the values are simple to retrieve. In future articles I will show how to deal with the complex types like POLYGON, POLYLINE and MULTIPOLYGON
To keep this demonstration simple I will connect directly to the database in this class. You can extend this further using your preferred data access model. For example, a factory pattern where connections and commands are pooled and reused for performance. This is not the focus of this article.
public static List<Pushpin> GetPostcodesWithinBounds(string WKTBoundsEast, string WKTBoundsWest)
{
List<Pushpin> items = new List<Pushpin>();
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand cmd = new SqlCommand("dbo.GetPostcodesSwitch", sqlConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@WKTBoundsEast", WKTBoundsEast);
cmd.Parameters.AddWithValue("@WKTBoundsWest", WKTBoundsWest);
SqlDataReader geomRdr = cmd.ExecuteReader();
while (geomRdr.Read() == true)
{
SqlGeography loc = (SqlGeography)geomRdr["Location"];
items.Add(new Pushpin {
Latitude=(double)loc.Lat,
Longitude=(double)loc.Long,
Title=(string)geomRdr["Title"],
Description=(string)geomRdr["Description"] });
}
geomRdr.Close();
}
sqlConn.Close();
}
return items;
}
dbo.GetPostCodesByBound
The moment you have been waiting for and it doesn’t disappoint!
SELECT TOP 200
[Postcode] as Title
, [Title] as Description
, [Location]
FROM
[dbo].[Postcodes]
WHERE
[Location].STIntersects(geography::STPolyFromText(@WKTBounds, 4326)) = 1
The stored procedure takes a single bounds string supplied in WKT format and queries our postcode table for the first set of records within those bounds. The syntax is super simple and very effective.
Due the limitation mentioned earlier regarding polygons only not exceeding half the area of the Earth we need two more Stored Procedures:
dbo.GetPostCodesByBounds - Essentially the same but will check two bounding polygons as part of a where clause.
dbo.GetPostcodesSwitch - A conditional stored procedure that will determine based on the two potentially empty bounds strings which stored procedure to call. I use this method to keep the code modular and to allow the execution plans to be cached and optimised. I welcome other ways to achieve what we need here.
You are now ready to run the code. It works, but we are missing one very important performance feature.
Spatial Index
Isaac K. Kunen from Microsoft calls it an "adaptive multi-level grid" and it consists of a similar concept to that of Virtual Earth tiles. That is, it uses a configurable grid of 16-256 cells over the world, for each cell it then produces a 16-256 cell grid, and so on, for a limited number of levels (4). The concept is that complex shapes can be very quickly eliminated as possible valid values based on this grid alone.
To apply the index we run the following T-SQL:
CREATE SPATIAL INDEX SIndx_Postcodes_Location_col1
ON dbo.Postcodes(Location);
Conclusion
In this very first look at SQL 2008 Spatial and Virtual Earth we have been introduced to the new data types and have seen how to create our first table. We have imported some data and written our first spatial query. We have seen our first limiting factor of this technology and a work around has been provided. Additionally I have supplied you with a simple, clean, framework to provide data from your database to Virtual Earth.
I challenge you to build on these concepts, import you own data and visualize it on Virtual Earth. In future articles I will explore handling Polygons within SQL Server, both stored and created through various spatial functions. Importantly I will show you how to handle these within your logic code to create a structure compatible with Virtual Earth in a clean structure.
