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

Johannes Kebeck's Blog

09-26-2007, 04:13 PM

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 <a href="http://msdn2.microsoft.com/en-us/library/bb412543.aspx">events</a> which we can attach to the map. Amongst these events are such which fire when we <a href="http://msdn2.microsoft.com/en-us/library/bb412469.aspx">finish zooming</a>*or*<a href="http://msdn2.microsoft.com/en-us/library/bb429556.aspx">finish panning</a>. 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. <a href="http://blufiles.storage.msn.com/y1pLfDzppuxash9qdhzxOiTUttgppdg1GyEu0QdXYShtWILiGR 5hErkyIYW-RHOw4_4Q2OPjV3uPc8"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" height=246 alt=image src="http://blufiles.storage.msn.com/y1pLfDzppuxasgcQ-Egz0HDmTevf4mxh-tsxqV4OdOZAysJdr2DqUaik6UdNDjJ9340oosboy_qRZE" width=400 border=0></a> 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.<pre><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>
</pre><a href="http://11011.net/software/vspaste"></a>
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.<pre>var activePushPinLayer = new Array()
var pplBP = new VEShapeLayer();
var pplRailway = new VEShapeLayer();
</pre><a href="http://11011.net/software/vspaste"></a>
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.<pre>pplBP.SetTitle('pplBP');
pplRailway.SetTitle('pplRailway');</pre><a href="http://11011.net/software/vspaste"></a>
The function that is being executed when we click on one of the checkboxes is AddPOILayer and it takes 2 parameters as input.
<ul>
<li>the name of the control that has been clicked and
<li>the name of the layer we want to populate.</ul>
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 <a href="http://msdn2.microsoft.com/en-us/library/bb429556.aspx">onendpan</a> and <a href="http://msdn2.microsoft.com/en-us/library/bb412469.aspx">onendzoom</a> 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.<pre>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();
}
}</pre><a href="http://11011.net/software/vspaste"></a>
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 <a href="http://msdn2.microsoft.com/en-us/library/bb429564.aspx">PixelToLatLong</a>. 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 <a href="http://msdn2.microsoft.com/en-us/library/bb429564.aspx">PixelToLatLong</a>*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.<pre>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);
}
}</pre><a href="http://11011.net/software/vspaste"></a>
This is our function to determine the appropriate XMLHTTP-object as mentioned above:<pre>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;
}
</pre><a href="http://11011.net/software/vspaste"></a>
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.<pre>CREATE TABLE [BP](
 [Latitude] [float],
 [Longitude] [float],
 [Name] [nvarchar](255))</pre><a href="http://11011.net/software/vspaste"></a>
We define the connection string to our database in the web.config<pre><connectionStrings>
<add name="MyDB"
 connectionString="
 Data Source=MyServer;
Initial Catalog=MyDatabase;
uid=MyUID; pwd=MyPWD;"
 providerName="System.Data.SqlClient"/>
</connectionStrings>
</pre><a href="http://11011.net/software/vspaste"></a>
Now we create a new WebHandler DBAccess.ashx and import the namespace<pre>Imports System.Data.SqlClient</pre><a href="http://11011.net/software/vspaste"></a>
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. <pre>'set culture to en-UK to avoid potential problems with decimal-separators
System.Threading.Thread.CurrentThread.CurrentCultu re = System.Globalization.CultureInfo.CreateSpecificCul ture("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</pre><a href="http://11011.net/software/vspaste"></a>
All right, that's it let's see what we have:
<a href="http://blufiles.storage.msn.com/y1pLfDzppuxasiciUZid3mSWNPU-zf-2_jrz21ZYYiaTUHl4_h4r_x98ua777iwecfzFKiUdPdSd3Q"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px" alt=14-small src="http://blufiles.storage.msn.com/y1pLfDzppuxashpLb4j48_NVtCbJWMl7NaFGWJ6BjVn6HW2wmW LHCxQY0pNX1uud30WwpYyi67dKlk" border=0></a>
You will find the sample application <a href="http://mappointemea.members.winisp.net/Database/Default.aspx">here</a> and as always the complete source code is available for download from this page as well.
<div style="padding-right:0px;display:inline;padding-left:0px;padding-bottom:0px;margin:0px;padding-top:0px">Technorati Tags: <a href="http://technorati.com/tags/Virtual Earth" rel=tag>Virtual Earth</a>, <a href="http://technorati.com/tags/AJAX" rel=tag>AJAX</a>, <a href="http://technorati.com/tags/Database" rel=tag>Database</a></div><img src="http://c.services.spaces.live.com/CollectionWebService/c.gif?cid=4819404664324524694&page=RSS%3a+How+to+bring+your+own+content+to+Virtu al+Earth+(Part+4)&referrer=" width="1px" height="1px" border="0" alt=""><img style="position:absolute" alt="" width="0px" height="0px" src="http://c.live.com/c.gif?NC=31263&NA=1149&PI=88469&RF=&DI=3919&PS=85545&TP=johanneskebeck.spaces.live.com&GT1=JohannesKebeck">

Click here to view the article. (http://JohannesKebeck.spaces.live.com/Blog/cns!42E1F70205EC8A96!1822.entry)