Integrating SQL Server 2008 Spatial Capabilities with Microsoft Virtual Earth

SQLServer 2008 introduces exciting possibilities for storing and manipulating spatial information. For application developers and end users, tools such as Microsoft MapPoint and services such as the Live Search Maps website (maps.live .com/#) provide many options for visualizing spatial data. I want to show you how to put the capabilities of SQL Server 2008 together with Microsoft’s Virtual Earth platform so you can not only store, query, and manipulate spatial information, but also display this spatial data. For the examples in this article, I use SQL Server 2008 and Visual Studio 2008. First, I’ll walk you through a comprehensive store locator example. Next, I’ll give you a quick tour through spatial fundamentals, and then we’ll dive into how the example was built.

Getting Started with the Store Locator Example
To help you understand how to develop an application using spatial data types, let's look at an example “store locator” application. First, you need to download the code for the example: Go to www.sqlmag.com, enter 100528 in the InstantDoc ID text box, and click the 100528.zip hotlink. Next, in Visual Studio 2008, open theVirtualEarthEg2 website by selecting File, Open, Web Site and pointing to the VirtualEarthEg2 folder that you’ve downloaded. This website uses the new SQL Server 2008 AdventureWorks2008 database, which you should download from www.codeplex .com/MSFTDBProdSamples. Depending on where you install the database, you might need to adjust the default database connection property in the web .config file.

To view the StoreLocator.aspx web page in a browser, first make sure it’s the default page for the website by right-clicking the VirtualEarthEg2 project in the Solution Explorer and selecting Property Pages. Select the Start Options tab, set the Start action to Specific page, and select StoreLocator.aspx in the dropdown box. Now, open the VirtualEarthEg2 website in a browser by selecting Debug, Start Debugging. In the Location box at the bottom of the page you can enter a location address. (By default, a zip code is already entered.) You can enter a maximum distance in the Max Distance box (default of 50 miles). When you click Find Store(s) by Location, a set of stores is placed on the map. If you hover the cursor over one of the stores, the store name, address, and distance from the location is displayed, as shown in Figure 1.

You build the Store Locator page using the Microsoft Virtual Earth Map Control along with a SQL Server 2008 database. When you click Find Store(s) by Location, the Map Control finds a longitude/latitude coordinate for the address in the Location box. Then you can use this value (along with the Max Distance value) to query a SQL Server 2008 database. The database contains store addresses which, in addition to typical attributes such as street, city, or state names, contains a longitude/latitude coordinate. Using the new built-in GEOGRAPHY data type, the Store Locator returns a list of stores that are near the location.

Spatial Primer
Before I walk you through the code for working with the comprehensive Store Locator example, let’s take a quick look at some fundamentals. The storage, manipulation, and visualization of spatial information spans a broad and deep set of technologies. I’ll cover a few basics now.

Spatial data represents the physical location and shape of objects. These objects can range from a simple point to complex objects such as a building or a country. SQL Server 2008 provides two new data types to store spatial data—GEOMETRY and GEOGRAPHY. The GEOMETRY data type assumes a flat coordinate system, while the GEOGRAPHY data type takes into account the shape of the earth. In this article, I work with the GEOGRAPHY data type, but remember that both types share many of the same methods and properties.

From an implementation standpoint, GEOGRAPHY and GEOMETRY are CLR types, although you don’t need to enable CLR on an instance of SQL Server to use them. Within your own Microsoft .NET Framework applications, you can locally instantiate or manipulate the GEOGRAPHY and GEOMETRY data types by adding a reference to the Microsoft .SqlServer.Types.dll assembly.

Using the Person.Address Table
The Person.Address table in the new Adventure- Works2008 sample database contains a column of type geography, named SpatialLocation. Let’s take a closer look at the Person.Address table to get familiar with spatial data and methods. After opening a new query window in SQL Server Management Studio (SSMS), execute the following query:

--Declare and Instantiate a local geography
  variable
DECLARE @ZipCodeGeog geography =
  geography::STGeomFromText('Point(-95.3410
  874920231 29.7070506062296)',4326);

--Select a few rows from the Person.Address
  table
SELECT TOP(5) A.AddressID AS [ID],
   A.AddressLine1,
    A.City,
    A.SpatialLocation,
    A.SpatialLocation.AsGml() AS SpatialGML,
    A.SpatialLocation.STAsText() AS
   SpatialText,
    A.SpatialLocation.STGeometryType() AS
   [Type],
    A.SpatialLocation.STNumPoints() AS
   Points,
    (A.SpatialLocation.STDistance(@
   ZipCodeGeog) / 1609.34400) AS
   DistanceInMiles
FROM Person.Address A;

The results of this query are shown in Figure 2. This query first declares and instantiates the @Zip CodeGeog geography variable using the static/shared geography method STGeomFromText. The text value ‘Point(-95.3410874920231 29.7070506062296)’,4326 represents a point located at a particular longitude and latitude. The value 4326 is a spatial reference identifier (SRID); for more about SRIDs, see the Learning Path. Note that a GEOGRAPHY data type can be instantiated from text, binary, or XML input.

Then the query selects five rows from the Person .Address table. Several GEOGRAPHY data type methods are used to display the SpatialLocation column in different formats—the XML format shown at the bottom of Figure 2 is the result of clicking one of the SpatialXML column values. The last column in the query uses the STDistance method to calculate the distance in meters (then divides by 1609.344 to derive a value in miles) between the location and the @Zip- CodeGeog variable. This is the same method used in the Store Locator example.

Continue on Page 2

Choosing a Mapping Service
Several companies (e.g., Microsoft, Google, Map- Quest) provide mapping services, both to the public and to organizations. In addition to a traditional desktop application (MapPoint), Microsoft provides two complementary but different Internet-based services for working with and displaying spatial information: The MapPoint Web Service provides location, routing, and rendering functionality. The Virtual Earth platform lets developers use integrated services to create online experiences via maps and geospatial imagery. A major component of this platform is the Virtual Earth Map Control, which contains objects, methods, and events you can use for displaying maps in an application. Microsoft itself uses this platform to deliver the maps.live.com website.

The major differences between the MapPoint and Virtual Earth services have to do with programmability and rendering. You’ll find the MapPoint Web Service, with its Simple Object Access Protocol (SOAP) API, easier to integrate with back-end processing. From a rendering perspective, the Virtual Earth Map Control returns interactive maps that an end user can easily move, zoom, and so on. Maps rendered using the Map- Point Web Service are static pictures (without further development effort). In this article, I focus on the Virtual Earth Map Control—although I also include a sample application that uses the MapPoint Web Service for geocoding/static map rendering. Note that both services, when deployed in a production environment, require a valid license. See the online Learning Path for more about licensing requirements.

Using the Virtual Earth Map Control
Virtual Earth provides an API via the Virtual Earth Map Control, which is actually an AJAX control. This means that coding against the API is done with JavaScript. For more on developing with JavaScript, see the web-exclusive sidebar “JavaScript Development,” InstantDoc ID 100533.

Let’s get started with a very simple example— displaying a map in a web page. Open the first sample website, VirtualEarthEg1, in Visual Studio 2008. Select File, Open, Web Site and point to the VirtualEarthEg1 folder on your machine. Open the Default_ShowMap.aspx web page in a browser by selecting Debug, Start Debugging. You’ll see a web page that's completely interactive. You can move it around, zoom in and out, switch between views, and so on.

After closing the browser window, in Visual Studio open the Default_ShowMap.aspx web page (double-click the file in the Solution Explorer) to view the source code. (See Listing 1) The Virtual Earth Map Control is declared and included (on line 8) using the following script:

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

In the GetMap() function (line 12), we instantiate a variable (named map) of type VEMap and call the LoadMap() function to display the map:

        function GetMap() {
            map = new
  VEMap('myMap');
            map.LoadMap();
         }

After the body of the web page is loaded into the browser, the GetMap() function is called (line 18):

<body onload="GetMap();">

I encourage you to try debugging the JavaScript in this file. (At some point in your use of the Map Control, you’ll want the ability to step through your script code.) Set a breakpoint in the GetMap() function by right-clicking the first line of the function and selecting Breakpoint, Insert Breakpoint. Then, select Debug, Start Debugging to open the Default_ShowMap.aspx web page in a browser. If Visual Studio displays a message about a setting in Microsoft Internet Explorer (IE) that you need to change in order to debug the script, then follow the directions and try again.

I’ve included several other web page samples (in the SimpleExamples folder of the website) that demonstrate how to manipulate the Map Control after you load it. For example, AddPolygonToMap.aspx shows how to add a shape (here a polygon) to a map. ShowASpecificMap.aspx, as the name implies, demonstrates how to load a specific map given a latitude and longitude coordinate. To get a better understanding of the various objects and methods available in the Map Control, be sure to visit the interactive software development kit (SDK) available at dev.live.com/ virtualearth/sdk. Then, open my next sample website, VirtualEarthEg2, in Visual Studio.

Loading Spatial Data from SQL Server
The VirtualEarthEg2 website contains four web pages: Default.aspx, StoreLocator.aspx, and two aspx pages in the ImportData folder. In the source code for Default.aspx, you’ll notice a reference to a custom script file (Map- Script.js) that contains the code necessary to load the map. I’ve simply done this for reusability purposes— all the aspx pages use this script file.

So far, you’ve seen how to load a map in a web page and use methods to manipulate it. Now let’s tackle the (seemingly) simple problem of adding and displaying spatial data on the map. First you need to understand two concepts, shape and shape layer. The Map Control supports three shapes you can add to a map—pushpin, polyline, or polygon. Pushpin is analogous to a point; this is the type of shape we’ll be working with. You can add a shape either directly to the map (on the map’s base layer) or to a custom shape layer. A shape layer lets you create or manage a collection of shapes that can be layered on top of the Map Control’s base layer.

The Map Control provides two sets of methods for importing shapes. The AddShape and AddShape- Layer methods add either a single shape or a few shapes at a time. The ImportShapeLayerData method lets you point to and import a remote collection of shapes. You can use either method to get shape data from a remote source (such as a SQL Server database). However, because we’re working with an AJAX control in the example, we need to request (and process) the remote data from within our JavaScript code.

I settled on ImportShapeLayerData for importing data from SQL Server. Using ImportShapeLayerData requires relatively little JavaScript code, and it’s efficient for importing a large number of shapes. To see an example of calling the ImportShapeLayer- Data function, view the ImportShapesFromFile .aspx file (located in the ImportData folder) in your browser. ImportShapesFromFile.aspx uses a static file, GeoRSSTest.xml, as its data source. GeoRSSTest.xml is an XML document representing a collection (or feed) of locations, with a schema based in part on the GeoRSS standard. (For information about this standard, see www.georss.org.)

Continue on Page 3

There’s a problem with ImportShapeLayer- Data: It expects a remote file as a data source. But a remote file is a static source of data, and we want to dynamically return data from a database. To get around this problem, I’ve written a simple HTTP handler that emulates a file but lets you dynamically load data. The code for the handler is in the GeoRSSHandler.vb file (located in the App_Code folder of the VirtualEarthEg2 web- site). To learn more about developing HTTP handlers in .NET, see the Visual Studio 2008 topic “HTTP Handlers and HTTP Modules Overview” at msdn.microsoft.com/en-us/library/ bb398986.aspx.

Now let’s open the file ImportShapes- FromSQL.aspx in the browser. If you look at the accompanying JavaScript for this page (line 8 in the ImportShapesFromSQL.js script file), you’ll notice we’re using a “file” named sample .georss as the data source:

       var geoRssLayer = new
  VEShapeLayer();
  
       veLayerSpec = new
  VEShapeSourceSpecification(
                       VEDataType.
  GeoRSS,
                       "sample.
  georss",

  geoRssLayer);

        map.ImportShapeLayerData(veLaye
  rSpec, onFeedLoad, 1);

The sample.georss file doesn’t exist. Our HTTP handler intercepts all requests for files ending in .georss so we can build a “file” on the fly (see Listing 2). It turns out that the data returned from the “file” sample .georss is also static. (I’ve hardcoded an XML variable in the ReturnSampleFeed function.) But we now have a framework for building dynamic content. With the ImportShapesFromSQL.aspx page still open in the browser, click the Get Store by AddressID button.

This action calls the following JavaScript:

  var geoRssLayer = new VEShapeLayer();

  
   veLayerSpec = new
VEShapeSourceSpecification(                    
                                        VEDataType.GeoRSS,           
                                             "getaddress.
georss?addressid=” + AddressID,            
                          geoRssLayer);

Getaddress.georss is intercepted by the HTTP handler, which invokes a call to SQL Server to return address information for a given AddressID. Listing 3 shows the code that returns an address for a given AddressID. The SQL-specific code is embedded in the AddressSQLQuery function. We pull back the store name and address for descriptive purposes, and the spatial column is returned in an XML format via the AsGml() method.

Finally, you can again view the StoreLocator.aspx page, our comprehensive example. I hope you now have a better understanding of the underlying technology.

I discovered that a few of the longitude/latitude values in the AdventureWorks2008 database are invalid (i.e., they don’t match the address data). I fixed these problems with an application that uses the MapPoint Web Service. For more information, see the web-exclusive sidebar “Using the MapPoint Web Service to Fix a Bug in AdventureWorks2008” at InstantDoc ID 100534. Running the sample I discuss in the web-exclusive sidebar requires a username and password for the MapPoint Web Service. You can learn more about obtaining a Virtual Earth Platform Developer Account at https://mappoint-css.live.com/mwssignup.

A Complete Spatial Solution
The GEOGRAPHY and GEOMETRY data types in SQL Server 2008 are a great way to store, query, and manipulate spatial information. To display spatial data use services such as Virtual Earth. Use both platfoms to deliver a complete spatial solution.