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.
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: