MapForums

Retrieving Data from SQL Server 2008

Now that we have the first spatial data in our database we will investigate the various approaches to get them out again and display them in Virtual Earth.

Adding VEShape-Objects

Adding Individual Pushpin-Objects (Points)

Adding individual VEShape-objects is a simple process. First we define the VEShape-object by type and location. In the example code below, we add a VEShape of type Pushpin for our first sample. We can also define custom icons, set a title, set a description and many other properties. For a full reference have a look at the SDK but for our sample code the information below will suffice.

var shape=new VEShape(VEShapeType.Pushpin, new VELatLong(51.59076, 0.31522));

shape.SetCustomIcon('./IMG/poi_search3.gif');

shape.SetTitle('MyTitle');

shape.SetDescription('MyDescription');

layer.AddShape(shape);
 
The table, we have so far, is ideal for the very first approach. It contains 2 columns with latitude and longitude which we can directly use to create VEShape-objects. Note: Using latitudes and longitudes in numeric format rather than spatial format will work in any database but we want to start simply and you will soon see the advantages of the spatial engine in SQL Server 2008. 
 
How do we access a database at all from an application which is coded in JavaScript? The most reasonable approach is to create an AJAX-call which calls a web service and waits until it receives a response. The web service will then in turn execute the SQL statement and return the results to the AJAX-call. We have discussed earlier, various types of responses that Virtual Earth can handle and in this first step we will add individual VEShape-objects. Thus, the web service has to build a JavaScript similar to the one mentioned above. 
 

  
 
That is simple enough and to make it a bit more interactive we leverage another feature of the Virtual Earth API – the ability to attach events. In fact it is almost always not necessary to retrieve all information at once. You will enhance performance quite a lot if you only retrieve those data which are in the area that is currently visible in the Virtual Earth MapControl. If you do this you have to refresh the data of course whenever you pan or zoom the map and here is where the events become relevant. Virtual Earth has various build-in events. Amongst these there are 2 which fire whenever we finish zooming or finish panning the map. 
 
We will develop our application with ASP.AJAX and the ASP.NET AJAX Control Toolkit and use the Accordion Control to group our various functions that we are about to implement. Since we are using Visual Studio 2008 with the .NET Framework 3.5, AJAX is already an integral part of the IDE and you only have to download the Control Toolkit. If you use Visual Studio 2005, you first have to download the ASP.NET AJAX extension. We will go a little bit slower through our first part of this web application and be less detailed in the following steps. 
 
When you create a new project of type “ASP.NET Web Site” using Visual Studio 2008, the AJAX extension is automatically configured in the web.config file. We start by including the necessary components into the HTML header of the web page:
 <title>Virtual Earth & SQL Server 2008title>

    <link href="CSS/MyStyles.css" rel="stylesheet" type="text/css" />

    <link rel="shortcut icon" href="IMG/favicon.ico" />

    <script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6&mkt=en-gb" type="text/javascript">script>

    <script src="JS/MyScript.js" type="text/javascript">script>
 
Now we will define our HTML-body. When we load the page we want, a JavaScript function loads the Virtual Earth MapControl among other things. Thus we define an onload-event for the body: 
 
<bodyonload="GetMap()" >
*** <formid="form1"runat="server">
 
The ScriptManager is a key ASP.NET AJAX component which has automatically been added when we generated the project:
 <asp:ScriptManager ID="ScriptManager1" runat="server">asp:ScriptManager>


 
This first DIV-element is just a header and is only there for the looks:
 <div style="position:absolute; top:0px; left:0px; width:100%; height:40px;" class="header">

            <img src="IMG/VirtualEarth.gif" alt="Virtual Earth Logo" style="margin-left:5px;" />

        div>


 
The div-Accordion is where we host the Accordion-control from the ASP.NET AJAX Control Toolkit. The design of the Accordion-control is linked to CSS-classes in our style sheet and in the control we have various accordion-panes. We will start with 2 panes: one which allows us to search for an address and another one which we use to retrieve data from the database. In the latter we have HTML-controls for the interaction: 
 <div id="divAccordion" style="position:absolute; top:40px; left:0px; width:290px; margin-left:5px;">

            <cc1:Accordion ID="Accordion1" runat="server" HeaderCssClass="accordionHeader" ContentCssClass="accordionContent" AutoSize="Fill">

                <Panes>

                    <cc1:AccordionPane ID="paneSearch" runat="server">

                        <Header>SearchHeader>

                        <Content>

                            <b>What / Whereb><br />

                            <input id="txtWhat" type="text" style="width:125px"/><input id="txtWhere" type="text" style="width:125px" /><br />

                            <input id="btnSearch" type="button" value="Search" style="width:250px" onclick="Search()" /><br />

                        Content>

                    cc1:AccordionPane>

                    <cc1:AccordionPane ID="AccordionPane1" runat="server">

                        <Header>VEShape-ObjectsHeader>

                        <Content>

                            <input id="cbFMDPoints" type="checkbox" onclick="AddFMDPoints('cbFMDPoints')"/><a href='javascript:ShowLocation(54.41893, -3.735352, 6);'>FMD-Pointsa><br />

 Content>

                    cc1:AccordionPane>

 Panes>

            cc1:Accordion> 

        div>


 
The last DIV-element will host the Virtual Earth MapControl. We define a position and an initial size for this DIV-element but we will later change the size dynamically:
 <div id="divMap" style="position:absolute; top:45px; left:300px; width:300px; height:300px;">div>


 
So far we have a page which looks with my CSS like this: 
 

  
 
As you have probably noticed in the source code, above, we call a number of JavaScript functions when we click the button or the hyperlink and of course there is also the JavaScript code which is being executed when the onload-event of the body fires. So, now we’ll have a look at these JavaScripts: 
 
We start with a global variable for our VE MapControl and we add 2 other parameters for the map-dimensions which we will later use to dynamically resize the map:
//Map

var map = null;

var mapWidth = null;

var mapHeight = null;


 
We also define a global variable for the layer which will host the data that we retrieve from the database. This variable is of type VEShapeLayer:
//VEShape-Objects

var slFMDPoints = new VEShapeLayer();


 
The function that is being executed when the onload-event fires is GetMap(). This function initializes the Virtual Earth MapControl in the DIV-element “divMap” and then loads the map. The LoadMap-method of the Virtual Earth MapControl doesn’t have any mandatory parameters but as you can see, we can already control the centre-point and zoom-level of the map as well as the style (which is in this case the hybrid-style, i.e. roads are overlaid on top of the aerial images). For a full reference of these parameters have a look at the Virtual Earth SDK. Finally, we call a function, Resize, which adjusts the size of the DIV-elements and the map to the available real-estate in the browser window. It also shows a mini map in the upper right corner of the map:
function GetMap()

{

    map = new VEMap('divMap');



    //Load and resize the map

    map.LoadMap(new VELatLong(51.461962075378054, -0.9260702133178665), 18, VEMapStyle.Hybrid, false);

    Resize();

}

//Resize map and controls whenever the size of the browser window changes

//Also load the minimap

function Resize()

{

    var mapDiv = document.getElementById("divMap");

    var sqlTXT = document.getElementById("txtSQL");

    var accordion = document.getElementById("Accordion1");

    var windowWidth = document.body.clientWidth;

    var windowHeight = document.body.clientHeight;

    mapWidth = windowWidth - 305;

    mapHeight = windowHeight  - 155;

    mapDiv.style.width = mapWidth + "px";

    mapDiv.style.height = mapHeight + "px";

    sqlTXT.style.width = mapWidth - 175 + "px";

    accordion.style.height = (windowHeight - 45) + "px";

    map.Resize(mapWidth, mapHeight);

    map.ShowMiniMap(mapWidth-205, 13, VEMiniMapSize.Large);

}
 
Since we want the Resize-function not only to be executed not only when we open the page for the first time but also whenever we change the size of the browser-window, we add an event to the top of our JavaScript (i.e. before we define the global variables):
window.onresize = Resize;
 
At this point we can already compile our project and admire our first Virtual Earth map. This map has all the basic functions which are building into the Virtual Earth MapControl: you can pan and zoom the map with the controls in the dashboardwith the mouse and with the keyboard and you can change the style and switch between 2D- and 3D-mode. 
 

  
 
We prepared the first 2 accordion panes for 3 further JavaScript functions. In the pane for the search we want to provide the ability to search for locations and to search within business listings. The function we attached to the onclick-event of the button is very simple. Basically we retrieve the text within the textboxes and use them as parameters for our Find-method:
function Search()

{

    map.Find(document.getElementById('txtWhat').value, document.getElementById('txtWhere').value);

}
 
In the pane for the VEShape-Objects we have one function which is attached as a hyperlink to the name of the checkbox. When we click on this hyperlink we want to set the centre and zoom the map to a particular location. We send 3 parameters to this function: the latitude and longitude of the centre-point as well as the zoom-level:
function ShowLocation(lat, lon, lvl)

{

    var cp = new VELatLong(lat, lon);

    map.SetCenterAndZoom(cp, lvl);

}
 
In this first step we want use the database table with the occurrences of “Food and Mouth Disease” . Since the data records represent locations all over the UK, we will set the centre-point and zoom-level in a way that we see the whole of the country. Of course we can use the API to determine these parameters but there is another easy way to do this:
 
You can use Live Search Maps to centre and zoom the map as you need it and then you can use the Share-function to copy a permalink to the clipboard, to email or to blog it:
 

  
 
The permalink would recreate the map view as you just see it and you will find the necessary parameters for latitude, longitude and zoom-level:
 
http://maps.live.com/default.aspx?v=2&cp=54.788017~-3.977051&style=r&lvl=6&tilt=-90&dir=0&alt=-1000&encType=1
 

 
After this preliminary work, we will have a look at the main function: the ability to retrieve points of interest from a database corresponding to the area that is shown in the current map view. 
 
You saw that we attached an onclick-function to the checkbox. This function will first determine if we have checked or unchecked the control. If we checked the control we will try to add the VEShapeLayer for “Foot & Mouth Disease” and then attach Virtual Earth events which fire whenever we finish panning or zooming the map. When this happens we want to execute a function, LoadFMDPoints(). Finally we execute this function for the first time. If we unchecked the control we will delete all VEShape-objects from the VEShapeLayer and then detach these Virtual Earth events:
function AddFMDPoints(control)

{

    if (document.getElementById(control).checked==false)

    {

        slFMDPoints.DeleteAllShapes();

        map.DetachEvent("onendpan", LoadFMDPoints);

        map.DetachEvent("onendzoom", LoadFMDPoints);

    }

    else

    {

        try

        {

            map.AddShapeLayer(slFMDPoints);

        }

        catch(e)

        {

        }

        map.AttachEvent("onendpan", LoadFMDPoints);

        map.AttachEvent("onendzoom", LoadFMDPoints);

        LoadFMDPoints();

    }  

}
 
The function LoadFMDPoints() implements the AJAX-call we mentioned in Figure 41. It first determines the pixel-coordinates of the upper left and lower right corner of the current map view and uses these values as parameters in the Virtual Earth method PixelToLatLong. As a result we will receive the latitudes and longitudes of the bounding rectangle. With these values, we build a URL to call a web service or in this case a generic HTTP-handler. We haven’t built this handler yet but basically it will execute the SQL query and dynamically build the JavaScript as mentioned above. Next we call a function GetXmlHttp() which determines the appropriate XMLHTTP-object for our browser and then we execute the AJAX-call asynchronously. The call will wait for the results from the HTTP-handler and then execute the result using the eval-function:
function LoadFMDPoints()

{

    map.DeleteAllShapes();



    //Retrieve the boundaries of the mapview

    var ulPixel  = new VEPixel(0, 0);

    var brPixel  = new VEPixel(mapWidth, mapHeight);

    var ulLatLong = map.PixelToLatLong(ulPixel);

    var ulLat = ulLatLong.Latitude;

    var ulLong = ulLatLong.Longitude;

    var brLatLong = map.PixelToLatLong(brPixel);

    var brLat = brLatLong.Latitude;

    var brLong = brLatLong.Longitude;



    //Build URL to call the server

    var url="./VEShapeFMDPoints.ashx?";

    url += "&ulLat=" + ulLat;

    url += "&ulLong=" + ulLong;

    url += "&brLat=" + brLat;

    url += "&brLong=" + brLong;



    //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);

    }

}



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;

}
 
The HTTP-handler, VEShapeFMDPoints.ashx, will execute the SQL-statement and dynamically build a JavaScript which will then be executed in the client’s browser. First we make sure that the culture is set to “en-UK” or anything else that interprets a “.” as decimal separator. The reason is that most Non-English languages would return the latitudes and longitudes from the database with a “,” as decimal separator and that would inevitably lead to a problem with the Virtual Earth MapControl which expects a “.”
 
Next we fetch the URL-parameters for the bounding box, set up the database connection and execute the SQL query. Please note we are not retrieving data from the spatial data type column but only from columns with numeric and text data types. While we read the data, we use a StringBuilder to create our JavaScript. The result will then be returned in the response to the AJAX-call:
'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")



'Fetch URL-parameter

Dim ulLat As String = context.Request.Params("ulLat")

Dim ulLong As String = context.Request.Params("ulLong")

Dim brLat As String = context.Request.Params("brLat")

Dim brLong As String = context.Request.Params("brLong")



'Query database(s) and create JavaScript

Dim settings As ConnectionStringSettings

Dim sb As StringBuilder = New StringBuilder

Dim myPins As String = ""



settings = ConfigurationManager.ConnectionStrings("SpatialDB")

Dim myConn As New SqlConnection(settings.ConnectionString)

myConn.Open()

Dim j As Integer = 0

Dim myQuery As String = "SELECT Latitude, Longitude, CONVERT(VARCHAR, Date, 103), City, County, Cattle, Pigs, Sheep FROM FMD WHERE (Latitude BETWEEN " + brLat + " AND " + ulLat + ") AND (Longitude BETWEEN " + ulLong + " AND " + brLong + ")"

Dim myCMD As New SqlCommand(myQuery, myConn)

Dim myReader As SqlDataReader = myCMD.ExecuteReader()

While myReader.Read()

    myPins = myPins + _

        "var shape" + j.ToString + "=new VEShape(VEShapeType.Pushpin, new VELatLong(" + myReader(0).ToString + ", " + myReader(1).ToString + "));" + _

        "shape" + j.ToString + ".SetCustomIcon('./IMG/poi_search3.gif');" + _

        "shape" + j.ToString + ".SetTitle('" + myReader(2).ToString + "');" + _

        "shape" + j.ToString + ".SetDescription('" + myReader(3).ToString + "
" + myReader(4).ToString + "

Cattle: " + myReader(5).ToString + "
Pigs: " + myReader(6).ToString + "
Sheep: " + myReader(7).ToString + "');" + _

        "slFMDPoints.AddShape(shape" + j.ToString + ");"

    j = j + 1

End While

sb.Append(myPins)

myReader.Close()

myConn.Close()



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


 
All right, that’s it. Now we can compile and test our code. Please note that so far we are not using anything that is new in SQL Server 2008. Everything we did so far would work with SQL Server 2005 as well.
 

  
 
(to be continued)
 
Technorati Tags: Virtual Earth,SQL Server 2008

 
 Click here to view the article.