Welcome to the Data Connector project.


This project provides examples of SQL Server Spatial data displayed using Bing Maps Silverlight Control and WCF

Data Connector is an Open Source + Open Data project focused on connecting the power of Bing Maps Silverlight Control to the spatial query capabilities of SQL Server 2008.

The three basic parts of this project are the DataBase (SampleGeographyData), the WCF Services (DataConnectorWCF), and the Silverlight Client (DataConnectorUI2). The DataConnectorWCF and DataConnectorUI were developed using Visual Studio 2008 and the data base requires SQL Server 2008.

There are lots of examples and blogs that explore Silverlight Mapping as well as resources for SQL Server Spatial. However, the WCF middle tier is not as well represented, which is the reason for publishing this project. The DataConnectorUI2 example Silverlight UI is a Navigation Project with examples of connecting to each of the sample WCF Services.
  1. WKT example uses 'Well Known Text' to transmit SQL query results to the client.
  2. XAML example transforms SQL query results into XAML, and then transmits as a XAML MapLayer.
  3. Tile example builds raster png images in 256px x 256px tiles from the SQL query results. Tile Service feeds these to the client as MapTileLayer.TileSources endpoints. Tile has examples of both cached tiles and dynamic tiles. The thematic option will build dynamic tiles, otherwise tiles are cached in SQL Server as acquired.
  4. Hybrid example combines tiles and XAML. Lower zoom levels use tile service for better performance with large data sets while higher zoom levels switch to XAML service to take advantage of vector interactivity.

Live Example of DataConnector

DataBase - SampleGeographyData

SampleGeographyData is a SQL Server 2008 database with five spatial tables:

These datasets were chosen to provide useful common data resources as well as examples of each type of spatial feature: points, lines, and polygons. All of these sources are public domain, copyright free, and available for use without concern for permission or credit.

Additional data resources are easily added to SQL Server. The import utility used for this project was FME Translator from Safe Software.

The data was imported as spatial type geometry and then corrected as required to make valid before updating to an additional geography spatial column. EPSG:4326 is the SRID used for all tables and constraints were added to enforce this srid. The end result is two spatial columns, “the geo” as geography, and “the geom” as geometry. Once imported the data was modified to include an “ID” column suitable for use as a primary key necessary for spatial indexing. For the sake of uniformity each table was also modified as necessary to provide a ‘NAME’ column. For the thematic polygon example an ‘AREA’ column was added to each of the polygon tables. Finally, spatial indices were added to the geography and the geometry columns.

WCF Connector - DataConnectorWCF

DataConnectorWCF is the middle tier service layer that provides the connection between the SQL Server spatial tables and the example Bing Maps Silverlight Control. There are currently four types of WCF services provided:

1. WKT – Well Known Text is an OGC OpenGIS simple feature specification that defines a set of common textual representations for vector features.

As a standards based spatial database SQL Server supports the OGC standards wherever possible and the STAsText() function produces WKT output for both geography and geometry.
   POINT(6 10)
   LINESTRING(3 4,10 50,20 25)
   POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2))
   MULTIPOINT(3.5 5.6, 4.8 10.5)
   MULTILINESTRING((3 4,10 50,20 25),(-5 -8,-10 -8,-15 -4))
   MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)),((3 3,6 2,6 4,3 3)))
   POINT ZM (1 1 5 60)
   POINT M (1 1 80)

WKT Service provides WKT spatial feature output to client asynchronous requests. On the Silverlight client, WKT is transformed into point icons, MapPolylines, or MapPolygons by C# code behind.

Pros: WKT is simple to create, transform, and understand. The resulting vector features are available to the full range of event handlers in the client.

Cons: The transmission size of text adversely affects latency, especially in low bandwidth scenarios. This is helped somewhat by using a custom
<binaryMessageEncoding /> binding which compresses the httpTransport for transmission. Performance eventually degrades on the client when adding large numbers of vector features. This is handled by setting a recordLimit = 1000 on the client. Result sets exceeding this limit will show a message indicating that a smaller area of interest should be selected.

Using WKT pushes all styling to the client. Styling is not available to simple feature types like WKT. Although additional style columns could be added to the spatial tables, they are typically not forced on the data for flexibility at the client. However, this demands a certain level of a priori knowledge at the client for suitable styling during the transform to Silverlight vector features.
Even though WKT is text, the httpTransport is set to binaryEncoding which helps reduce transmission size.

There is an issue with Viewport AOI. Occasionally the render result is only a partial viewport. This may be due to multiple ViewChangeEnd events firing to the service. There is no guarantee of the order returned by the Asynch services and the last viewport returned may be an earlier viewport bounds. This is more likely when the reduce factor changes from one zoomlevel to another, and earlier events have fewer node results.

2. XAML – is Microsoft’s WPF/Silverlight declarative XML graphics grammar. It provides a rich range of features including all forms of multimedia and event handlers, while retaining the human readability of XML. XAML for Silverlight browser web applications is a subset of the full XAML specification.

Unlike WKT there is no direct support of XAML from SQL Server. In the XAML Service example Microsoft.SQLServer.Types.SQLGeography output from SQL Server is transformed to XAML text. Since XAML does include styling attributes, style decoration can be applied at the XAML Service or at the Silverlight client. This example chose to perform clientside styling which helps to reduce transmission size.

XAML Service currently uses simple StringBuilder Append for transforming to XAML. A future update would add a System.XML.XmlTextWriter version.

Pros: XAML is a modern, rich media, XML interchange format that is powerful enough to fulfill almost any 2D web application requirement. XAML provides an efficient XAML reader on the client side, which simplifies transforming to Silverlight vectors.
MapLayer newLayer = (MapLayer)XamlReader.Load(e.Result.XAML);

Cons: Like WKT, XAML is a text encoding with ramifications for transmission latency. The example XAML Service uses
<binaryEncoding/>to help reduce transmission size. Also like WKT the result is vector features which due to browser performance are limited by a recordLimit=1000.

Even though XAML is also text based, the httpTransport is set to binaryEncoding which helps reduce transmission size.

3. Tile – The WCF Tile Service provides a method of accommodating large features sets which cannot be rendered efficiently as vectors. This commonly occurs at wide extent views of lower zoom levels. The tile approach queries SQL Server for the result set of a desired viewport and then produces a set of 256px x 256px tiled raster png images to be transmitted to the client. Since Silverlight includes the MapTileLayer mechanism, these can be added over the background Bing Maps resource for high performance even with a large number of features. Because the tiles are rendered at the service styling must be done on the service rather than the client.

Since TileSources at the client require a raster endpoint this type of service cannot use the Asynchronous WCF service architecture found in Silverlight WCF. Fortunately WCF has a <webHttp/> endpointBehavior that is ideal for TileSources.

This follows the popular RESTful pattern:
	[WebGet( UriTemplate="{table}/{quadkey}/{thematicstr}" )]

Tile Service example provides both dynamic and cached tile service:

Cached Tile service - When initially accessed tiles are built and then stored as blob image tiles in SQL Server. Subsequent tile requests will use the blob table entries when available. Each tile blob is indexed using the quadkey as a primary key field.

Dynamic Tile Service – In some scenarios the table resources change frequently or the styling is dynamic. In these cases a static cached tile source will not work well. Tile Service provides an example of using a dynamic tile source for thematic map rendering. Although slower, tile building is still reasonable especially at higher zoom levels where the area of interest is a smaller scope.

Pros: Better performance for large data sets and larger area of interest. Cached tiles provide the best performance and scaling potential.

Cons: There are no event handlers or rich media capabilities for interactive user interfaces at the client. The CPU load on the server is significant, which presents problems for scaling. This is especially true of the uncached dynamic tiles which are continually building tiles.

See IDL problem above.
Dynamic tile building consumes a lot of CPU on the server and consequently does not scale well at the server. Effective dynamic tiles will need to use customized data tables that are tuned to the particular resolution required for display. In order to show dynamic thematic maps at a world scale a country level table with fewer nodes is required.

4. Hybrid Tile + XAML – Hybrid is not a new service. It merges the best of both Tile and XAML services. At large extent, low zoomLevels, the cached tile service is used. At small extent, higher zoom levels, it switches to the XAML Service to take advantage of all the interactive event handlers.

Some Architectural Options for Hybrid Services
Tradeoff vector vs raster

Polyline and Polygon Layers (MapPolygon, MapPolyline)scenario:

1. Low - vector poly feature counts < 300 per viewport use vector queries from DB. Taking advantage of the SQL Reduce function makes it possible to drop node counts for lower zoom levels. Point Tables/Layers < 3000

2. Medium - zoomlevels with counts > 300 per viewport use dynamic tile builder. I’m not sure what the upper limit is on performance here. I’ve only run it on fairly small tables 2000 records. Eventually dynamic tile building on server effects performance. My guesstimate would be 5000-10000 range.

3. High – Pre-seeded static tile at low levels, dynamic tiles in middle, and vectors on high levels. Pre-seed lower levels and update only nightly or weekly. No real limit on static tile pyramid.

4. Very High – low levels layer off, middle levels use a caching tile builder, vectors again at higher zoom levels.

Homogenous geographic data – hard zoom level switches
Heterogeneous geographic data – some hard switches and some soft switches (two step queries)

Point Layer:
In a point layer scenario at some level you switch from Heatmap to Cluster Icons to Pushpins and use power scaling at pushpin level. The Heatmap is a dynamic tile source or a more generalized caching tile pyramid. Experiments showed icon max limit at 3000-5000.

Hybrid zoom level based approach is the best general approach to Silverlight map pyramids where large data sets are involved.

5. SQLService – simply an experimental service for trying new features. Currently setup to experiment with streaming service. transferMode ="StreamedResponse" It is still not clear whether streamed response is really streamed or actually a byte[] buffer.

Silverlight UI DataConnectorUI2

The Client example leverages Bing Maps Silverlight Control to show examples of connecting to each of the WCF DataConnectors described above. Silverlight Navigation Application allows all of the examples to exist in a single Silverlight web application as distinct Views.

Each example uses some common controls. The Layers Control provides the basic checkbox filtering by database table layer, along with a simple opacity slider. This control is used by WKT, XAML, Tile, and Hybrid service examples.

The vector examples, WKT and XAML, also expose a DrawTools control. This provides area of interest filter definition for four types of queries:
  1. Proximity – within lat,lon radius of a center point
  2. Polyline Buffer – within lat,lon radius of a polyline
  3. AOI – rectangular area of interest
  4. Viewport – same as AOI but uses the current map viewport as the AOI.

The Tile Service example does not use the DrawTools control and simply provides Viewport area of interest for its tile production.

In addition a Metrics Control is exposed for WKT, XAML, and Hybrid. This provides some quantity and timing estimate metrics for evaluating the efficiency of a particular service approach. Note that transmit time is estimated as total time minus query time at the server minus render time at the client. The metric control could not be implemented for TileSources because there is no way to transmit additional attributes from the webHttp requests and there is no end of tile rendering event.

License Apache v2.0

Last edited Jun 26, 2010 at 2:57 AM by rkgeorge, version 2


No comments yet.