SQL Server 2008 and SQL Azure databases support spatial data types, including spatial data indexes and queries. My company, OnTerra, has developed a tool we call AJAX Map DataConnector that helps make developing spatial maps with SQL Server 2008 and SQL Azure on Bing platforms quite simple. DataConnector’s open-source framework allows you to get started quickly, reuse existing code/best practices, and focus on your application requirements and data.
For example, many mapping applications involve storing latitude and longitude data as extra fields in relevant database tables. An individual location could be an address (which could be geocoded using the Bing Maps geocoder). It could also be more complex data like country or state polygons. Spatial data gets stored in the spatial data types using functions like STTextAsGeom(), which converts textual location data to binary spatial data types.
Aside: For complex data loading scenarios I would recommend that you consider using FME (Feature Manipulation Engine) from Safe Software (www.safe.com).
So you have spatial data in SQL Server spatial, what now? Well, there is no direct integration between SQL Server/SQL Azure and Bing Maps. This is mostly because SQL Server is server-side and Bing Maps is a client-side technology. To build web mapping applications, developers need to bring these technologies together. Architecture has much in common with the standard tiered web application, with database, Data Access Layer (DAL), web server, and client UI. However, there are some key differences for web mapping applications.
Client UI is fundamentally the map, and can be implemented using Bing Maps AJAX or Silverlight map controls. The UI displays spatial data and handles user interaction and queries. A DAL would then typically reside on the web server to connect the DB and client UI. The DAL is important because it processes queries from the client, queries the DB, and then performs data conversions. Spatial data from SQL Server is returned as Well Known Text (or other formats like WKB or GML). The WKT data includes strings of data representing points, lines, and polygons. Latitude and longitude fields are part of the strings. The client UI, however, cannot easily take WKT. So either the client or the DAL must convert it.
If the client is AJAX, this could be done using GeoJSON, a JavaScript-based format for storing geospatial data. (See figure 1 for an example client UI.) JSON has the advantage of being more easily processed by JavaScript code and less verbose than XML and others. You should consider limiting the amount of data returned via this method to a few thousand (varies based on browser performance). To load larger amounts of data consider raster tile layers.
Figure 1 – Query/display of spatial data in Bing Maps AJAX using GeoJSON.
The DataConnector projects on Codeplex provide a lot of the pieces of this architecture including sample databases, DAL, and client UI. Recently we released an AJAX version using the Bing Maps AJAX 7.0 API (http://ajaxmapdataconnector.codeplex.com). See figure 2 for details on the elements.
Figure 2 – AJAX Map DataConnector architecture.
We hope that you find the AJAX Map DataConnector to be a useful tool that makes your development process faster and easier. As always, we’ll be watching comments to catch any feedback or questions that you have.
–Steve
Steve Milroy is CEO and cofounder of OnTerra Systems (http://www.onterrasystems.com) and an expert with Location Based Services and geospatial solutions. Steve has presented at developer conferences, user groups, and business associations on many emerging technology solution and development topics.