May 01

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...

...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.

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.

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.

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.

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")

Technorati Tags: Virtual Earth,SQL Server 2008,FindNearRoute

April 30

Another Update for Virtual Earth Imagery

I'm sure, Chris and Steve will publish the full list shortlist. There has been another update and increase of coverage with all sorts of imagery in Virtual Earth. This time it's 22 TB of data and for the first time we have some Australian regions available in Bird's Eye imagery.

The full list of Bird's Eye imagery outside North America is here.

Technorati Tags: Virtual Earth,Update,Imagery

April 11

What's new in Virtual Earth version 6.1?

We just released version 6.1. of the Virtual Earth API. Steve Lombardi and Chris Pendleton have blogged about the new Features so I will cut it short and concentrate on those features which surface through the API.

Bird's Eye Hybrid

We can now overlay the roads on the Bird's Eye images: Use VEMapStyle.Birdseye or 'o' if you want to see Bird's Eye images without labels and VEMapStyle.BirdseyeHybrid or short 'b' for the Bird's Eye hybrid.

Vegetation in 3D

Yep, our 3D team is great. They have brought the automated process to generate our 3D-model close to perfection. The density of the models is higher than before and the quality of the process is so accurate that we could really count the trees in a city. The first fruits are visible now in Virtual Earth. There is also an improved version of the 3D-modelling client 'Virtual Earth 3dvia' available.

Walking Directions

This is a simplified version of walking directions using the same road-networks as for vehicles but with different rule-set, i.e. you may not use motorways but you may walk in the opposite along one-way streets. To use walking directions set 'VERouteOptions.RouteMode = VERouteMode.Walking'.

In the screenshot below you see the fastest route from Munich Main-Station to the Karlsplatz in Munich for cars in green and for pedestrians in blue.

Localized Dashboard

Is available by setting the mkt-parameter when loading the VEMapControl http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.1&mkt=XX-YY. Currently only the markets

fr-fr
it-it
ja-jp

have a localized dashboard so far.

Localized Driving Directions

Localized driving directions have been available since version 6. So far you needed to set 'VERoutoptions.UseMWS = true' to fall back to the MapPoint Web Service routing engine. With version 6.1 you can use the Virtual Earth routing engine for localized driving directions by just setting the mkt-parameter when loading the map.

Precision-property for VEPlace-object

The geocoder returned already flags for the MatchCode and the MatchConfidence. Now it also returns the precision which basically tells you if the geocode was interpolated or accurate to the rooftop. Rooftop-geocoding is only available in the US.

Other New Features and Enhancements

Switch to disable Bird's Eye imagery
Enhanced support for the Safari browser
Staging environment for Customer Identification (transaction counting). See also http://msdn2.microsoft.com/en-us/library/bb924353.aspx
Better printing support
Traffic based routing (only in the US)
Reverse geocoding (only in the US). Please note: enterprise customers can also use MapPoint Web Service which is part of the Virtual Earth Platform. In MapPoint Web Service we have reverse geocoding for all countries where we support geocoding.

Technorati Tags: Virtual Earth

April 09

Lazy Loading the Virtual Earth MapControl

Although I think the performance of the Virtual Earth MapControl is pretty good there may be cases when you want the web site without the map first and then load the map if and when needed. In these cases you can avoid the overhead of the Virtual Earth MapControl for the initial loading and inject it only when needed. Some call this process of injecting the JavaScript after the document body has been loaded also 'lazy loading'. Here is how it's done:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
   <head>
      <title></title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <script type="text/javascript">
      var map = null;
            
      function GetMap()
      {
         map = new VEMap('myMap');
         map.LoadMap();
      }   
      
      function LoadMapControl() 
      {
         var script = document.createElement("script");
         script.setAttribute("src", "http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6");
         script.setAttribute("type", "text/javascript");
         document.documentElement.firstChild.appendChild(script);
         GetMap();
      }      
      </script>
   </head>
   <body>
      <div id='myMap' style="position:relative; width:400px; height:400px;"></div>
      <input type="button" value="Load VE MapControl" onclick="LoadMapControl();"/>
   </body>
</html>

Technorati Tags: Virtual Earth

April 06

How many points can you add to Virtual Earth?

I hear this question from time to time. Well, there is no hard limit in the API but since the points are added through JavaScript-methods on the client your application will have slower response times the more points you add. A reasonable upper limit depends on the performance of the client-machine as well as the methods you use. Hold on, is there more than one method to add VEShape-objects to a map? Not really but since version 6 you can add the shapes either individually or through bulk-methods.

Bulk-Loading of VEShape-Objects

I did some performance test on my standard laptop with an application where I retrieve increasing amounts of POI from a database. The general principle of the database access is explained in one of my previous postings.

In the first test, I used exactly this approach and the web handler will return a JavaScript which adds individual VEShape-Objects like this:

var shape0=new VEShape(VEShapeType.Pushpin, new VELatLong(51.46102, -0.98861));
shape0.SetCustomIcon('./IMG/blue.png');
shape0.SetTitle("My Title 1");
slPOI.AddShape(shape0);
var shape1=new VEShape(VEShapeType.Pushpin, new VELatLong(51.45506, -0.93939));
shape1.SetCustomIcon('./IMG/blue.png');
shape1.SetTitle("My Title 2");
slPOI.AddShape(shape1);
var shape2=new VEShape(VEShapeType.Pushpin, new VELatLong(51.45971, -0.97639));
shape2.SetCustomIcon('./IMG/blue.png');
shape2.SetTitle("My Title 3");
slPOI.AddShape(shape2);

In the second test I modified the web handler in a way that it returns an JavaScript which creates an array of VEShape-Objects and adds all of them at a time:

var myPOIArray = new Array();
var shape0=new VEShape(VEShapeType.Pushpin, new VELatLong(51.46102, -0.98861));
shape0.SetCustomIcon('./IMG/blue.png');
shape0.SetTitle("My Title 1");
myPOIArray.push(shape0);
var shape1=new VEShape(VEShapeType.Pushpin, new VELatLong(51.45506, -0.93939));
shape1.SetCustomIcon('./IMG/blue.png');
shape1.SetTitle("My Title 2");
myPOIArray.push(shape1);
var shape2=new VEShape(VEShapeType.Pushpin, new VELatLong(51.45971, -0.97639));
shape2.SetCustomIcon('./IMG/blue.png');
shape2.SetTitle("My Title 3");
myPOIArray.push(shape2);
slPOI.AddShape(myPOIArray);

The impact on performance is quite obvious and becomes more dramatically the more points you add:

However, there is another point to consider: the more points you have the less readable a map becomes. In the screenshot below you see a 800x600 pixel map with ~700 POI:

You see that the icons are in some places overlapping and hiding each other. To improve the readability you should consider clustering.

Clustering

There are 2 general approaches: Client-Side and Server-Side Clustering. Richard Brundritt has described the 'client-side clustering' on 'Via Windows Live'. The advantage of this approach is, that you have no dependencies in the middleware and the backend but since the clustering itself is done on the client, it requires that all data are being transferred to the client in the first place. Thus performance decreases with the amount of points of interest.

You also see that there is a dependency on the zoom-level. This is because the clustering becomes the more CPU intensive the more points you have to group into one cluster. Thus Ricky introduced a threshold for the zoom-level in his code. This approach keeps the performance in reasonable limits but it also hides the POI for lower zoom-levels.

John O'Brien has published the code for server-side clustering on 'Via Windows Live'. John works with Virtual Earth from the very beginning and you can trace the sample back to it's origins in earlier Virtual Earth versions. In the meantime it is incorporated in his own framework with lot's of helper functions and JavaScript-classes.

Let's have a look at the basic principle of clustering. We attach an event to the VEMap-object and re-create the cluster whenever we pan or zoom the map. To do so we execute an AJAX-call to a web handler which

retrieves all POI on the visible map from the database.
creates a virtual grid and groups all POI within this grid into a clustered POI.
sends the clustered POI to the AJAX-call

For database access without clustering we use only the latitudes and longitudes of the bounding box of the map as URL-parameters in our AJAX-call. For the server-side clustering we also need the height and width of the map as well as the zoom-level to create the virtual grid.

As mentioned above we use Virtual Earth events to capture whenever we panned or zoomed the map and recreate the cluster. This would go through the complete procedure even if we pan the map for only 1 pixel. To remove some of this load we will introduce a threshold which only refreshes the map if we pan for more than this threshold. In the example below this threshold is set to 100 pixels. Here is my complete JavaScript:

window.onload = GetMap;
window.onresize = Resize;

//Map
var map = null;
var mapWidth = null;
var mapHeight = null;

//To query or not to query
var cpLatLongOld = null;
var panThresHold = 100;

//VEShapeLayer
var slPOI = new VEShapeLayer();
var slAllPOI = new VEShapeLayer();

function GetMap()
{
    map = new VEMap('divMap');
    map.LoadMap(new VELatLong(51.461962075378054, -0.9260702133178665), 18, VEMapStyle.Shaded, false);
    Resize();
    
    //Add VEShapeLayer
    map.AddShapeLayer(slPOI);
}

//Resize map and controls whenever the size of the browser window changes
//Also load the minimap
function Resize()
{
    var mapDiv = document.getElementById("divMap");
    var ctrlDiv = document.getElementById("divCtrl");
    var windowWidth = document.body.clientWidth;
    var windowHeight = document.body.clientHeight;
    mapWidth = windowWidth - 210;
    mapHeight = windowHeight  - 70;
    mapDiv.style.width = mapWidth + "px";
    mapDiv.style.height = mapHeight + "px";
    ctrlDiv.style.height = (windowHeight - 60) + "px";
    map.Resize(mapWidth, mapHeight);
    map.ShowMiniMap(mapWidth-205, 13, VEMiniMapSize.Large);
}

//Database Layers
function BulkAddShapeClusterServer(control)
{
    if (document.getElementById(control).checked == false) 
    {
        //Delete all Shaps in Layer
        slPOI.DeleteAllShapes();
        
        //Detach Map-Events
        map.DetachEvent("onstartpan", RememberCP);
        map.DetachEvent("onendpan", ToQueryOrNotToQuery);
        map.DetachEvent("onendzoom", ServerSideCluster);
        
        //Empty the Textboxes
        document.getElementById('txtNumPOI').value = "";
        document.getElementById('txtMyTime').value = ""; 
        document.getElementById('txtQuery').value = "";
    }
    else
    {
        //Attach Map-Events
        map.AttachEvent("onstartpan", RememberCP);
        map.AttachEvent("onendpan", ToQueryOrNotToQuery);
        map.AttachEvent("onendzoom", ServerSideCluster);
        ServerSideCluster();
    }
}

//Remeber centre point before the panning
function RememberCP()
{
    cpLatLongOld = map.GetCenter();
}

//To query or not to query
function ToQueryOrNotToQuery()
{
    var cpLatLongNew = map.GetCenter();
    var cpPixelOld = map.LatLongToPixel(cpLatLongOld);
    var cpPixelNew = map.LatLongToPixel(cpLatLongNew);
    var x = Math.abs(cpPixelOld.x - cpPixelNew.x);
    var y = Math.abs(cpPixelOld.y - cpPixelNew.y);
    
    if (x > panThresHold || y > panThresHold)
    {
        ServerSideCluster()
    }
    else
    {
        document.getElementById('txtNumPOI').value = "";
        document.getElementById('txtMyTime').value = ""; 
        document.getElementById('txtQuery').value = "FALSE";
    }
}

//Call the web service
function ServerSideCluster()
{
    slPOI.DeleteAllShapes();
    
    //Retrieve the boundaries of the mapview
    var ulPixel  = new VEPixel(0, 0);
    var brPixel  = new VEPixel(mapWidth, mapHeight);
    var ulLatLon = map.PixelToLatLong(ulPixel);
    var ulLat = ulLatLon.Latitude;
    var ulLon = ulLatLon.Longitude;
    var brLatLon = map.PixelToLatLong(brPixel);
    var brLat = brLatLon.Latitude;
    var brLon = brLatLon.Longitude;
    var lvl = map.GetZoomLevel();
    
    //Build URL to call the server
    var url="./LoadCluster.ashx?";
    url += "&ulLat=" + ulLat;
    url += "&ulLon=" + ulLon;
    url += "&brLat=" + brLat;
    url += "&brLon=" + brLon;
    url += "&lvl=" + lvl;
    url += "&width=" + mapWidth;
    url += "&height=" + mapHeight;

    //Get the appropriate XMLHTTP object for the browser
    var xmlhttp = GetXmlHttp();
    
    //if we have a valid XMLHTTP object
    if (xmlhttp)
    {
        xmlhttp.Open("GET", url, true); // varAsynx = true
        
        //set the callback
        xmlhttp.onreadystatechange = function()
        {
            if (xmlhttp.readystate ==4) //4 is a success
            {
                //server code creates JavaScript "on the fly"
                var result = xmlhttp.responseText;
                //start the timer
                var myStart = new Date();
                //execute using eval()
                eval(result);
                //stop the timer
                var myEnd = new Date();
                var myTime = myEnd-myStart;
                document.getElementById('txtNumPOI').value = slPOI.GetShapeCount();
                document.getElementById('txtMyTime').value = myTime; 
                document.getElementById('txtQuery').value = "TRUE";
            }
        }
        xmlhttp.send(null);
    }
}

//Helper-Function
function GetXmlHttp()
{
    var x = null;
    try
    {
        x = new ActiveXObject("Msxml2.XMLHTTP");
    }
    catch (e)
    {
        try
        {
            x = new ActiveXObject("Microsoft.XMLHTTP");
        }
        catch (e)
        {
            x = null;
        }
    }
    if (!x && typeof XMLHttpRequest != "undefined")
    {
        x = new XMLHttpRequest();
    }
    return x;
}

Now let's move on to the web handler. When we process the request we first make sure that the culture is set to something which interprets the "." as a decimal separator before we fetch the URL-parameters. Then we set up an array for our virtual grid. The size of the array depends on the size of the map and the size of the grid (here: 40 pixels). Now we convert the latitudes and longitudes of the upper left corner into pixel coordinates. These pixel-coordinates will be the origin of our virtual grid and please note: this is not the same as a VEPixel-object where the upper-left corner of the visible area is always (0, 0). The pixel coordinates we calculate here are relative to an origin which covers the whole world. For the calculation we use the algorithm which has been published by Joe Schwartz. The code is listed at the bottom of this listing.

Now we set up our database-query and while we read the database records we calculate the pixel coordinates, determine the position in our virtual grid and append to our array of clustered pins. Finally we loop through our array and create the JavaScript which is then returned to the AJAX-call.

'Constants for the Clustering
'(addressable area in VE)
Private Const MinLatitude As Double = -85.05112878
Private Const MaxLatitude As Double = 85.05112878
Private Const MinLongitude As Double = -180
Private Const MaxLongitude As Double = 180
'Grid-Size
Private Const GridSize As Integer = 40

Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
    'set culture to en-UK to avoid potential problems with decimal-separators
    System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

    'Get the URL-Parameters
    Dim ulLat As String = context.Request.Params("ulLat")
    Dim brLat As String = context.Request.Params("brLat")
    Dim ulLon As String = context.Request.Params("ulLon")
    Dim brLon As String = context.Request.Params("brLon")
    Dim lvl As Integer = context.Request.Params("lvl")
    Dim mapWidth As Integer = context.Request.Params("width")
    Dim mapHeight As Integer = context.Request.Params("height")
    
    'Set up a grid for the Clustering
    Dim numXCells = CInt(Math.Ceiling(mapWidth / GridSize))
    Dim numYCells = CInt(Math.Ceiling(mapHeight / GridSize))
    Dim numCells As Integer = numXCells * numYCells - 1
    Dim gridCells()() As Object = New Object(numCells)() {}
    
    'Determine PixelX and PixelY of upper left corner
    Dim ulTotalX As Integer
    Dim ulTotalY As Integer
    LatLongToPixel(ulLat, ulLon, lvl, ulTotalX, ulTotalY)

    'Query database(s) and create JavaScript
    Dim poiTotalX As Integer
    Dim poiTotalY As Integer
    Dim poiMapX As Integer
    Dim poiMapY As Integer
    Dim settings As ConnectionStringSettings
    settings = ConfigurationManager.ConnectionStrings("HannesPOI")
    Dim sb As StringBuilder = New StringBuilder
    sb.Append("var myPOIArray = new Array();")
    Dim i As Integer = 0
    Dim myConn As New SqlConnection(settings.ConnectionString)
    myConn.Open()
    Dim myQuery As String = "SELECT Latitude, Longitude, Name FROM UK_low_bridges_all WHERE (Latitude BETWEEN " + brLat + " AND " + ulLat + ") AND (Longitude BETWEEN " + ulLon + " AND " + brLon + ")"
    Dim myCMD As New SqlCommand(myQuery, myConn)
    Dim myReader As SqlDataReader = myCMD.ExecuteReader()
    While myReader.Read()
        'Determine PixelX and PixelY of POI
        LatLongToPixel(myReader(0), myReader(1), lvl, poiTotalX, poiTotalY)
        poiMapX = poiTotalX - ulTotalX
        poiMapY = poiTotalY - ulTotalY
        'Populate the array with clustered pins
        For x = 0 To numXCells - 1
            If (x * GridSize <= poiMapX) And (poiMapX < (x + 1) * GridSize) Then
                For y = 0 To numYCells - 1
                    If (y * GridSize <= poiMapY) And (poiMapY < (y + 1) * GridSize) Then
                        Dim myClusteredPin(2) As Object
                        If gridCells(x * y) Is Nothing Then
                            myClusteredPin(0) = 1
                            myClusteredPin(1) = myReader(0).ToString + ", " + myReader(1).ToString
                            myClusteredPin(2) = myReader(2).ToString
                        Else
                            myClusteredPin = gridCells(x * y)
                            myClusteredPin(0) = myClusteredPin(0) + 1
                            myClusteredPin(2) = myClusteredPin(2) + "<hr>" + myReader(2).ToString
                        End If
                        gridCells(x * y) = myClusteredPin
                    End If
                Next
            End If
        Next
    End While
    myReader.Close()
    myConn.Close()
    
    'Create the pins
    Dim myPins As String = ""
    For j = 0 To numCells
        If gridCells(j) IsNot Nothing Then
            Dim myClusteredPin = gridCells(j)
            myPins = myPins + _
                "var shape" + i.ToString + "=new VEShape(VEShapeType.Pushpin, new VELatLong(" + myClusteredPin(1) + "));" + _
                "shape" + i.ToString + ".SetTitle(" + """" + "There are " + myClusteredPin(0).ToString + " POI" + """" + ");"
            If myClusteredPin(0) > 5 Then
                myPins = myPins + "shape" + i.ToString + ".SetDescription(" + """" + "There are more than 5 POI in this cluster zoom closer to see the details." + """" + ");"
            Else
                myPins = myPins + "shape" + i.ToString + ".SetDescription(" + """" + myClusteredPin(2).ToString + """" + ");"
            End If
            If myClusteredPin(0) > 1 Then
                myPins = myPins + "shape" + i.ToString + ".SetCustomIcon('./IMG/red.png');"
            Else
                myPins = myPins + "shape" + i.ToString + ".SetCustomIcon('./IMG/blue.png');"
            End If
            myPins = myPins + "myPOIArray.push(shape" + i.ToString + ");"
            i = i + 1
        End If
    Next
    
    sb.Append(myPins)
    sb.Append("slPOI.AddShape(myPOIArray);")
    context.Response.Write(sb.ToString())
End Sub

    'Clips a number to the specified minimum and maximum values
    Private Shared Function Clip(ByVal n As Double, ByVal minValue As Double, ByVal maxValue As Double) As Double
        Return Math.Min(Math.Max(n, minValue), maxValue)
    End Function
    
    'Determine the offset off the map
    Public Shared Function Offset(ByVal lvl As Integer) As UInt32
        Return 256 << lvl
    End Function

    'Convert Latitude and Longitude to VEPixel
    Public Shared Sub LatLongToPixel(ByVal latitude As Double, ByVal longitude As Double, ByVal lvl As Integer, ByRef pixelX As Integer, ByRef pixelY As Integer)
        latitude = Clip(latitude, MinLatitude, MaxLatitude)
        longitude = Clip(longitude, MinLongitude, MaxLongitude)
 
        Dim x As Double = (longitude + 180) / 360
        Dim sinLatitude As Double = Math.Sin(latitude * Math.PI / 180)
        Dim y As Double = 0.5 - Math.Log((1 + sinLatitude) / (1 - sinLatitude)) / (4 * Math.PI)
 
        Dim mapSize As UInt32 = Offset(lvl)
        pixelX = CType(Clip(x * mapSize + 0.5, 0, mapSize - 1), Integer)
        pixelY = CType(Clip(y * mapSize + 0.5, 0, mapSize - 1), Integer)
    End Sub

That's it. In my tests I never exceeded 200 milliseconds to render the POI in the client (this is without the processing in the web handler and the database query).

The complete sample application is available here.

Technorati Tags: Virtual Earth,Performance,Clustering

View more entries

Hannes's Virtual Earth Blog