I ended my last post stating that I was going to write about exposing a SQL Server spatial database to the outside (internet) world via WCF Web Services. As I’m planning to consume these Web Services in Silverlight anyway I decided to cut a few corners and give WCF RIA Services a try for exposing and consuming spatial data.
Using the WCF RIA Services template for building a Silverlight Business Application (as shown here) not only gave me a Silverlight application wired up to my spatial database, but also more or less automatically provided me with the WCF Web Service I needed.
I wanted to show the spatial data contained in the Malta Polygons Table I created earlier by importing OpenStreetMap data. It turned out that this table needed a Primary Key in order to be exposed via an ADO.NET Entity Data Model. It didn’t have one yet, so I slightly changed the importer I ended up with in this post: I manually dropped the table, in my importer code I added an ID column of type int IDENTITY(1,1) NOT NULL and then imported the data once again. I’ve attached the adapted importer solution to this post.
Using Visual Studio 2010, I then created a Silverlight Business Application:
After the solution had been generated, It contained two projects: a Silverlight project and a Web project which could host the Silverlight application produced by the Silverlight project. In the SqlServerSpatialTypesInSilverlight.Web project I generated an ADO.NET Entity Model in the following way: I right clicked on the Models folder in that project and chose Add –> New Item and then in the category Data I chose ADO.NET Entity Model:
I named the model SpatialModel.edmx and clicked on the Add button.
In the next step I chose the option Generate from database and clicked Next:
My SpatialDatabase connection appeared automatically. The wizard obviously inferred it from the Data Connections in the Visual Studio Server Explorer:
I just clicked Next once again. I then answered Yes to the following question:
On the question which database objects I wanted to include, I answered like this:
I then clicked Finish.
This did not work. Even with the new Primary Key, applying the Entity Data Model Wizard to the spatial table did not get me the result I aimed for. It produced the following warning:
“The data type 'geometry' is not supported; the column 'geom' in table 'C:\SOURCE\VS2010\SQLSERVERSPATIALTYPESINSILVERLIGHT\SQLSERVERSPATIALTYPESINSILVERLIGHT.WEB\
APP_DATA\SPATIALDATABASE.MDF.dbo.MaltaPolygons' was excluded.”
As this column was exactly the one I wanted to expose, this result was virtually useless.
It turns out that User Defined Types (the Geometry type belongs to this category) are not supported in Entity Data Models.
There are lots of blog posts and forum entries about this. For GIS programmers this is obviously a huge issue. Fortunately there’s a workaround. It’s documented here and it works like this:
Using Visual Studio or SQL Server Management Studio I created a view using the following syntax:
USE [C:\DATA\SPATIALDATABASE.MDF]
CREATE VIEW MaltaPolygonsView AS SELECT FID, PointId, Name, CAST(geom AS VARBINARY(MAX)) AS geom FROM [dbo].[MaltaPolygons]
I then ran the ADO.NET Entity Model wizard again, but this time chose the new view instead of the table:
This worked. I ended up with the following basic model:
There was still a warning:
“The table/view 'C:\SOURCE\VS2010\SQLSERVERSPATIALTYPESINSILVERLIGHT\SQLSERVERSPATIALTYPESINSILVERLIGHT.WEB\APP_DATA\
SPATIALDATABASE.MDF.dbo.MaltaPolygonsView' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.”
This wasn’t what I wanted. I wanted to be able to update my view as well. But a solution for this problem could be easily found here. I just needed to to close SpatialModel.edmx, right click on it, chose Open With… and select XML (Text) Editor:
I had to change the entry
<EntitySet Name="MaltaPolygonsView" EntityType="SpatialDatabaseModel.Store.MaltaPolygonsView"
store:Type="Views" store:Schema="dbo" store:Name="MaltaPolygonsView">
into:
<EntitySet Name="MaltaPolygonsView" EntityType="SpatialDatabaseModel.Store.MaltaPolygonsView"
store:Type="Views" Schema="dbo">
and completely remove the section <DefiningQuery>…<\DefiningQuery>
Compiling my solution again resulted in a build without any remaining warnings or errors.
I now had an Entity Model on top of my spatial database, but I needed more. I wanted a Web Service exposing the Model to the outside world. This Web Service could be created by right clicking on the Services folder in the SqlServerSpatialTypesInSilverlight.Web project and choosing Add –> New Item –> Web –> Domain Service Class:
I named it SpatialDomainService. In the next screen I checked MaltaPolygonsView for inclusion and checked Enable editing as well.
I now had a WCF Web Service which can access the complete contents of the MaltaPolygons table. This Web Service method, for example, enables me to read every field of every row:
public IQueryable<MaltaPolygonsView> GetMaltaPolygonsViews()
{
return this.ObjectContext.MaltaPolygonsViews;
}
I could bind the MaltaPolygonsViews to a datagrid in the following way:
In the Silverlight application I needed to add the following XAML fragments to the Home.xaml view:
1. the following namespaces:
xmlns:riaControls="clr-namespace:System.Windows.Controls;
assembly=System.Windows.Controls.DomainServices"
xmlns:my="clr-namespace:SqlServerSpatialTypesInSilverlight.Web.Services"
xmlns:my1="clr-namespace:SqlServerSpatialTypesInSilverlight.Web.Models"
xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
(and a reference to System.Windows.Controls.DomainServices.dll)
2. the following code (just above the closing tag of the StackPanel):
<riaControls:DomainDataSource AutoLoad="True"
d:DesignData="{d:DesignInstance my1:MaltaPolygonsView, CreateList=true}"
Height="0" LoadedData="maltaPolygonsViewDomainDataSource_LoadedData"
Name="maltaPolygonsViewDomainDataSource" QueryName="GetMaltaPolygonsViewsQuery" Width="0">
<riaControls:DomainDataSource.DomainContext>
<my:SpatialDomainContext />
</riaControls:DomainDataSource.DomainContext>
</riaControls:DomainDataSource>
<sdk:DataGrid AutoGenerateColumns="False" Height="123"
ItemsSource="{Binding ElementName=maltaPolygonsViewDomainDataSource, Path=Data}"
Name="maltaPolygonsViewDataGrid" RowDetailsVisibilityMode="VisibleWhenSelected" Width="400">
<sdk:DataGrid.Columns>
<sdk:DataGridTextColumn x:Name="fIDColumn" Binding="{Binding Path=FID, Mode=OneWay}"
Header="FID" IsReadOnly="True" Width="SizeToHeader" />
<sdk:DataGridTextColumn x:Name="nameColumn" Binding="{Binding Path=Name}"
Header="Name" Width="SizeToHeader" />
<sdk:DataGridTextColumn x:Name="pointIdColumn" Binding="{Binding Path=PointId}"
Header="Point Id" Width="SizeToHeader" />
<sdk:DataGridTextColumn x:Name="geometryColumn" Binding="{Binding Path=Geometry }"
Header="Geometry" Width="SizeToHeader" />
</sdk:DataGrid.Columns>
</sdk:DataGrid>
Finally I needed to right click on the just LoadedData event handler of the DomainDataSource just copied in and choose Navigate to Event Handler. In the empty method that is automatically generated, I had to copy in some error handling stuff:
if (e.HasError)
{
System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error",
System.Windows.MessageBoxButton.OK);
e.MarkErrorAsHandled();
}
The Silverlight application compiled and I could run it now.
The only problem I still had is that the Geometries are streamed in the form of a SQL Server Geometry byte array, and not in a form easily consumable in a Silverlight application. That’s why they don’t show up in the datagrid:
I amended that as follows:
In the SqlServerSpatialTypesInSilverlight.Web project the MaltaPolygonsView class is defined in the file SpatialDomainService.metadata.cs. As it is a partial class, I could add a separate file MaltaPolygonsView.cs in the folder Services with the following contents:
using System.Runtime.Serialization;
using System.IO;
using SqlServerSpatialTypesInSilverlight.Web.Extensions;
namespace SqlServerSpatialTypesInSilverlight.Web.Models
{
public partial class MaltaPolygonsView
{
[DataMember()]
public string Geometry
{
get
{
using (MemoryStream memoryStream = new MemoryStream(geom))
{
using (BinaryReader binaryReader = new BinaryReader(memoryStream))
{
Microsoft.SqlServer.Types.SqlGeometry sqlGeometry =
new Microsoft.SqlServer.Types.SqlGeometry();
sqlGeometry.Read(binaryReader);
System.Windows.Media.Geometry geometry = sqlGeometry.ToWpfGeometry();
return geometry.ToString().Replace(";", " ");
}
}
}
set
{
using (MemoryStream memoryStream = new MemoryStream())
{
using (BinaryWriter binaryWriter = new BinaryWriter(memoryStream))
{
Microsoft.SqlServer.Types.SqlGeometry sqlGeometry =
Microsoft.SqlServer.Types.SqlGeometry.Parse(
new System.Data.SqlTypes.SqlString(value));
sqlGeometry.Write(binaryWriter);
}
geom = memoryStream.ToArray();
}
}
}
}
}
Two remarks need to be made here:
1. I haven’t tested the setter at all, so I don’t know if it works. As I’m going to use it in a later version of my Silverlight application, I’ll find out automatically.
2. The extension method ToWpfGeometry() in the getter produces a StreamGeometry. Using the ToString() method on a StreamGeometry produces a Path Data string that at some places contains semicolons instead of whitespaces, so I had to do a Replace() on that string.
In order to let this code compile, I also needed to add references to the following assemblies:
PresentationCore.dll
WindowsBase.dll
c:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll
And finally I needed to add the static class containing the SqlServer Geometry extension methods I created for an earlier post (and change its namespace to SqlServerSpatialTypesInSilverlight.Web.Extensions) in order to let the new partial class use the extension method ToWpfGeometry() (I didn’t bother to rename it to ToSilverlightGeometry(), but that would of course have been the proper thing to do in this context).
SqlServerSpatialTypesInSilverlight.zip (1,13 mb)
LinqToXmlPerformanceTest.zip (48,46 kb)
If we run the Silverlight application now, the SQL Server geometries show up in the datagrid in the form of strings which can be easily converted to Silverlight geometries. I’m going to explore how to do that in my next post.