How to bring your own content to Virtual Earth (Part 4)

In the previous parts we added individual VEShape-objects to a layer and we imported collections from Live Search Maps as well as GeoRSS-feeds. These are quick and simple methods to add information to your map but they are limited to some hundred VEShape-objects. There is no hard cut in Virtual Earth but you will experience a degradation in performance when you have more than let’s say 300 points. A good solution would be to not load all available information but only those which are in the current MapView. Our approach will be to determine the bounding box of the current MapView whenever it changes, use this information to query points of interest from a database and dynamically populate a VEShapeLayer with these points.

Dynamically populating VEShapeLayer from a Database

The Virtual Earth API provides a number of events which we can attach to the map. Amongst these events are such which fire when we finish zooming or finish panning. We will capture these events and determine the latitude and longitude of the upper left and the lower right corner of our MapView. With these values we will build and execute a SQL-query against a database table with our points of interest (POI) and retrieve only those records which are within our bounding box. To make this as secure as possible we create an AJAX-call to a WebHandler. The WebHandler in turn will build and execute the SQL Query. While it runs through a SqlDataReader it uses a StringBuilder to dynamically create a JavaScript or JSON-objects. The script is now returned in the response to the AJAX-call and executed there using the eval-command.

image

Don’t worry, if that was to fast, we will walk through it in slow motion. First we add a new AccordionPane to our web site. Again we have HTML-controls of type checkbox in it and again we will capture the onclick-event and execute a parameterized JavaScript. There are 2 POI-categories we offer so far and that means we have to add 2 checkboxes.

<ajaxToolkit:AccordionPane ID="panePOI" runat="server">
    <Header>POI from Database</Header>
    <Content>
        <input id="cbBP" type="checkbox" onclick="AddPOILayer('cbBP',pplBP)" /><a href='javascript:ShowLocation(51.46160111471333, -0.9252655506134102, 19);'>BP</a><br /> 
        <input id="cbRailway" type="checkbox" onclick="AddPOILayer('cbRailway',pplRailway)" /><a href='javascript:ShowLocation(51.46160111471333, -0.9252655506134102, 19);'>Railway</a><br /> 
    </Content>
</ajaxToolkit:AccordionPane>

Now lets move on to the JavaScript-part. In the global section we define an array which holds the activated POI-layers so that we can switch on and off various POI-categories. We also define a VEShapeLayer for each category.

var activePushPinLayer = new Array()
var pplBP = new VEShapeLayer();
var pplRailway = new VEShapeLayer();

In the GetMap-function which is being executed when we load the page we set titles for each layer so that we can easily address them later in our functions.

pplBP.SetTitle('pplBP');
pplRailway.SetTitle('pplRailway');

The function that is being executed when we click on one of the checkboxes is AddPOILayer and it takes 2 parameters as input.

  • the name of the control that has been clicked and
  • the name of the layer we want to populate.

If the checkbox has been activated we add the relevant layer to the map and to the array which holds the active VEShapeLayers. Then we attach the events onendpan and onendzoom to the map and make sure that the function LoadData is executed whenever these events occur. Finally we call the function LoadData for the first time.

If the checkbox has been deactivated we leave the layer on the map but we delete all VEShape-objects from it. Then we remove the VEShapeLayer from the array of active layers and detach the events from the map.

function AddPOILayer(control,layer)
{
    if (document.getElementById(control).checked == false) {
        //Delete Shape-Layer
        layer.DeleteAllShapes();
        
        //Remove the layer from the array of active layers
        for (var i = 0; i < activePushPinLayer.length; ++i)
        {
            if (activePushPinLayer[i] == layer.GetTitle())
            {
                activePushPinLayer.splice(i,1);
            }
        }
        
        //Detach Map-Events
        if (activePushPinLayer.length == 1) {
            map.DetachEvent("onendpan", LoadData);
            map.DetachEvent("onendzoom", LoadData);
        }
    }
    else{
        //Create a new Shape-Layer
        try
        {
            map.AddShapeLayer(layer);
        }
        catch(e)
        {
        }

        //Add the layer to the array of active layers
        activePushPinLayer.push(layer.GetTitle());

        //Attach Map-Events
        map.AttachEvent("onendpan", LoadData);
        map.AttachEvent("onendzoom", LoadData);
        LoadData();
    }
}

The function LoadData prepares and executes our AJAX-call. First we delete all VEShape-objects from the map. Then we calculate the latitude and longitudes of the upper left and lower right corner of our MapView. Virtual Earth supports us here with a function PixelToLatLong. This function takes as input an VEPixel-object and this again is defined by 2 pixel coordinates relative to the MapView. Thus the upper left corner is always (0, 0) and the lower right corner is defined by the width and the height of our <div>-element. The output of the PixelToLatLong function is always a VELatLong-object and we need to break it into its properties latitude and longitude.

Now we can start building our AJAX-call. The call goes to a WebHandler DBAccess.ashx which we still have to create and it will hand over a few URL-parameters. The first 4 are always the latitudes and longitudes of the upper left and lower right corner of our MapView. The fifth is the total number of active layers and the next ones are the names of the active layers.

Before we execute the call we first have to determine what the appropriate XMLHTTP-object for the clients browser is and we do that by calling the function GetXmlHttp. With this object we execute the XMLHTTP Get-Request to our WebHandler and then the call waits until it receives the response from the WehHandler. As mentioned earlier this response is a JavaScript and it will be executed using the eval-command.

function LoadData()
{
    map.DeleteAllShapes();
    
    //Retrieve the boundaries of the mapview
    var ulPixel  = new VEPixel(0, 0);
    var brPixel  = new VEPixel(mapWidth, mapHeight);
    var ulLatLon = map.PixelToLatcLong(ulPixel);
    var ulLat = ulLatLon.Latitude;
    var ulLon = ulLatLon.Longitude;
    var brLatLon = map.PixelToLatLong(brPixel);
    var brLat = brLatLon.Latitude;
    var brLon = brLatLon.Longitude;
    
    //Build URL to call the server
    var url="DBAccess.ashx?";
    url += "&ulLat=" + ulLat;
    url += "&ulLon=" + ulLon;
    url += "&brLat=" + brLat;
    url += "&brLon=" + brLon;

    //In this part we check how many and which layers are active
    url += "&layerCount=" + activePushPinLayer.length; 
for (var i = 0; i < activePushPinLayer.length; ++i) { url += "&layer" + i + "=" + activePushPinLayer[i]; } //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" for us to //execute using eval() var result = xmlhttp.responseText eval(result); } } xmlhttp.send(null); } }

This is our function to determine the appropriate XMLHTTP-object as mentioned above:

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;
}

That’s all on the JavaScript side. Now we move on to our WebHandler but before we do that let’s have a quick look at our database. We have 2 tables for the POI with a total of more than 4,000 records. The tables contain columns for the latitude and longitude and one with the same. This is of course just a sample and there could be many more information in it. How did we get the latitudes and longitudes in the first place? Well, that may be an issue for another blog-posting but the Virtual Earth platform can support you here as well.

CREATE TABLE [BP](
    [Latitude] [float],
    [Longitude] [float],
    [Name] [nvarchar](255))

We define the connection string to our database in the web.config

<connectionStrings>
    <add name="MyDB"
         connectionString="
           Data Source=MyServer;
           Initial Catalog=MyDatabase;
           uid=MyUID; pwd=MyPWD;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

Now we create a new WebHandler DBAccess.ashx and import the namespace

Imports System.Data.SqlClient

Finally we replace the default code with the one shown below. It is always a good idea to set the CurrentCulture to make sure that the decimal separator for the latitudes and  longitudes are returned as a ‘.’. Then we fetch the URL-parameters as well as the ConnectionStrings from the web.config and set up the database connection before we build the SqlCommand. The SqlCommand is being executed with a SqlDataReader and as we read the records we use a StringBuilder to create the JavaScript which is later to be returned to the AJAX-call and executed there. If we have more than 300 records which meet the criteria we don’t return a JavaScript to add the VEShape-objects but rather we return a script which pops up a message.

'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")
        
'How many layers are active?
Dim layerCount As Integer = context.Request.Params("layerCount")
        
'Query database(s) and create JavaScript
Dim settings As ConnectionStringSettings
Dim sb As StringBuilder = New StringBuilder
Dim myPins As String = ""
Dim poiCounter As Integer = 0
Dim i As Integer = 0
For i = 0 To layerCount
    Dim dummy As String = context.Request.Params("layer" & i)
    Select Case dummy
        Case "pplBP"
            settings = ConfigurationManager.ConnectionStrings("MyDB")
            Dim myConn As New SqlConnection(settings.ConnectionString)
            Dim myQuery1 As String = "SELECT COUNT(*) FROM BP WHERE (Latitude BETWEEN " + brLat + " AND " + ulLat + ") AND (Longitude BETWEEN " + ulLon + " AND " + brLon + ")"
            Dim myCMD1 As New SqlCommand(myQuery1, myConn)
            myConn.Open()
            Dim myReader1 As SqlDataReader = myCMD1.ExecuteReader()
            myReader1.Read()
            If myReader1(0) > 300 Then
                myPins = myPins + _
                    "map.ShowMessage('There are more than 300s POI of type BP in this MapView. The Layer will not be shown.');"
                sb.Append(myPins)
                myReader1.Close()
            Else
                poiCounter = poiCounter + myReader1(0)
                myReader1.Close()
                Dim j As Integer = 0
                Dim myQuery2 As String = "SELECT Latitude, Longitude, Name FROM BP WHERE (Latitude BETWEEN " + brLat + " AND " + ulLat + ") AND (Longitude BETWEEN " + ulLon + " AND " + brLon + ")"
                Dim myCMD2 As New SqlCommand(myQuery2, myConn)
                Dim myReader2 As SqlDataReader = myCMD2.ExecuteReader()
                While myReader2.Read()
                    myPins = myPins + _
                        "var shape" + j.ToString + "=new VEShape(VEShapeType.Pushpin, new VELatLong(" + myReader2(0).ToString + ", " + myReader2(1).ToString + "));" + _
                        "shape" + j.ToString + ".SetCustomIcon('IMG/BP.bmp');" + _
                        "shape" + j.ToString + ".SetTitle(" + """" + myReader2(2).ToString + """" + ");" + _
                       "pplBP.AddShape(shape" + j.ToString + ");"
                    j = j + 1
                End While
                sb.Append(myPins)
                myReader2.Close()
            End If
            myConn.Close()
       Case "pplRailway"
            settings = ConfigurationManager.ConnectionStrings("MyDB")
            Dim myConn As New SqlConnection(settings.ConnectionString)
            Dim myQuery1 As String = "SELECT COUNT(*) FROM Railway WHERE (Latitude BETWEEN " + brLat + " AND " + ulLat + ") AND (Longitude BETWEEN " + ulLon + " AND " + brLon + ")"
            Dim myCMD1 As New SqlCommand(myQuery1, myConn)
            myConn.Open()
            Dim myReader1 As SqlDataReader = myCMD1.ExecuteReader()
            myReader1.Read()
            If myReader1(0) > 300 Then
                myPins = myPins + _
                    "map.ShowMessage('There are more than 300s POI of type Railway in this MapView. The Layer will not be shown.');"
                sb.Append(myPins)
                myReader1.Close()
            Else
                poiCounter = poiCounter + myReader1(0)
                myReader1.Close()
                Dim j As Integer = 0
                Dim myQuery2 As String = "SELECT Latitude, Longitude, Name FROM Railway WHERE (Latitude BETWEEN " + brLat + " AND " + ulLat + ") AND (Longitude BETWEEN " + ulLon + " AND " + brLon + ")"
                Dim myCMD2 As New SqlCommand(myQuery2, myConn)
                Dim myReader2 As SqlDataReader = myCMD2.ExecuteReader()
                While myReader2.Read()
                    myPins = myPins + _
                       "var shape" + j.ToString + "=new VEShape(VEShapeType.Pushpin, new VELatLong(" + myReader2(0).ToString + ", " + myReader2(1).ToString + "));" + _
                       "shape" + j.ToString + ".SetCustomIcon('IMG/Railway.bmp');" + _
                       "shape" + j.ToString + ".SetTitle(" + """" + myReader2(2).ToString + """" + ");" + _
                       "pplRailway.AddShape(shape" + j.ToString + ");"
                    j = j + 1
                End While
                sb.Append(myPins)
                myReader2.Close()
            End If
            myConn.Close()
    End Select
Next

If poiCounter > 300 Then
    context.Response.Write("map.ShowMessage('The POIs in all layers add up to more than 300. Please deselect a layer or zoom-in.');")
Else
    context.Response.Write(sb.ToString())
End If

All right, that’s it let’s see what we have:

14-small

You will find the sample application here and as always the complete source code is available for download from this page as well.

Technorati Tags: , ,
Advertisements
This entry was posted in Virtual Earth. Bookmark the permalink.

2 Responses to How to bring your own content to Virtual Earth (Part 4)

  1. Andrew says:

    This awesome! Finally seeing how to populate database informaton into Virtual Earth! Thanks!! Everday I check your blog. You have great stuff here for us .Net programmers! Keep more coming! :)

  2. JeffK says:

    Hi dude,
    nice article, its the way we use VE and SqlServer Data, BUT, just taking the querystring parameters as strings and concatenate them into a Sql select is a serious security risk.
    What about updating the sourcecode with :
    double uLat =0;
    double.TryParse( context.Request["ulLat"], out uLat);
    The same technique applies to the other querystring parameters.

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