SQL Server Spatial and Silverlight MapControl


SQL Spatial Silverlight MapControl
Fig 1 – SQL Server 2008 Spatial and Silverlight MapControl

Microsoft SQL Server 2008 introduces some spatial capabilities. Of course anytime Microsoft burps the world listens. It isn’t perhaps as mature as PostGIS, but it isn’t as expensive as Oracle either. Since it’s offered in a free Express version I wanted to give it a try, connecting the Silverlight MapController CTP with some data in MS SQL Server 2008. Here is the reference for SQL Server 2008 with new geography and geometry capabilities: Transact-SQL

To start with I needed some test data in .shp format.

The new Koordinates Beta site has a clean interface to some common vector data sources. I signed up for a free account and pulled down a version of the San Francisco Bay Area bus stops, bikeways, and transit stations in Geographic WGS 84 (EPSG:4326) coordinates as .shp format. Koordinates has built a very nice data interface and makes use of Amazon AWS to provide their web services. They include choices for format as well as a complete set of EPSG coordinate systems that make sense for the chosen data source. I selected Geographic WGS 84 (EPSG:4326) because one area SQL Server is still lacking is EPSG coordinate system support. EPSG:4326 is the one supported coordinate system in the Transact SQL geography and there is no transform function.


Koordinates map data
Fig 2 -Koordinates SF Bay Bus Stop data

With data downloaded I can move on to import. Morten Nielson’s SharpGIS has some easy to use tools for importing .shp files into the new MS SQL Server 2008 spatial. Windows Shape2Sql.exe, like many windows apps, is easier to use for one at a time file loading with its nice GUI, at least comparing to the PostgreSQL/PostGIS shp2pgsql.exe loader. However, batch loading might be a bit painful without cmd line and piping capability. SQL Server Spatial Tools

Shape2Sql
Fig 3 – Shape2Sql from sharpgis.net

Curiously SQL Server offers two data types, the lat,long spatial data type, geography, and the planar spatial data type, geometry. SRIDs can be associated with a record, but currently there is no transform capability. Hopefully this will be addressed in future versions. Looking ahead to some comparisons, I used Shape2SQL to load Bay Area Bus Stops as geometry and then again as geography. In both cases I ended up with a field named “geom”, but as different data types. The new geo data types hold the spatial features. The data load also creates a spatial index on the geom field. Once my data is loaded into MS Sql Server I need to take a look at it.

Using geom.STAsText() to show WKT, I can look inside my geography field, “geom”, and verify the loading:

SELECT TOP 1000 [ID]
  ,[AGENCYNAME]
  ,[REGIONNAME]
  ,[JURISDICTI]
  ,[ASSET_NAME]
  ,[RuleID]
  ,[CLASS]
  ,[geom]
  ,geom.STAsText()as WKT
  FROM [TestShp].[dbo].[bay-area-transit-stations]

ID AGENCYNAME REGIONNAME JURISDICTI ASSET_NAME RuleID CLASS geom WKT
1 Caltrain San Mateo San Bruno San Bruno Station 1 Rapid Rail Station 0xE6100000010C003163A9BCCF4240DB58CB7F109A5EC0 POINT (-122.40725703104128 37.622944997247032)

SQL Server
Fig 4 – SQL Server showing a Geography Display (note selection of projections for map view)

SQL Server 2008 Management Studio includes a visual display tab as well as the normal row grid. Geography data type offers a choice of 4 global projections and affords some minimalistic zoom along with a lat,long grid for verifying the data. But geometry data type ignores projection. The geometry spatial result offers a grid based on the x,y extents of the data selection. I didn’t find a way to show more than one table at at a time in the spatial view.

Here is a list of some helpful geometry functions:
OGC Methods on Geometry Instance
And here is a list of geography functions:
OGC Methods on Geography Instance

It is worth noting that there are differences in the method lists between geometry and geography:

Here is an article with some background material on geography vs geometry – SQL Server Spatial Data

Now that we have some spatial data loaded it’s time to see about hooking up to the new Silverlight MapController CTP. The basic approach is to access the spatial data table through a service on the Web side. A service reference can then be added to the Silverlight side, where it can be picked up in the C# code behind for the MapControl xaml page. This is familiar to anyone using Java to feed a Javascript client. The Database queries happen server side using a servlet with jdbc, and the client uses the asynchronous query result callback to build the display view.

Continuing with the new Silverlight MapControl CTP, I took a look at this tutorial: Johannes Kebeck Blog

However, the new geo data types are not supported through Linq designer yet. This means I wasn’t able to make use of Linq OR/M because of the geom fields. I then switched to an ADO.NET Data Service model, which appears to work, as it produces the ado web service and allows me to plug in my GeoModel.edmx GeoEntities into the new auto generated GeoDataService.svc like this:
GeoDataService : DataService<GeoEntities>

Using the service endpoint call
“http://localhost:51326/GeoDataService.svc/bay_area_transit_stations(1)”
returns the correct entry, but minus the all important geom field. Once again I’m thwarted by OR mapping, this time in EF. Examining the auto generated GeoModel.designer.cs reveals that geom field class is not generated. Perhaps this will be changed in future releases.

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<entry xml:base=”http://localhost:51326/GeoDataService.svc/”
xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices”
xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”
xmlns=”http://www.w3.org/2005/Atom”>

<id>http://localhost:51326/GeoDataService.svc/bay_area_transit_stations(1)</id>
<title type=”text”></title>
<updated>2009-04-16T21:10:50Z</updated>
<author>
<name />
</author>
<link rel=”edit” title=”bay_area_transit_stations” href=” bay_area_transit_stations(1)” />
<category term=”GeoModel.bay_area_transit_stations”
scheme=”http://schemas.microsoft.com/ado/2007/08/dataservices/scheme” />
<content type=”application/xml”>
<m:properties>
<d:ID m:type=”Edm.Int32″>1</d:ID>
<d:AGENCYNAME>Caltrain</d:AGENCYNAME>
<d:REGIONNAME>San Mateo</d:REGIONNAME>
<d:JURISDICTI>San Bruno</d:JURISDICTI>
<d:ASSET_NAME>San Bruno Station</d:ASSET_NAME>
<d:RuleID m:type=”Edm.Int64″>1</d:RuleID>
<d:CLASS>Rapid Rail Station</d:CLASS>
</m:properties>
</content>
</entry>

So neither Linq OR/M or ADO.net OR/M has caught up with the SQL Server 2008 data types yet. Apparently Linq is competing internally wtih ADO.NET EF, see Is LINQ to SQL Dead? I gather going forward .NET 4.0 will be emphasizing Entity Framework, but as far as spatial data types auto generated code isn’t there yet.

Back to manual code. I can add a Silverlight-enabled WCF service to my GeoTest.Web that connects to the database and then pass the results back to Silverlight using a Data Service Reference. From that point I can use the result inside Page.cs to populate a MapLayer on the map interface with Ellipse shapes representing the transit stops or MapPolyline for Bikeways. I know there must be a more sophisticated approach to this process, but for simplicity I’ll just pass results back to the Silverlight page via strings.

Here is a WCF service:

Notice that the STIntersects query needs to use a matching SRID in the view bounds Polygon to get a result. I also noticed that using a geometry data type causes an inordinately long query time. In my test with Bus Stops up to 5 sec was required for a query that was only in the sub second range for a geography data type with the same STIntersects query. After looking over the statistics for the two versions, I could see that the geometry version loops through the entire 79801 records to retrieve 32 rows, while the geography version uses only 4487 loops to get the same result. Perhaps I'm missing something but the geometry index doesn't appear to be working!

Here is the Page.cs that consumes the WCF service result:

This bit of code turned out to be very useful:

private SFBayDataServiceClient GetServiceClient()
{
Uri uri = new Uri(HtmlPage.Document.DocumentUri,
"SFBayDataService.svc");
EndpointAddress address = new EndpointAddress(uri);
return new SFBayDataServiceClient("*", address);
}

WCF services are easy to create and use in Visual Studio, however, deployment is a bit of a trick. The above code snippet ensures that the endpoint address follows you over to the deployment server. I have still not mastered WCF deployment and spent a good deal of trial and error time getting it working. Deployment turned out to be the most frustrating part of the process.

Now, I have a Silverlight map display with fully event driven geometry similar to SVG. ToolTips give automatic rollover info, but it is also possible to add mouse events to completely customize interaction with individual points, polylines, and polygons. I expect that this will result in more interesting event driven map overlays. Unfortunately, large numbers of features slow down map interactions so a better approach is to use a Geoserver WMS tile cache for zoom levels down to a reasonable level and then switch to shape elements.

Summary

It is still early in the game for SQL Server 2008 spatial. There are a few holes and version 1.0 is not really competitve with PostGIS in capability, but performance is decent using the geography data type. The combination of Silverlight MapControl and SQL Server spatial is good for tightly coupled webapps. However, these days OGC standards implemented by OGC servers make decoupled viewer/data stores very easy to develop. I would choose a decoupled architecture in general, unless there are constraints requiring use of Microsoft products. Future releases will likely add OR/M auto generated code for geography and geometry data types. Also useful would be a complete multigeometry capability and general EPSG support.

The nice thing is the beauty of a Silverlight MapControl and the ability to completely customize overlays with event driven interaction at the client using C# instead of javascript.

Comments are closed.