AJAX Map DataConnector
AJAX Map DataConnector is an Open Source + Open Data project focused on connecting the power of Bing Maps AJAX Control, Version 7.0 to the spatial query capabilities of SQL Server 2008. The examples provided here represent a starting point, showing some ways to harness SQL Server Spatial to Bing Maps UIs.
The current project consists of three components:
The SQL Server 2008 database contains tables for items to be displayed. These tables contain geography columns for the shape and location of the items. Some online resources for SQL Server 2008:
The "Client Pages" are one page with a few tabs. The different tabs are delivered as partial views via AJAX. A single script MainPage.js contains the implementation of the client. jQuery and jQuery UI are used in this implementation although other libraries could be used instead. The map and associated items are displayed using the Bing Maps AJAX Control, Version 7.0. References for the Bing Maps control are here:Bing Maps AJAX Control, Version 7.0
Drawing Objects On The Map
See the DrawingPage object in MainPage.js. For a demonstration see Vector Data On The Map. There are a few types of items that can be drawn on the map:
•Pushpins are point locations which can be represented using a builtin pushpin image or an image specified by the programmer. See the display of Earthquakes.
•Polylines are a sequence of points connected by line segments. See the display of Faults.
•Polygons are closed polylines optionally filled with a color. See the display of States, Counties, and Countries.
A Few Items Worth Mentioning
Orientation of Polygons
Polygons sent to SQL Server 2008 must be oriented counterclockwise. If not, SQL Server 2008 interprets the figure to be everything OUTSIDE the polygon, which is most of the Earth. For one thing, this is probably not correct - and for another, figures on SQL Server 2008 must be less than a hemisphere. You can demonstrate this by entering an Area Of Interest by clicking on points at the North and East sides of the map, and then double-clicking at the West to make a triangle. When you click query you'll get an error message indicating that the figure is too large or the orientation is incorrect. Click on the same points in the other order and it will be okay. Limitations on Number of Items
As the number of items being drawn on the map increases, the performance of the map decreases. For example, after drawing a few thousand faults or earthquakes on the map, panning the map becomes slow. If you need to display a number of items on a map, consider the use of Tiling.
Another limitation on the number of items, of course, is the size of the data being sent to the client. It is usually sent in a single big chunk which gets larger as the number of items increases. User Interaction With The Map
User interaction with the map includes hovering over items and clicking on items or on the map. We use the "mousemove" events to track mouse movement over the map. In DrawingPage we use the "click" and "dblclick" events to make use of mouse button clicks. To display a popup with data about an item on the map, we use the "mouseover" and "mouseout" events for the items. Since all of these events are map events, they all receive data that includes latitude and longitude. Drawing Objects Over The Map
The main reason for drawing items using the map is that the location data is provided using latitude and longitude, and events such as mouse clicks are delivered arguments containing latitude and longitude. However, since the map is just another div on the page you can also overlay it with other html objects if you wish. For example, you can float additional items such as the "Show Controls" button and the Controls panel in the examples. This will be even more useful once support for HTML5 is widespread since the use of SVG will allow more complex graphics to be drawn on the client. Updating the Map - Querying the Server
Whenever the user changes the items to display or moves the map, the items on the map must be updated. Since the map can only display items that are on the visible portion of the map we can limit the data being returned from the server by specifying that only items that intersect the bounding box need to be returned. This criterion can be combined with others such as "distance from a point". The DataController serves as a data service, providing data in GeoJSON format. Working with SQL Server Geography Types
SQL Server 2008 geography and geometry types are implemented in the assembly Microsoft.SqlServer.Types. Since this assembly can be used by the web application, geography data can be passed directly and manipulated in the web application if desired. This is often simpler and more efficient than converting to Well Known Text on the server and sending strings to the web application. As an example, the geography data for items being displayed is sent from the SQL Server to the DataService without being processed on the server. The web application translates from geography types to GeoJSON objects for transmission to the client using methods in GeoJsonConvert.cs. This file also contains example usage of SqlGeographyBuilder, which is used to construct SQL Geography instances. GeoJSON
Since the client is obtaining data via AJAX, a JSON format is simple to consume. The GeoJSON format is a standard set of objects that contain location and other data. See GeoJSON.org for a discussion and definitions. MVC2 provides us with JSON serialization of arbitrary objects, so we just define a set of classes in GeoJson.cs. Using SQL Server 2008 in Code
Data retrieval is handled by DataService.cs. The code here is simple since this is an example. Things to note in this file:
•The geography type, from the assembly Microsoft.SqlServer.Types.dll, is passed to stored procedures as a User Defined Type (UDT)
•The properties member of the GeoJSON Feature class is implemented as a Dictionary<string, string>, but any object with simple properties can be used.Stored Procedures
The stored procedures are simple, and are identical aside from the tables and columns used. We use STIntersects to get only items within a bounding box. This is a useful technique when used in combination with other, more expensive computations since the computations need not be made for items outside the area of interest. For example, CountiesByDistance is used to find counties within a distance of a given geography. Since the distance computation can be expensive for complicated geographies we avoid it for items that we cannot even plot on the map. For finding counties within an arbitrary area of interest, see CountiesByIntersection Tiling on The Map
The MVC2 web site contains a RESTful service (see TileController.cs and TileMaker.cs) that provides tiles. These tiles are 256px x 256px images that are overlaid on the map with transparency. An explanation of the Bing Maps tiling system can be found in the online MSDN documentation. For a demonstration see Tiling On The Map. Tiling or Drawing? Try Both
While tiling allows a large number of items to be displayed on the map without degrading performance, it does not easily allow for interaction since the map doesn't know about items on the map. Furthermore, tiling at large zoom levels (a closeup view) requires more and more memory for caching since there are more and more tiles to cover a given area. So a "Hybrid" map combines both techniques, using tiles at small zoom levels and drawing items at larger zoom levels. For a demonstration see Hybrid Map.