Introduction

In this tutorial we'll analyze Microsoft Virtual Earth, the new SQL Server 2008 GEOGRAPHY data type and ASP.NET AJAX. We'll build a database of longitude and latitude records for specific locations, we'll then use ASP.NET AJAX and the Virtual Earth service to exploit these locations found in the database. We're also going to build a basic application that makes use of Yahoo's Geocode service to retrieve the longitude and latitude of a specific address, thus we'll be able to add additional locations easily and quickly. How is this useful, you may be asking, to answer your question I'll give you an example. My wife has been searching for homes recently online and has been using a popular home searching web application, which will remain nameless. This application makes use of Virtual Earth and the user can browse around a city and the map is populated with homes for sale, you can then click the home, a popup appears and gives you the opportunity to view the home details. In this tutorial we'll build a similar application, however, emphasizing the use of the new SQL Server 2008 GEOGRAPHY data type.

To begin, we'll analyze the new SQL Server 2008 GEOMETRY data type, after we've got that covered and a table populated with data we'll build an ASP.NET 3.5 AJAX application to query the database and populate the Virtual Earth map with the locations found in the database within a specific region, that is, consider that you're viewing the western part of the Las Vegas city, we'll construct a viewing rectangle and then query the database for all locations falling within the bounds of that rectangle, we can then insert pushpin icons for each location.

Let's get to it, shall we?

Introducing SQL Server 2008 Spatial Support

SQL Server 2008 has introduced two new data types designed to represent spatial data, these data types are, GEOMETRY and GEOGRAPHY. The first can be used to represent planar, or Euclidean (flat-earth) data, while the latter is used to represent ellipsoidal (round-earth) data, such as, GSP longitude and latitude. These data types have been defined as a .NET CLR custom data type, much the same as the HIERARCHYID data type previously discussed.

First and foremost, what kind of uses would such data types have and how can we benefit from using these data types as opposed to simply storing coordinates in VARCHAR columns. Well, these data types include very complex and advanced methods for determining if a particular point intersects one another, the area of a polygon, the length between two objects, among many others. For example, consider that we wish to build a rectangular region of the current view point within a map (such as Microsoft’s Virtual Earth), we can do so, and then determine if any data stored within our database lies within this region (hey, that's exactly what we're going to do!). Another example, perhaps we wish to determine the length from point A to point B, again we may do so using the supplied methods for these two data types. Finally, the complex indexing algorithms used by SQL Server 2008 can significantly reduce the processing time and overhead to perform operations against spatial data, something that’s not possible without the use of these data types. These particular points are to name only a few. Let us now examine the GEOGRAPHY data type in detail. In this tutorial we're not going to concern ourselves with the GEOMETRY data type, as it has nothing to do with our main objective.

Note

We’ll examine this in greater detail in a few moments, however to put your mind at ease, the spatial objects in SQL Server 2008 are capable of representing a few different geometrical primitives, such as polygons, points, lines, etc. A polygon can be used to represent a region, maybe a lake, building, or even a region which can represent a user’s current viewing region within Virtual Earth, Google Maps, Yahoo Maps, etc to determine intersecting objects, etc.

Geography

The GEOGRAPHY data type can be used to represent ellipsoidal (round-earth) data such as GSP longitude and latitude. As the Earth is not flat, representing locations prove to be considerably more complex than that of a user-defined space, thus the GEOMETRY data type previously examined proves to be useless when representing locations on Earth. Hence the GEOGRAPHY data type, this data type makes use of longitude and latitude to represent a particular location on Earth. As you may, or may not know, longitude measures how far east or west a particular point is from a Prime Meridian, while latitude measures how far north or south a particular point is from the equator. Using these two results together, we can determine an exact location on Earth, or for that matter, any round object (assuming we’ve defined a prime meridian and the equivalent to the equator).

The prime meridian is an imaginary arc on the earth which runs north to south at which the longitude is defined to be 0°. However, unlike the latitude where the equator is universal and is defined to be 0°, the prime meridian is arbitrary. Multiple locations of the prime meridian have been defined throughout history, however, the universally accepted meridian passes through Greenwich, London, United Kingdom and was defined by the Royal Observatory, Greenwich and was established by Sir George Airy in 1851. Later, the United States Department of Defense along with scientists of other institutions and even countries began developing the WGS, short for World Geodetic System in the late 1950s. After a few iterations and revisions we’ve arrived at what’s known today as WGS84. Today WGS84 is used in global positioning systems (GSP) and is the most common prime meridian in use today, it actually also runs through Greenwich, London, United Kingdom, however lies 102.5 meters, or 336.3 feet from the original, internationally recognized meridian.

This said, as the GEOGRAPHY data type is used to represent a position on earth, the data must be represented in longitude and latitude, thus we must indicate the prime meridian we wish to utilize, this is accomplished via the Spatial Reference ID, or SRID. The SRID can be used when defining a spatial object, such as a point, polygon, line, etc. It’s important to note that a variety of different SRIDs can be used within a single GEOGRAPGY column, however only spatial objects with the same SRID can be used when performing operations against one another with the SQL Server 2008 supplied methods. The SRID you choose must be one defined in the table sys.spatial_reference_systems. If you wish to view all available SRIDs you may do so by simply selecting the content of this table as shown below.

SELECT * FROM sys.spatial_reference_systems

The SRID defining the WGS84 meridian is 4326 which also specifies a measurement unit of meter, meaning that when operations such as distance between two spatial instances are performed, the resulting value will be in meters.

Spatial Objects

The GEOGRAPHY and GEOMETRY data types support eleven different objects or instance types, however only seven of these objects (or types) are instantiable, meaning that an instance of them can be created and worked with. These seven data (or instance) types are as follows.

  • Point
  • MultiPoint
  • LineString
  • MultiLineString
  • Polygon
  • MultiPolygon
  • GeometryCollection

In this tutorial we're only going to discuss a few of the spatial objects, these are the point, linestring and polygon as that's all we really need to build our sample Virtual Earth application. But first, it’s worth noting that the GEOGRAPGY and GEOMETRY data types can recognize an instance of the above objects as string literals (actually WKT, Well-Know Text), assuming the WKT markup is well-formed, that is, despite an instance being explicitly defined, GEOGRAPHY and GEOMETRY can recognize the well-formed WKT representation of the object. For example, if you invoke the method STPointFromText() or the static Parse() method, passing as an argument the WKT markup defining a point, the GEOGRAPHY or GEOMETRY data type will create an instance of a POINT object.

Note

WKT, short for Well-Know Text is a text markup for representing vector geometry objects on a map. We’ll soon discover the markup syntax; however a few examples are as follows:

POINT(3 4)
LINESTRING(1 5, 3 3, 2 5, 1 4, 2 2)
POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))

We’ll use WKT markup text to define our vector objects in space, regardless of the space being Euclidean or ellipsoidal.

The Point

The POINT is a 0-dimensional object that can be used to represent a single point in space and has support for defining the elevation (Z) and measure (M). That is, we can define the size of the measure of the point as well as the elevation (the Z coordinate of the point in space).

Finally, the MULTIPOINT object is simply a collection of multiple points. This should be used when we wish to define multiple points for similar objects, for example, if we’re defining points for a university campus we could use the MULTIPOINT object, thus grouping multiple points for each building of the campus.

The points within a MULTIPOINT collection object can be retrieved using a few different methods, for example, consider that we wish to retrieve the third point from a collection, we could use the STGeometryN() method of the GEOMETRY or GEOGRAPHY object, passing as an argument the point in the collection we wish to retrieve.

The code listing below demonstrates the declaration of a POINT object into a T-SQL local variable using the STGeomFromText() method. Keep in mind that we’ll examine the prototype for this method in a few moments, for now just take the method at face value, that is, it can be used to define geometry/geography object types from WKT markup.

DECLARE @Geog GEOGRAPHY
SET @Geog = GEOGRAPHY::STGeomFromText('POINT (3 4)', 4326)
SELECT @Geog.Lat AS Latitude

Notice that the STGeomFromText() method accepts two arguments, the first is the WKT markup defining the spatial data type instance to create, and the second is the spatial reference system identifier, or SRID for short. We’ve previously referenced the use of the SRID, however, again, what it does is indicate the reference system for our spatial object, for example, if we’re defining a longitude and latitude for the Earth we must specify the reference system we wish to use as the reference system dictates the static references on earth that will be used to determine our exact position.

Another important thing to note is that in SQL Server 2008 CTP (Customer Technology Preview) release (as of the February CTP), the order that the longitude and latitude must be defined within the WKT markup is different than what the final release will use. For example, in the code listing above, the CTP will recognize the latitude as 3 and the longitude as 4, however the developers state that the final version (and probably any future CTPs) will reverse the order. Thus the following WKT markup:

POINT(Latitude Longitude) - February CTP
POINT(Longitude Latitude) - Final SQL Server 2008 release

The LineString

A LINESTRING is a 1-dimensional object representing a sequence of points. A line string can only be formed when at least two points are used. Now, that doesn’t mean we must first define two individual point instances and then a line string, rather, when an instance of a LINESTRING object is created, we must specify at least two points, these points will be constructed and the line string formed. To illustrate this, consider the screenshot below.

image

Notice that in the above figure a single LINESTRING object is depicted, this object consists of five points and ideally can be used to represent on a map things such as streets, paths, etc.

Creating a LINESTRING object is quite simple, we may use the STGeomFromText() method or the static Parse() method of either the GEOMETRY or GEOGRAPHY data type just as we did above when creating a point, however this time around, we must specify that we wish an instance of a LINESTRING object to be created. The code listing below demonstrates this.

DECLARE @Geom GEOGRAPHY
SET @Geom = GEOGRAPHY::STGeomFromText('LINESTRING (1 5, 3 3, 2 5, 1 4, 2 2)', 4326)
SELECT @Geom.STPointN(1).Lat

The Polygon

We know, from our geometry class in school, that a polygon must have at least three sides and the sides do not cross each other and exactly two sides meet at what’s called a vertex. The SQL Server 2008 POLYGON object is no different. A POLYGON object represents a 2-dimensional surface stored as a sequence of points defining an exterior bounding ring. The space within the ring defines the interior of a polygon. A polygon can contain interior rings as well. These rings punch holes into the polygon, thus allowing us to construct flexible interiors that match that of the real world, perhaps a land mass with a lake. The lake can be an interior ring, thus making the interior of the lake the exterior of the parent ring. For example, consider the following polygons in the figure below.

image

You’ll notice that the polygon in figure 2.10A above defines a single exterior ring. The space within the bounds of the polygon is referred to as the interior of the polygon. And figure 2.10B also consists of an external ring defining the bounds of the polygon; however notice that it also contains a single interior ring. The area within the bounds of the interior ring is part of the exterior of the polygon.

Note

The interior rings of a POLYGON object can touch both themselves and each other at a single tangent point. If the interior rings of a POLYGON cross, the instance is not valid.

Defining a POLYGON object can be accomplished invoking the same method as we’ve previously seen, that is, the STGeomFromText() method as shown below.

DECLARE @Geom GEOGRAPHY
SET @Geom = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))', 4326)

Notice that a simple POLYGON instance is defined with five points.

Now that we've got the basics of the new SQL Server 2008 spatial data objects covered, let's go ahead and build our database structure so that we can populate it with data and build our Virtual Earth application.

Our Application Data Structure

In the preceding sections we covered lots of ideas and demonstrated a lot of T-SQL code for defining spatial objects, however the goal of this tutorial is to build a Virtual Earth application which populates push-pin points based on the data found with a database and the users viewing region. Recall that above I referenced a popular web portal where homes for sale can be searched and the homes matching your search criteria are displayed on a map allowing you to browse around the map to find a home at your desired location. Well, in this tutorial we're going to duplicate this functionality using SQL Server 2008 and Virtual Earth. That said, our first order of business is to build our database structure and populate it with homes for sale. We're going to even do a little mashup and make use of Yahoo's Geocode service to allow for easy insertion of new real estate properties.

Our database structure is going to be very simple, we're going to create a single table, which will contain details on the home for sale and a GEOGRAPHY column to represent the homes location on the. The database diagram is as follows.

image

Let's go ahead and create the database structure, check out the T-SQL code below.

CREATE TABLE PropertiesForSale
(ID INT PRIMARY KEY NOT NULL IDENTITY,
 Title VARCHAR(128) NOT NULL,
 Price DECIMAL(7,0) NOT NULL,
 [Description] VARCHAR(MAX) NULL,
 Coordinates GEOGRAPHY NOT NULL)

Now that we've got our data structure in place, we need to import data into the table. I've produced an XML document of a few different home locations. These homes aren't necessarily for sale, however that's not really important to us, it's just an example.

Populating our Database

To populate our database with initial data I've constructed an XML document, we'll be able to parse this document and insert the homes along with their longitude and latitude coordinates into the database. For the initial database population we'll make use of the OPENXML() and OPENROWSET() SQL methods to populate our table from the XML document, however for future bulk insertions we'll write some C# code that'll be invoked from our ASP.NET portal. Our initial XML document structure is as follows.

<Homes>
  <House>
    <Name>Home 1</Name>
    <Price>250000</Price>
    <Description>This is the first home</Description>
    <Latitude>656.665499</Latitude>
    <Longitude>-6324.32165</Longitude>
  </House>
</Homes>

Finally, the T-SQL code that parses the XML document and inserts the data into our PropertiesForSale table is defined below.

DECLARE @homeForSale XML
DECLARE @xmlDoc INT

-- Declare a Temp table for temp storage
DECLARE @TempTable TABLE 
(Name VARCHAR(MAX), 
 Price VARCHAR(MAX),
 [Description] VARCHAR(MAX),
 Latitude VARCHAR(MAX),
 Longitude VARCHAR(MAX))

-- Use OPENROWSET() to read our XML database of
-- homes for sale
SELECT @homeForSale = BulkColumn
FROM OPENROWSET(BULK 'C:\HomesForSale.xml', SINGLE_BLOB) TempXML

-- Prepare XML Document
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @homeForSale

-- Parse the XML document object with the OPENXML()
-- method and store the result set into the @TempTable table variable
INSERT INTO @TempTable
SELECT * FROM
OPENXML (@xmlDoc, 'Homes/House', 1)
WITH
(Name VARCHAR(MAX) 'Name/.',
 Price VARCHAR(MAX) 'Price/.',
 [Description] VARCHAR(MAX) 'Description/.',
 Latitude VARCHAR(MAX) 'Latitude/.',
 Longitude VARCHAR(MAX) 'Longitude/.')
    
-- Remove the XML document from memory
EXEC sp_xml_removedocument @xmlDoc

-- Finally, insert the records into our PropertiesForSale table.
INSERT INTO PropertiesForSale
    SELECT    Name, Price, [Description], 
            GEOGRAPHY::STGeomFromText('POINT('+Latitude + ' ' + Longitude + ')', 4326) FROM @TempTable

For those of you who aren't very familiar with T-SQL, its really pretty simple. We open an XML document from the file system using the OPENROWSET() method and store the content into a SQL XML object, we then use the OPENXML() method to parse the XML structure into a rowset. The results of this parse are stored in a temporary table variable, we can then insert the content of this table variable into our PropertiesForSale table using INSERT INTO. Notice how we formed the WKT markup, we simply concatenated the latitude and longitude to construct WKT markup representing a point. This WKT string is passed as an argument to the STGeomFromText() method, also notice that the specified SRID is 4326, recall that this indicates WGS84 as the spatial reference system.

Last, but hardly least, from our ASP.NET application we may need to insert new locations in the future, thus we can also construct a simple stored procedure that can be invoked from within the data layer of our application at a latter date. The simple stored procedure is defined as follows.

CREATE PROCEDURE VE_InsertProperty
    @Name VARCHAR(128),
    @Price DECIMAL(7,0),
    @Description VARCHAR(MAX),
    @Latitude VARCHAR(24),
    @Longitude VARCHAR(24)
AS
BEGIN
    INSERT INTO PropertiesForSale VALUES (
        @Name, 
        @Price, 
        @Description, 
        GEOGRAPHY::STGeomFromText('POINT(' + @Latitude + ' ' + @Longitude + ')', 4326))
END

The above T-SQL code isn't anything we haven't already seen, however, again notice that we use the STGeomFromText() method to define our spatial point instance.

Earth to Virtual Earth

Let's put our database and SQL Server 2008 on the back burner for a few moments and analyze our presentation layer, the star of the show, Virtual Earth. While all components of our application play very important roles, Virtual Earth will take care of the presentation, that is, Virtual Earth will be the interface between our users and our back end structure. First and foremost, The Virtual Earth API is defined as JavaScript methods and objects, we can make use of Virtual Earth by simply invoking JavaScript code from client-side, thus, for communication between the client and the server we'll make heavy use of ASP.NET AJAX web services.

To begin using Virtual Earth we must include the Virtual Earth JavaScript file into the client-side portion of our application, we can do this by simply adding a <script> block to the header of our ASPX file (or HTML file) as shown below.

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

As you may have noticed, the query string v with the value of 6 specifies the version of Virtual Earth we're requesting, in our case, version 6.

We can insert a Virtual Earth object onto our page simply by creating an instance of the JavaScript class VEMap. The default constructor for this class requires that you supply the ID of an HTML control that should contain the map (for example, a <div> tag). After the VEMap object has been created, VEMap.LoadMap() must be invoked, loading the map and placing the object into the specified HTML tag. The VEMap.LoadMap() method accepts a few different arguments allowing for the customization of the initial map object, however let's just assume that we've invoked the method with no arguments, a default map would be created shown in the screenshot below.

image

Our next order of business is to build a reusable control. As its quite possible that in the future we may wish to reuse the Virtual Earth control we'll construct an ASP.NET AJAX extender, which simply extends an <asp:Panel> control and turns it into a Virtual Earth object. Let's go ahead and do that really quick.

ASP.NET AJAX Virtual Earth Extender

Alright, what we need to do is create an ASP.NET AJAX extender control that can be used to easily extend <asp:Panel> controls and convert them to a Virtual Earth map. In this tutorial I'm using Visual Studio 2008 and ASP.NET 3.5. I began by creating a simple web application, creating an App_Code folder and creating a new class which inherits from the ExtenderControl base abstract class.

The extender control will have the following public properties available so that general options associated with the Virtual Earth instance may be configured easily.

Property Description
Latitude This property allows us to set the initial latitude of the Virtual Earth map. Use this in conjunction with the longitude property below to define the initial viewing location.
Longitude This property is used to set the initial longitude for the Virtual Earth map instance.
ZoomLevel This property will let us set the zoom level of the map. The default value is 4.
ShowSwitch A Boolean property that specifies whether to show the map mode switch on the dashboard control. The default value is true.
MapStyle An enumeration that allows us to set the initial style view. That is, Road, Shaded, Aerial, Hybrid, Oblique and Birdseye.
OnChangeView The client-side method that should be invoked when the Virtual Earth change event is raised. This event is raised when ever the map changes, either zoom-in, zoom-out or moving the viewport in any direction.
InitMethod The client-side method that should be invoked upon initialization of the map and extender control.

Server-side Implementation

The JavaScript Virtual Earth library has an enumeration that can be used to define the map styles, however, as our map styles are definable via a serve-side property, we will create a server-side enumeration to ease the transition from server-side to client-side. The enumeration is defined below.

public enum VEMapStyle
{
    Road = 'r',
    Shaded = 's',
    Aerial = 'a',
    Hybrid = 'h',
    Oblique = 'o',
    Birdseye = 'b'
}

The values assigned to the enumeration properties match that of the Virtual Earth client-side enumeration, however there is one important thing to note, as the C# enum is numeric-based, the ASCII code will be used rather than the character 'r', thus before we pass the value of this enumeration to the VEMap.LoadMap() method we simply convert the character code to the string representation (more on this in a moment).

Finally, our C# extender control is defined as as follows:

[TargetControlType(typeof(Panel))]
public class VEExtender : ExtenderControl

Notice that the only valid control that can be extended by our Virtual Earth extender is a Panel (<asp:Panel). Next, our properties are defined as follows:

#region Properties
public double Latitude
{
    get
    {
        object d = (object)ViewState["Latitude"];
        return ((d == null) ? 0.0 : (double)d);
    }
    set
    {
        ViewState["Latitude"] = value;
    }
}

public double Longitude
{
    get
    {
        object d = (object)ViewState["Longitude"];
        return ((d == null) ? 0.0 : (double)d);
    }
    set
    {
        ViewState["Longitude"] = value;
    }
}

public int ZoomLevel
{
    get
    {
        object i = (object)ViewState["ZoomLevel"];
        return ((i == null) ? 4 : (int)i);
    }
    set
    {
        ViewState["ZoomLevel"] = value;
    }
}

public bool ShowSwitch
{
    get
    {
        object b = (object)ViewState["ShowSwitch"];
        return ((b == null) ? true : (bool)b);
    }
    set
    {
        ViewState["ShowSwitch"] = value;
    }
}

public VEMapStyle MapStyle
{
    get
    {
        object b = (object)ViewState["MapStyle"];
        return ((b == null) ? VEMapStyle.Aerial : (VEMapStyle)b);
    }
    set
    {
        ViewState["MapStyle"] = value;
    }
}
#endregion

#region Event Handlers
public string OnChangeView
{
    get
    {
        string s = (string)ViewState["OnChangeView"];
        return ((s == null) ? string.Empty : s);
    }
    set
    {
        ViewState["OnChangeView"] = value;
    }
}
#endregion

Last, but hardly least, our implementation of the GetScriptDescriptors() and GetScriptReferences() methods are as follows:

protected override IEnumerable<ScriptDescriptor> GetScriptDescriptors(Control targetControl)
{
    ScriptBehaviorDescriptor descriptor = new ScriptBehaviorDescriptor("MI.VirtualEarth.VEExtender", targetControl.ClientID);

    descriptor.AddProperty("latitude", this.Latitude);
    descriptor.AddProperty("longitude", this.Longitude);
    descriptor.AddProperty("zoomLevel", this.ZoomLevel);
    descriptor.AddProperty("showSwitch", this.ShowSwitch);
    descriptor.AddProperty("mapStyle", this.MapStyle);
    descriptor.AddProperty("changeViewEvent", this.OnChangeView);
    descriptor.AddProperty("id", this.ID);

    return new ScriptDescriptor[] { descriptor };
}

protected override IEnumerable<ScriptReference> GetScriptReferences()
{
    ScriptReferenceCollection srefs = new ScriptReferenceCollection();
    srefs.Add(new ScriptReference("http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6"));
    srefs.Add(new ScriptReference(Page.ResolveClientUrl("~/VEExtender.js")));
    
    return srefs;
}

Notice that we've simply included all of our properties within a ScriptBehaviorDescriptor object so that they can be retrieved from our client-side JavaScript and used when the Virtual Earth map instance is created. Note the property id. This property is used to define a value to the id property of the Component object, which is the base client-side class for AJAX behavior objects (extenders). This will allow us to make use of the $find shortcut to retrieve an instance of the Virtual Earth extender object, and thus gain access to the Virtual Earth map instance so we can place pushpins for the objects found in our database (homes for sale). For example, if we specify a client-side method that should be invoked when the map changes, we can use the $find shortcut in this method, retrieve an instance of the Virtual Earth map and then place pushpins, move the map, zoom, etc.

Client-side Implementation

From with Visual Web Developer (or Studio) 2008 we can create a new AJAX Client Behavior JavaScript file into our project, which just so happens to be a normal, run-of-the-mill JavaScript .js file, however with the basic client-side behavior structure in place (saves time since you don't have to re-type the structure, or copy+paste from another file). Anyway, our client-side behavior object is pretty straight forward, go ahead, take a look below.

Type.registerNamespace("MI.VirtualEarth");

MI.VirtualEarth.VEExtender = function(element) {
    MI.VirtualEarth.VEExtender.initializeBase(this, [element]);
    
    this._behaviorId = null;
    this._map = null;
    
    this._latitude = null;
    this._longitude = null;
    this._zoomLevel = null;
    this._showSwitch = null;
    this._mapStyle = null;
    
    this._changeViewEvent = null;
    this._initMethod = null;
    
    this._onMapChange$Delegate = Function.createDelegate(this, this._onMapChangedView);
}

MI.VirtualEarth.VEExtender.prototype = {
    initialize: function() {
        MI.VirtualEarth.VEExtender.callBaseMethod(this, 'initialize');
        
        var e = this.get_element();
        this._map = new VEMap(e.id);
        
        var centerPoint = new VELatLong(this._latitude, this._longitude);

        this._map.LoadMap(centerPoint, this._zoomLevel, 
            String.fromCharCode(this._mapStyle), false, 
            VEMapMode.Mode2D, this._showSwitch, 0);
        
        // Invoke the specified init method
        if (this._initMethod != '')
            window.setTimeout(this._initMethod, 0);
        
        // Bind the onchangeview event handler method
        if (this._changeViewEvent != '')
            this._map.AttachEvent("onchangeview", this._onMapChange$Delegate);

    },
    
    dispose: function() {        
        //Add custom dispose actions here
        MI.VirtualEarth.VEExtender.callBaseMethod(this, 'dispose');
        
        if (this._map != null)
        {
            this._map.Dispose();
            this._map = null;
        }
    },
    
    _onMapChangedView :  function(x)
    {
        window.setTimeout(this._changeViewEvent, 0);
    },
    
    get_map : function()
    {
        if (this._map != null)
            return this._map;
        else
            return null;
    },
    // The setter and getter methods have been removed from this listing

What I want you to notice is the initialize() method, this is where the action happens. Notice that a Virtual Earth map instance is created and assigned to the this._map variable. Also notice that all of the properties that can be set via markup (or programmatically) are used to define the properties of the map instance. The String.fromCharCode() method is used to convert the ASCII character code (style enumeration referenced above) to a valid character, the client-side initialization method is invoked, and the onchangeview method is bound to the Virtual Earth map control, again, allowing us to perform tasks when the map view is altered.

The client-side initialization method will be used later when we build our application to retrieve the all objects that should be displayed in the initial viewing region. Without this, the viewing region would remain empty until the user altered the view, by moving or zooming in/out, etc.

Creating the Viewing Rectangle

Why do we need to retrieve the users viewing region? The answer is simple, with the known viewing region, we can construct a a polygon object matching the users viewing region and using this polygon, determine if any of the points stored in our database intersect with this polygon. If the expression evaluate to tree, that is, we have an intersecting point, we know that the user is currently viewing an area that should contain pushpins, we can thus return a collection of longitude and latitudes and create pushpins for each point stored in our database.

Retrieving the viewing region is slightly different for each Virtual Earth map style. Let's first analyze the retrieval in 2D standard mode. First and foremost, we know that at least three points are required to build a polygon, however the longitude and latitude regions returned in 2D mode are that for only the top-left and bottom-right most coordinates, thus what we'll do is use what we know to construct a full, rectangular polygon. That is, consider the screenshot below.

image image

As we're given the top-left and bottom-right coordinates we need only combine the longitude and latitude of the available coordinates to construct the missing points as shown in the figure above (the screenshot to the right demonstrates the bounding region with a shape layer applied for a better visual, the pushpin represents the center of the viewing rectangle).

To retrieve the coordinates for 2D and 3D map styles we can invoke the VEMap.GetMapView() method. The returned value will be an instance of a VELatLongRectangle object. This class has the following properties.

Property Description
VELatLongRectangle.TopLeftLatLong An instance of a VELatLong object, which represents the upper-left point of the viewing rectangle.
VELatLongRectangle.BottomRightLatLong An instance of a VELatLong object, which represents the lower-right point of the viewing rectangle.
VELatLongRectangle.TopRightLatLong An instance of a VELatLong object, which represents the upper-right point of the viewing rectangle.

NOTE: This property is valid only for 3D map style. The value is NULL for 2D.
VELatLongRectangle.BottomLeftLatLong An instance of a VELatLong object, which represents the lower-left point of the viewing rectangle.

NOTE: This property is valid only for 3D map style. The value is NULL for 2D.
Note

The VELatLong class consists of four different properties, these are the Latitude, Longitude, Altitude and AltitudeMode.

If the map style is set to birds eye, the birds eye scene must be requested using the VEMap.GetBirdseyeScene() method. The returned object will be of type VEBirdseyeScene. We can then invoke the method VEBirdseyeScene.GetBoundingRectangle() which will also return a VELatLongRectangle object and the above rules apply, that is, the top left and bottom right are the only two points returned unless you're in 3D mode.

Note

Before we continue, its worth noting that the points in a polygon must be specified in a counter clock wise order, and the polygon, while seemingly have four points, will actually have five as the last point must also be the first, thus closing the polygon. Finally, polygons can't be larger than a hemisphere, if they are, SQL Server 2008 will throw an exception.

Alright, the rules of the game have been laid out, let's go ahead and write our method that can be used to retrieve the viewing region. This method will be a member of our extenders client-side object.

get_boundingRect : function()
{
    var rect = null;
    
    if (this._map.GetZoomLevel() < 3)
        return null;
    
    if (this._map.GetMapStyle() == VEMapStyle.Birdseye)
    {
        var be = VEMap.GetBirdseyeScene();
        rect = be.GetBoundingRectangle();
    }
    else
        rect = this._map.GetMapView()
    
    if (this._map.GetMapMode() == VEMapMode.Mode3D)
        return rect;

    rect.BottomLeftLatLong = new VELatLong(rect.BottomRightLatLong.Latitude, rect.TopLeftLatLong.Longitude);
    rect.TopRightLatLong = new VELatLong(rect.TopLeftLatLong.Latitude, rect.BottomRightLatLong.Longitude);
    
    return rect;
}

Notice that we first check the zoom level, if the level is less than 3, we're just going to assume that we probably don't need to display any pushpins, plus, the viewing region will be larger than a hemisphere and SQL Server 2008 will throw an exception when we perform an intersection test against the viewing region polygon and the points located in the database.

Now that this method is a member of our Virtual Earth extender, we'll never have to worry about retrieving the viewing rectangle, we need only call this method and a VELatLongRectangle object will be returned.

Web Services

We'll need to construct an AJAX web service and use the client-proxy implementation to communicate with the server. We need communication with our server for one very important reason, our database is stored on the server! And, unless we're going to utilize something like ADO.NET Data Services, we'll need to develop an AJAX web service and invoke the client proxy from our client-side JavaScript.

Our web service is actually very simple, it'll consist of two classes, the first is the actual web service and the other is used to represent pushpin objects, or more importantly, our homes for sale and their coordinates. Let's go ahead and create a web service. From within Visual Studio 2008 (or Web Developer 2008) right click your web site project and click Add New Item. From the window, select Web Service.

The object that will represent a pushpin (aka, a house for sale) is defined as follows:

public class PushpinsCollection : List<Pushpins>
{
}

public class Pushpins
{
    private string _name;
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }

    private string _description;
    public string Description
    {
        get { return _description; }
        set { _description = value; }
    }

    private string _latitude;
    public string Latitude
    {
        get { return _latitude; }
        set { _latitude = value; }
    }

    private string _longitude;
    public string Longitude
    {
        get { return _longitude; }
        set { _longitude = value; }
    }
}

Our web service method accepts a single argument of type List<string> (a JavaScript Array will be deserialized into the List<string> object) and constructs a WKT polygon object using the points found within the argument. Take a look for yourself.

[System.Web.Script.Services.ScriptService]
[System.Web.Script.Services.GenerateScriptType(typeof(Pushpins))]
public class VEService : System.Web.Services.WebService 
{
    [WebMethod]
    public PushpinsCollection GetData(List<string> points)
    {
        if (points.Count < 10 || points.Count > 10)
            throw new Exception("Invlaid number of points.");

        StringBuilder wkt = new StringBuilder();

        // Construct WKT
        wkt.AppendFormat("POLYGON(({0} {1}, {2} {3}, {4} {5}, {6} {7}, {8} {9}))",
            points[0], points[1], points[2], points[3], points[4], points[5],
            points[6], points[7], points[8], points[9]);

        return Dal.GetMapPoints(wkt.ToString());
    } 
}

And, finally, our DAL method connects to the SQL database and invokes the VE_GetIntersectingPoints stored procedure, which we have yet to look at, however will in a moment. But first take a look at the DAL GetMapPoints() method.

static public PushpinsCollection GetMapPoints(string WKT)
{
    PushpinsCollection pushpins = null;

    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString))
    {
        sqlConn.Open();

        using (SqlCommand cmd = new SqlCommand("VE_GetIntersectingPoints", sqlConn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@WKT", WKT);

            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
                pushpins = new PushpinsCollection();

            while (reader.Read())
            {
                Pushpins p = new Pushpins();
                p.Name = reader.GetString(1);
                p.Description = String.Format("{0:C}", reader.GetDecimal(2).ToString()) + "<br/>" + reader.GetString(3);
                p.Latitude = reader.GetDouble(4).ToString();
                p.Longitude = reader.GetDouble(5).ToString();
                pushpins.Add(p);
            }
        }
    }

    return pushpins;
}

Alright, pretty much straightforward DAL stuff, but the really cool part comes when we check out the T-SQL stored procedure. You may be amazed at the simplicity.

CREATE PROCEDURE VE_GetIntersectingPoints
    @WKT VARCHAR(MAX)
AS
BEGIN
    SELECT    ID, Title, Price, [Description], 
            Coordinates.Lat as Latitude, Coordinates.Long AS Longitude
        FROM PropertiesForSale
        WHERE Coordinates.STIntersects(GEOGRAPHY::STGeomFromText(@WKT, 4326)) = 1
END

A single argument is accepted, this should be the WKT markup representing the polygon object, which represents the Virtual Earth viewing region. Notice that all we've got to do is invoke the STIntersects() method for each point, passing as an argument the WKT markup representing the viewing region. If the expression evaluates to true (1), then the point falls within the bounds of the viewing region and should be a part of the SQL result set and thus inserted onto the map.

The Final Product

Okay, now that we've got the details our of the way we need to build our application! This should be a walk in the park as we've already done the hard part. Basically, we need a Default.aspx with a reference to our Virtual Earth extender control declared with a register directive as shown below.

<%@ Register TagPrefix="Demo" Namespace="VirtualEarth" Assembly="__code" %>

The Default.aspx file must also contain a ScriptManager control, we should also register the web service (created above) with the script manager, thus indicating to the AJAX engine that a client-side proxy should be constructed allowing us access from client-side code.

<asp:ScriptManager runat="server" ID="ScriptManager">
    <Services>
        <asp:ServiceReference Path="~/VEService.asmx" />
    </Services>
</asp:ScriptManager>

Our next order of business is to code our markup, define our Virtual Earth event handlers, sit back and enjoy the final product.

<asp:Panel runat="server" ID="veMap" CssClass="veMap"></asp:Panel>
<Demo:VEExtender runat="server" 
    id="veextender" 
    TargetControlID="veMap"
    Latitude="36.171830" 
    Longitude="-115.139764"
    MapStyle="Hybrid"
    OnChangeView="mapChanged()" 
    InitMethod="mapChanged()"></Demo:VEExtender>

That's it! Those two tags take care of just about everything, the Virtual Earth map will be placed in the <asp:Panel> control and will have the initial attributes set within the markup, in the case above, the viewing region will be placed over Las Vegas, and the map style will be hybrid. Notice the OnChangeView and InitMethod properties. These two properties will invoke our custom client-side method mapChanged(). Why are we doing this you may ask. And, the answer is that I wanted to build a generic extender, one which simply extends an asp:Panel with a Virtual Earth object, however, for our current needs, we also need the ability of performing specific tasks upon map change events, thus, we're now capable of doing this without altering our extender in anyway. Take a look below at our client-side change event handler method.

<script type="text/javascript">
    function mapChanged()
    {
        var veE = $find('veextender');
        var map = veE.get_map();
        
        var rect = veE.get_boundingRect();
        if (rect == null)
            return;
            
        var points01 = new Array(
           rect.TopLeftLatLong.Latitude,
           rect.TopLeftLatLong.Longitude,
           rect.BottomLeftLatLong.Latitude,
           rect.BottomLeftLatLong.Longitude,
           rect.BottomRightLatLong.Latitude,
           rect.BottomRightLatLong.Longitude,
           rect.TopRightLatLong.Latitude,
           rect.TopRightLatLong.Longitude,
           rect.TopLeftLatLong.Latitude,
           rect.TopLeftLatLong.Longitude
        );

        VEService.GetData(points01, getDataSuccess, getDataError);
    }
    
    function getDataSuccess(e)
    {
        if (e == null)
            return;

        // Clear all pins first
        var veE = $find('veextender');
        var map = veE.get_map();
        
        map.DeleteAllShapes();
        
        for (var i = 0; i < e.length; i++)
        {
            var shape = new VEShape(VEShapeType.Pushpin, new VELatLong(e[i].Latitude, e[i].Longitude));
            shape.SetTitle(e[i].Name);
            shape.SetDescription(e[i].Description);
            
            // Add shape to the map
            map.AddShape(shape);
        }
        
    }
    
    function getDataError(e)
    {
        alert("Error!");
    }
</script>

Notice the use of the $find shortcut. Again, what this does is retrieve an instance of the specified component, in our case, an instance of our client-side extender object. With this, we can retrieve an instance of the underlying Virtual Earth map and perform any desired task.

What's really important in the above code is how the latitude and longitude are stored. Notice that we construct, in a counter clockwise fashion, the points used to build our polygon. Ensure that you place the point coordinates in the proper order otherwise you may experience unexpected results. Lastly, our web service method is invoked, passing as an argument the array of point coordinates. Upon a successful invocation, we simply loop the item details, constructing a pushpin for each item and setting the name and description. When the mouse is hovered over a pushpin, the details will present themselves in a nice, clean popup bubble.

Our final results are shown in the screenshot below.

image

Conclusion

As you may have noticed from the screenshot above, we've got some HTML/CSS layout going on. I downloaded the template from http://www.oswd.org and am using it because we're going to add a little more functionality to our application. If you enlarge the screenshot above you may notice the link text in the menu bar that says "Add New Home". We're going to code in the ability to add new homes for sale to the database. We're going to use Yahoo's Geocode service to obtain the latitude and longitude of the specified address.

Stay tuned for the continuation to this article. There we will finish the application by adding support for querying longitude and latitude via Yahoo Geocode.

Download all source files