FindNearRoute with Virtual Earth 6.1 and SQL Server 2008

Just last night we released a minor update to Virtual Earth. Besides the fact that we can now generate the tokens for the Customer Identification and thus the transaction counting not only in the production- but also in the development environment we return now the complete route-geometry when you calculate a route. This feature is considered to be a ‘premium feature’ and is only available to enterprise customers who use the customer identification

Having access to the route-geometry allows us for instance to have multiple routes on a map…

image

…or together with a spatial-enabled database such as SQL Server 2008 to implement a feature which returns points of interest in a buffer around a route. Let’s have a look at the latter.

Introduction

While we do have a FindNearRoute-feature in MapPoint Web Service (MWS) there is no such thing in Virtual Earth (VE) yet. To use this feature in MWS all the Points of Interest (POI) have to uploaded to a so called Custom Data Store (CDS) and then the calculation is done directly within the Microsoft Data Centres.

Since SQL Server 2008 has many build-in spatial functions and since Virtual Earth version 6.1 exposes now the complete geometry of the route we can build such a feature ourselves.

In previous postings I have introduced SQL Server 2008 and its integration with Virtual Earth so I won’t go into the details here.

Concept

The idea is to calculate the route with the VEMap.GetDirections-method. The VERoute-object which is returned will have several properties. One of these is the ShapePoints, basically an array of latitudes and longitudes. We will process this array and convert it into Well Known Text (WKT) of type LINESTRING which can later be used for the spatial-relationship query within SQL Server 2008. The WKT is then send along with the size of the buffer via AJAX-call to a web handler. The web handler executes now a stored procedure which calculates a buffer around our route and thereafter determines all POI within this buffer. The result will be processed through a string builder and returned to the client as JavaScript.

image

Requirements

  • Virtual Earth version 6.1
  • A Virtual Earth Platform production or developer account. If you don’t have such an account you can sign-up to a free developer account here.
  • SQL Server 2008 CTP5 or higher

For my example I write the code for the web handler in .NET so we will need additionally:

  • Visual Studio with the integrated web server for development and testing or an Internet Information Server with ASP.NET enabled

Database

Our base table has a structure like this:

CREATE TABLE [MyPOI]
(
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [Name] [nvarchar](255) NULL,
    [ID] [int] NOT NULL,
  CONSTRAINT [PK_MyPOI] PRIMARY KEY CLUSTERED 
  (
    [ID]
  )
)

In my sample table there are 1,767 POI. In the first step we will add a column of type geography so that we can later use our spatial functions in SQL Server 2008.

ALTER TABLE MyPOI ADD GEOM geography NULL

Now we update the table to compute the geographies from the latitudes and longitudes.

UPDATE MyPOI 
  SET GEOM = 
    geography::STPointFromText('POINT(' + STR(Latitude, 20, 16) + ' ' + STR(Longitude, 20, 16) + ')', 4326)

And the we create a spatial index for better performance:

CREATE SPATIAL INDEX [SI_MyPOI] ON [MyPOI] 
(
      [GEOM]
)
USING  GEOGRAPHY_GRID 
WITH 
(
      GRIDS = (
        LEVEL_1=MEDIUM,
        LEVEL_2=MEDIUM,
        LEVEL_3=HIGH,
        LEVEL_4=HIGH), 
      CELLS_PER_OBJECT = 16
)

The most interesting part comes now. We create a stored procedure which will create a buffer of variable size around the route and determine the POI in it:

CREATE PROCEDURE [FindNearRoute] @myGEOM nvarchar(MAX), @myBuffer int
AS

DECLARE @myRoute geography;
SET @myRoute = @myGEOM;

--Create the Buffer
DECLARE @SearchArea geography;
SET @SearchArea = @myRoute.STBuffer(@myBuffer);

--Return all POI in the search area
SELECT    Latitude, Longitude, Name
FROM MyPOI
WHERE (@SearchArea.STIntersects(GEOM)) = 1 OPTION (LOOP JOIN)

The optimizer hint at the end is to overcome an performance issue in the SQL Server 2008 CTP6 which was documented by Isaac Kuhnen here.

Well that’s it on the database side we can move on to our web application.

Web Application

In our web application we have a ASP.NET-page with some text-boxes for start- and end-point as well as the size of the buffer in which we want to search. There are also 2 buttons: one to calculate the route and one to get in the POI in the buffer around this route. In the Page-Load event we generate our token as described in this article about customer identification.

image

The framework of the application is pretty much standard with 2 exceptions: We declare a global variable for the route-geometry

var myRouteGeom = null;

and use a callback-function after we calculate the route to populate this variable:

function GetRoute()
{
    var options = new VERouteOptions;
    options.RouteCallback = RouteCallback;
    var locations = new Array(document.getElementById('txtStart').value, document.getElementById('txtEnd').value);
    map.GetDirections(locations, options);
}

function RouteCallback(route)
{
    myRouteGeom = route.ShapePoints;
}

When we click on the button ‘Find Near Route’ we will execute the following AJAX-call which builds our WKT LINESTRING and sends it together with the buffer-size to our web handler. Since the WKT can become quite long we send it as HTTP-POST request.

function FindNearRoute()
{
    //Prepare WKT for the Linestring
    var myPoints = "";
    for (var i=0; i<myRouteGeom.length; i++)
    {
        myPoints+=myRouteGeom[i].Latitude + " " + myRouteGeom[i].Longitude  + ", ";
    }
    myPoints = myPoints.substr(0, myPoints.length - 2);
    var myGEOM = "LINESTRING (" + myPoints + ")";
    //Get the appropriate XMLHTTP object for the browser
    var xmlhttp = GetXmlHttp();
    
    //if we have a valid XMLHTTP object
    if (xmlhttp)
    {
        xmlhttp.open("POST", "./FindNearRoute.ashx", false);
        xmlhttp.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
        //set the callback
        xmlhttp.onreadystatechange = function()
        {
            if (xmlhttp.readystate ==4) //4 is a success
            {
                //server code creates JavaScript "on the fly"
                //execute using eval()
                var result = xmlhttp.responseText
                eval(result);
            }
        }
        xmlhttp.send("myGEOM=" + myGEOM + "&myBuffer=" + document.getElementById('txtBuffer').value);
    }
}

In the web handler we will fetch the WKT and the buffer-size from the URL-parameter, set up the database connection which we defined in the web.config and execute the stored procedure. While we loop through the data reader we create our JavaScript and send it back in the response to our AJAX-call.

'set culture to en-UK to avoid potential problems with decimal-separators
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

'Fetch URL-parameter
Dim myGEOM As String = context.Request.Params("myGEOM")
Dim myBuffer As String = context.Request.Params("myBuffer")

'Prepare database
Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("HannesPOI")

Dim myConn As New SqlConnection(settings.ConnectionString)
myConn.Open()

Dim myCMD As New SqlCommand()
myCMD.CommandTimeout = 300
'Set SQL Parameters
myCMD.Connection = myConn
myCMD.CommandType = Data.CommandType.StoredProcedure
myCMD.Parameters.Add(New SqlParameter("@myGEOM", myGEOM))
myCMD.Parameters.Add(New SqlParameter("@myBuffer", myBuffer))

'Specify the stored procedure name as the command text
myCMD.CommandText = "FindNearRoute"
       
Dim sb As StringBuilder = New StringBuilder
sb.Append("var myPOIArray = new Array();")
Dim myPins As String = ""
Dim i As Integer = 0
Dim myReader As SqlDataReader = myCMD.ExecuteReader()
While myReader.Read()
    myPins = myPins + _
        "var shape" + i.ToString + "=new VEShape(VEShapeType.Pushpin, new VELatLong(" + myReader(0).ToString + ", " + myReader(1).ToString + "));" + _
        "shape" + i.ToString + ".SetCustomIcon('IMG/blue.png');" + _
        "shape" + i.ToString + ".SetTitle(" + """" + myReader(2).ToString + """" + ");"
    myPins = myPins + "myPOIArray.push(shape" + i.ToString + ");"
    i = i + 1
End While
sb.Append(myPins)
sb.Append("slFNR.AddShape(myPOIArray);")

myReader.Close()
myConn.Close()

context.Response.Write(sb.ToString())

Result

Traditionally you would retrieve all results within the current map view. For my sample dataset and a short route from Reading, UK to Oxford, UK this would return 61 POI in a relatively short time of just 0.2 seconds.

image

With our FindNearRoute-method we need more time for the database query but we can filter the result down to the 3 points within 1 kilometre of our route.

image

Performance Considerations

  • For simple spatial-relationship queries where we only want to find points within a bounding rectangle (e.g. our map-window), standard SQL is faster than spatial SQL because we basically execute a range-query and the B-tree index within SQL Server is already very good. On my laptop I can easily optimise my database so that I get a response on a table with over 6 million entries in ~10 milliseconds.
  • For more complex spatial-relationship queries like "find my POI within a buffer around a route" spatial SQL is unbeatable.
  • Since CTP 6 SQL Server 2008 has a new function: ‘Filter’. This function uses approximations based on index-only intersects and Ed Katibah has discussed this function on his blog. While this function can achieve significant performance improvements for more complex queries, it is to be considered that it is always an approximation and thus not as accurate as the STIntersects-method.

The complete source code including sample database is available here. Please note that you have to enter your credentials for the Virtual Earth Platform Account in the Default.asp.vb in the following line:

commonService.Credentials = New System.Net.NetworkCredential("YOUR VE-ID", "YOUR VE-PWD")

Advertisements
This entry was posted in Virtual Earth. Bookmark the permalink.

5 Responses to FindNearRoute with Virtual Earth 6.1 and SQL Server 2008

  1. John says:

    Awesome Example, you rock Johannes!
    Just a note that people have had issues getting access to the authenication service. So your not alone if your developement creditential don’t work. I’ll email again to see if this has been fixed.
     
    Also, any thoughts on using WKB in javascript to cut down the transmission sizes and increase parsing performance in SQL?

  2. justin says:

    Top stuff Johannes!
     
    The integration with SQL2008 + VE/MWS is so kewl!
     
    Two questions:
    1) You use FLOAT as your datatype for lat/longs. If I have my lat/longs as DECIMAL, should i change them to FLOAT. if so, why?
    2) You said "For simple spatial-relationship queries where we only want to find points within a bounding rectangle (e.g. our map-window), standard SQL is faster than spatial SQL because we basically execute a range-query and the B-tree index within SQL Server is already very good. On my laptop I can easily optimise my database so that I get a response on a table with over 6 million entries in ~10 milliseconds. ". Can u you point a direct example that highlights this, please? (ie. some standard sql code..??)
     
    cheers :)

  3. Pilot says:

    Hi.I am working on some kind of smart POIs, I call them emark, just for short. Can we get compatibility with VE? I can mail you small presentation to have a look. Right now we are finishing up and preparing for stable beta of our service it will be running from 12 may, cross my fingers.

  4. Pilot says:

    Hi.I am working on some kind of smart POIs, I call them emark, just for short. Can we get compatibility with VE? I can mail you small presentation to have a look. Right now we are finishing up and preparing for stable beta of our service it will be running from 12 may, cross my fingers.

  5. justin says:

    Yeah John .. good question again! (I’ve been following your work for a bit now .. :P ) …
     
    Johannes … answer kind sir?

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