Using the Esri File Geodatabase API

by Willem 8. February 2012 06:37

For a long time the Esri shapefile storage format has been the de facto standard for exchanging data between GIS and/or CAD applications. As the shapefile makes use of DBase IV files, which are rather ancient, the time has come to replace the shapefile with something newer, more flexible, and possibly also more performant.

 

Since 2006, Esri has been promoting the File Geodatabase as the best file based means to store Esri data. Last year Esri released the File Geodatabase API to the public, probably in an attempt to make the File Geodatabase as popular as the shapefile for publishing GIS data for general use.

 

In its first incarnation this API was only suitable for C++ programmers, because it couldn’t be used (without taking lots of trouble) by programmers using other languages than C++.

But last August Esri released version 1.1 of the File Geodatabase API. The biggest addition to this version was a .NET wrapper library. Thus, it is now fairly easy to use the API in a .NET language using Visual Studio 10.

In order to get an idea of the functionality of the API and its ease of use, I created a small File Geodatabase Viewer application in C#.

 

I’ve attached the source code to this post. The compiled application has also been added in zipped form. If you want to try it, just unzip the file and run setup.exe.

 

Disclaimer: it is just a small application to try out the API and has not extensively been tested. So use it at your own risk.

 

The application is fairly basic. It just displays the File Geodatabase Feature Classes and it can perform a few simple actions like zoom, pan and identify. It is for a large part based on code I’ve published in earlier posts. The application is a WPF application. It uses Prism and MEF because I wanted an MVVM architecture.

 

I’ve only tested the app using a relatively small File Geodatabase published by Esri, containing USA data. So at present I’ve no idea if it stays performant enough when used on larger datasets. But that’s something for a later post.

 

Make sure that, before you run the application after compiling it, you’ve copied the (unmanaged) files FileGDBAPI.dll and FileGDBAPID.dll to the debug or release folder of your solution (depending on your build configuration), as these files need to reside in the same directory as the (managed) Esri.FileGDBAPI.dll file which contains the .NET wrapper classes. You can find the files in the .\packages\Esri\FgdbAPI\X86 folder.

 

For now, let’s look at the API itself. What can be said about it?

 

The API documentation is very brief and that’s putting it mildly. Most of it has probably been generated automatically and does not add much to what can be inferred from the method names and argument names. Nevertheless, because the methods and arguments have been sensibly named, this doesn’t reduce the usability of the API.

 

It is clear that the .NET wrapper library has been created using C++\CLI (the C++ language which can be used for creating .NET applications). This shows up in the documentation. It exists only in C++\CLI notation and thus looks very weird to people not used to this language; i.e. most .NET programmers.

 

There are still a few other issues stemming from the fact that C++/CLI has been used:

 

1. a number of properties on the wrapper classes have pascalCase names instead of CamelCase ones. This is not a problem, but it looks weird to in .NET programmers.

2. the enums I’ve used are of the general type System.Enum and have to be cast to their real type before they can be used in C# or VB.NET. For example:

 

var shapeBuffer = row.GetGeometry();
Esri.FileGDB.GeometryType geometryType = shapeBuffer.geometryType;

 

does not work. I had to use an explicit cast:

 

var shapeBuffer = row.GetGeometry();
Esri.FileGDB.GeometryType geometryType = (Esri.FileGDB.GeometryType)shapeBuffer.geometryType;

 

3. there is at least one inheritance issue. The ShapeBuffer class and its child classes can be used to read and write geometries from and to a File Geodatabase. One would expect to be able to cast from e.g. a MultiPartShapeBuffer to its parent class ShapeBuffer and back again. However, that last cast back again does not work. I haven’t yet checked why exactly this cast fails, but I’m assuming it has something to do with the generated C++\CLI .NET code. Anyway, this issue caused me to write more code than would otherwise have been necessary, as I needed a switch statement here with almost (but not quite) the same code in the various case options (see the FgdbDataProvider class in the attached solution).

 

But these are all minor issues. We can easily live with them.

 

Let’s now turn to the functionality exposed via the API.

 

I haven’t looked at every single class and method contained in the API, but as far as I can see it is fairly complete. I’ve noticed only four omissions. One of them can be probably designated as questionable, but one other is, in my opinion at least, serious. Let’s start with the questionable one.

 

1. The API does not allow us to read and/or write pre-ArcGIS 10 File Geodatabases. As the File Geodatabase format has changed a lot between ArcGIS 9.3 and ArcGIS 10 and the API only became available after ArcGIS 10 was released, it is understandable that Esri didn’t take the trouble to stay backward compatible. But lots of people still use ArcGIS 9.3 and thus are producing 9.3 type File Geodatabases. These datastores cannot be used via the API without converting them to the ArcGIS 10 format first. This is not an option for people who don’t use ArcGIS.

 

2. Compressed File Geodatabases cannot be read and/ or written. If one tries to open a compressed File Geodabase, the API throws an Exception: "FileGDB compression is not installed." This is because the API doesn’t support compressed File Geodatabases. Again, this is a showstopper for people who don’t use ArcGIS.

 

3. As far as I could figure out from the documentation and my experiments, it is not possible to get information about the field names and types in a (spatial) table without querying for at least one record first; a record can provide field information, but the Table class itself cannot. I find that curious, especially because the table class can provide us with the total exent of all the geometries contained in it, which is more or less the same type of information.

 

4. The File Geodatbase API documentation clearly states that “Spatial queries will be limited to the envelope-intersects operator”. This is a severe restriction and in my opinion totally unncessary. A very complete open source .NET library is available containing alle the spatial operations we want: the nettopologysuite library. So if we really would want to implement all the spatial operations and queries we can think of in a client application, we have that library at our disposal. The only drawback then would be that much of the operations would take place at the client side, i.e. outside the File Geodatabase. This is not good for performance and thus not good for general acceptance of the File Geodatabase as a replacement for the shapefile.

 

Apart from these four issues the API looks very promising. The tests I’ve done up until now (again it must be said: with relatively small datasets containing at the most a few thousand items) indicate that access via the API to the File Geodabase is very fast. And all the geometry types that were available in the shapefile are there. We can work with Z and M values and even with multipatches. Only the non-simple features like Network- and Topology features are not exposed via the API (although they can exist inside a File Geodatabase).

 

To summarise it can be said that the File Geodatabase looks like a promising replacement for the shapefile as the de facto GIS exchange format. But in order to be able to really use it to the max outside Esri’s ArcGIS it would be necessary to have more spatial query functionality available via the File Geodatabase API.

FgdbViewerSource.zip (6,64 mb)

FgdBViewerSetup.zip (3,18 mb)

Reading Spatial Types from Denali

by Willem 17. July 2011 21:17

A few days ago SQL Server Denali CTP3 became available for download.  I downloaded and installed it. I haven’t done much with it yet, but there’s one thing I did: check if my SpatialDataImporter still worked.

 

No problems there. However, I noticed an issue when I tried to read back the spatial data from the database using one of the applications I wrote about in my earlier posts.

 

For reading the spatial data I’m using a System.Data.SqlClient.SqlDataReader instance:

 

private IEnumerable<Geometry> Read(string table, string geometryFieldName)
{
   …

    using (SqlCommand sqlCommand = new SqlCommand(String.Format("select {0} from {1}", geometryFieldName, table), sqlConnection))
    {
        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
        {
            while (sqlDataReader.Read())
            {
                SqlGeometry sqlGeometry = (SqlGeometry)sqlDataReader[geometryFieldName];
                Geometry wpfGeometry = sqlGeometry.ToWpfGeometry();
                yield return wpfGeometry;
            }
        }
    }
}

When running this code, the application throws an exception as soon as it reaches the line:

 

SqlGeometry sqlGeometry = (SqlGeometry)sqlDataReader[geometryFieldName];

 

The exception is of type InvalidCastException and has the following message:

 

“[A]Microsoft.SqlServer.Types.SqlGeometry cannot be cast to [B]Microsoft.SqlServer.Types.SqlGeometry. Type A originates from 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0.0.0.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'. Type B originates from 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'.”

 

It clearly states the problem: I’m expecting a Denali SqlGeometry instance, but the SqlDataReader hands me a SQL Server 2008 SqlGeometry instance and a cast doesn’t work.

 

To be honest, this issue already showed up in Denali CTP1, but I simply ignored it by referencing version 10 of the Microsoft.SqlServer.Types.dll instead of version 11 in my client applications.

 

I’m surprised that CTP3 still has this issue, especially as the Denali SQLCLR now works with .NET 4.0. As I no longer wanted to use the old Microsoft.SqlServer.Types.dll in my client application I looked for a better workaround.

 

The first thing I tried turned out to be a workable solution: don’t let the SqlDataReader instantiate the geometry, but simply drag out the contents of the spatial field as an array of bytes and instantiate the geometry object myself, using the byte array as an argument in its constructor.

 

This is the code I ended up with:

 

private IEnumerable<Geometry> Read(string table, string geometryFieldName)

{
    …

    using (SqlCommand sqlCommand = new SqlCommand(String.Format("select {0} from {1}", geometryFieldName, table), sqlConnection))
    {
        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
        {
            int geometryColumnIndex = GetGeometryColumnIndex(sqlDataReader, geometryFieldName);
            while (sqlDataReader.Read())
            { 
                SqlGeometry sqlGeometry = DeserializeSqlGeometry(sqlDataReader, geometryColumnIndex);
                Geometry wpfGeometry = sqlGeometry.ToWpfGeometry();
                yield return wpfGeometry;
            }
        }
    }
}

private SqlGeometry DeserializeSqlGeometry(SqlDataReader sqlDataReader, int geometryColumnIndex)
{
    SqlGeometry sqlGeometry = new SqlGeometry();
    System.Data.SqlTypes.SqlBytes bytes = sqlDataReader.GetSqlBytes(geometryColumnIndex);
    using (MemoryStream stream = new MemoryStream(bytes.Buffer))
    {
        using (BinaryReader reader = new BinaryReader(stream))
        {

            sqlGeometry.Read(reader);
        }
    }
    return sqlGeometry;
}

 

private int GetGeometryColumnIndex(SqlDataReader sqlDataReader, string geometryColumnName)
{
    int columnCount = sqlDataReader.FieldCount;
    int index = -1;
    for (int i = 0; i < columnCount; i++)
    {
        string name = sqlDataReader.GetName(i);
        {
            if (name.Equals(geometryColumnName, StringComparison.InvariantCultureIgnoreCase))
            {
                index = i;
                break;
            }
        }
    }
    return index;
}

Importing Spatial Data into SQL Server, Part 2

by Willem 13. May 2011 06:37

The application I described and made available here has one big omission. During the DevDays presentation I referred to in my last post I stressed the importance of a Spatial Reference ID. Without such an ID the coordinates of a Geometry do not make much sense. Thus, ignoring the Spatial Reference ID while importing Spatial data into SQL Server is bad. But my Spatial Data Importer is exactly doing that.

I therefore needed to repair this error. It turned out to be fairly easy. I just had to use other methods of the SharpMap Geometry and the SQL Server SqlGeometry classes: on the Geometry object I used AsBinary() instead of AsText(). And on the SqlGeometry class I used STGeomFromWKB() instead of STGeomFromText().

I replaced the line:

 

sqlCommand.Parameters.AddWithValue("@p1", feature.Geometry.AsText());

 

with:

sqlCommand.Parameters.AddWithValue("@p1", CreateSqlGeometry(feature.Geometry, spatialReferenceId));

 

 

private byte[] CreateSqlGeometry(SharpMap.Geometries.Geometry geometry, int spatialReferenceId)
{
    SqlGeometry sqlGeometry = SqlGeometry.STGeomFromWKB(

                  new System.Data.SqlTypes.SqlBytes(geometry.AsBinary()), spatialReferenceId);
    return sqlGeometry.ToByteArray();
}

 

The extension method ToByteArray() on the SqlGeometry class was borrowed from a project I discussed here:

 

public static byte[] ToByteArray(this SqlGeometry sqlGeometry)
{
    using (MemoryStream memoryStream = new MemoryStream())
    {
        using (BinaryWriter binaryWriter = new BinaryWriter(memoryStream))
        {
            sqlGeometry.Write(binaryWriter);
            binaryWriter.Flush();
        }
        return memoryStream.ToArray();
    }
}

 

The new version of the importer is attached to this post. I should warn here that I didn’t do much error checking with regard to the user input on the WPF Form.

SpatialDataImporter.zip (281,32 kb)

Denali and Silverlight 5 Beta

by Willem 5. May 2011 05:53

In the second part of my talk on April 28th at the Microsoft DevDays in The Hague I showed two Silverlight solutions. I’ve attached them to this post. I already talked about the most interesting parts in earlier posts, so I don’t need to explain much here. I only changed the solutions I talked about here and here in two ways:

 

1. I used SQL Server Denali and Silverlight 5 Beta instead of SQL Server 2008 R2 and Silverlight 4;

2. I used a simple Silverlight Application instead of a Silverlight Business Application.

 

The reason for the first change was that I of course wanted to use the newest versions of the products during my talk. The reason for the second change was that the Silverlight Business Application Template didn’t compile properly after I had installed Silverlight 5 CTP. That is probably due to some configuration error on my machine, but as the Business Application Template added too many bells and whistles to my project anyway, I decided to use the simple Application template (with RIA Services enabled) for my demos.

 

I was told that my slides had to be in Dutch, so I had assumed from this that my talk needed to be in Dutch as well. But it turned out that some people in the audience had expected my presentation to be in English, so that’s why I delivered my talk in English (or something close to it Smile ). The video from the session can be found here.

DenaliAndBingInSilverlight.zip (672,29 kb)

DenaliInSilverlight.zip (43,99 kb)

Importing Spatial Data into SQL Server

by Willem 26. April 2011 00:05

It has been silent for a while on this blog. Together with two of the smartest GIS developers I know, I’ve been busy starting up our own GIS Consultancy company and that took more time than I had planned. But I hope to be able to make posts with some higher frequency from now on.

 

Next Thursday I’ll be presenting on the Microsoft DevDays in The Hague. My talk will be about using the SQL Server “Denali” spatial types in Silverlight 5. Despite the fact that I’ll be using the newest stuff, not much has changed since my earlier posts. The newest version of Entity Framework (4.1) still doesn’t support User Defined Types and drawing huge amounts of Silverlight geometries is still not blazingly fast.

 

I’ve been looking into the issues with Bing Maps I mentioned at the end of an earlier post, but as I haven’t finished my tests yet, I’ll leave these themes for a later post.

 

While preparing my demos I decided to create my own shapefile importer for SQL Server spatial. There’s nothing wrong with Morten Nielsen’s tool, but I wanted to have complete control over the importing process and Morten’s tool is closed source. So I went ahead and got a workable solution some weeks ago.

 

Recently I talked to Paul van Wingerden and he told me that he is also writing a shapefile importer for SQL Server. We agreed to join forces and create one single tool out of both solutions, but we haven’t got the time for that yet. So be aware that the solution I’m going to present here is probably going to change a lot.

 

Nevertheless, the stuff I created and the things I encountered while writing my tool is already interesting enough for a post here. Here’s what I did:

 

There are already lots of shapefile readers floating around on the internet, so I decided not to write my own reader. I used the one created by Morten Nielsen in his SharpMap project.

 

The shapefile reading stuff in the SharpMap project is not contained in a separate assembly, but that could be easily fixed. I created two new assemblies containing the code I needed. The first one I named ShapefileLib. I started out by adding the Shapefile class and doing a compile. I fixed the errors by incrementally adding a missing class and doing a compile once again. I continued doing this until everything compiled just fine.

Two things need to be mentioned here. Firstly, I put the geometry classes in a separate assembly, called GeometryLib, because the geometry classes don’t necessarily belong to the shapefile reading classes. They could be used for reading other spatial data formats as well. Secondly, for both the ShapefileLib and the GeometryLib assembly I needed a reference to an external assembly; the spatial projection library ProjNET.dll.

 

This whole exercise took less than half an hour. I left Morten’s code largely unchanged. I just removed the #define parts that had to do with the option of using DotSpatial code instead of ProjNET code for the projections. Also, I removed a few methods on the Geometry classes (Polygon, Point and LineString) which needed a SharpMap Map. As I didn’t want this Map in my GeometryLib project and as I didn’t need these methods, I just deleted them.

 

After having created both assemblies, reading a shapefile was easy. I created a separate assembly called

ShapefileReaderLib, which just contained one single class: ShapefileReader. In order to make it a bit more generic, I put it behind an interface ISpatialDataReader (which I put in a separate assembly named SpatialInterfacesLib). This gives me the possibility to exchange the ShapefileReader for another reader without having to change the consumer side of the code. All the ShapefileReader does, is use the functionality of the SharpMap classes:

 

public IEnumerable<IFeatureDataRow> Features
{
    get
    {
        if (FeatureDataSet == null)
        {
            yield return null;
        }
        {
            shapefile.ExecuteIntersectionQuery(FullExtent, featureDataSet);
            foreach (FeatureDataRow feature in featureDataSet.Tables[0])
            {
                yield return feature as IFeatureDataRow;
            }
        }
    }
}

 

I’m using yield return statements in order to be able to return one feature at a time. Thus, I’m not clogging up memory with a huge amount of features before returning them to the client asking for the features.

 

For storing the features in SQL Server Spatial I created a SqlServerSpatialDataWriter class in a separate assembly (called SqlServerSpatialDataWriterLib). Much of its functionality is already described in my earlier posts. So the only thing worth mentioning here is that adding the shapefile geometry to the parameter collection of the SQL INSERT command turned out to be very easy. One single line of code did the trick:

 

sqlCommand.Parameters.AddWithValue("@p1", feature.Geometry.AsText());

 

This is because the SharpMap Geometry instances know how to turn themselves into WKT using the methods AsText() and of course SQL Server understands WKT.

 

I put the SqlServerSpatialDataWriter behind an interface (ISpatialDataWriter) for the same reason I used an interface for the ShapefileReader: there are other Spatial Databases besides SQL Server and I’m now able to re-use most of the functionality of my tool in case I want to import spatial data into an Oracle Spatial or a PostGIS database.

 

The last thing I needed to do was write a Client application using both the reader and writer stuff. I created a WPF application called SpatialDataImporter. As I recently did a presentation about Silverlight at the Esri Nederland GISTech, where I stressed the importance of the MVVM pattern, I couldn’t simply write a WPF application with a lot of code behind in MainWindow.xaml.cs. So I used the MVVM pattern here as well.

 

But apart from all the MVVM stuff (which is a bit of overkill here, to be honest, but is enormously important when writing larger applications) and the progress messaging and error checking stuff, the application basically just boils down to this code:

 

...
ISpatialDataReader reader = new ShapefileReader();
reader.OpenFile(shapefileName);
ISpatialDataWriter writer = new SqlServerSpatialDataWriter();
writer.Connect(serverName, databaseName);
...
writer.WriteToTable(reader, tableName, geometryFieldName, indexFieldName);

 

Using this application I was able to import the shapefiles I needed into a Denali database.

 

As always the complete code is attached to this post.

SpatialDataImporter.zip (275.64 kb)

Shortest Path

by Willem 5. November 2010 22:14

There’s still lots of things to explore with regard to Silverlight/ WPF and Web Services, and I certainly have plans to do that, but I’ve got another subject to write about first.

 

Lately I’ve been reading up on another interesting subject: the Shortest Path Problem. This problem can be stated as follows: given a network of nodes with a number of connecting routes between them, how can one figure out the shortest path between two randomly chosen nodes?

 

I tried to obtain a program in C# that shows the solution as clearly as possible, but doesn’t need to be rewritten for every different set of objects for which one wants to calculate the shortest path. I'm going to discuss that program here. But I’ll try to explain the concepts first, using information I sifted out from the many pages found on the internet which deal with this theme.

 

Like many other programming problems, this one has already been solved during the early years of computer programming. It was solved by Edsger W. Dijkstra, one of the Dutch pioneers in computer science and without any doubt the greatest among them.

 

Surprisingly enough the name Dijkstra nowadays is not widely known anymore in the Dutch software development community. This despite the fact that he has a long lasting influence on the way we work now. Not only was he the first one urging for structured programming, but he also invented the semaphore concept, which is still widely used in parallel programming.

 

I don’t have the illusion that this blog is going to change all this, but I feel that, if a Dutch GIS blog deals with the shortest path problem, Dijkstra should be mentioned prominently.

 

Dijkstra’s solution was published in 1959. He designed the algorithm three years earlier while drinking a cup of coffee on an Amsterdam terrace.

 

Here’s how he describes his algorithm:

 

------------------------------------------------------------------------------------------------

 

“[…] Find the path of minimum total length between two given nodes P and Q.

[…]

In the course of the solution the nodes are subdivided into three sets:

A. the nodes for which the path of minimum length from P is known; nodes will be added to this set in order of increasing minimum path length from node P;

B. the nodes from which the next node to be added to set A will be selected; this set comprises all those nodes that are connected to at least one node of set A but do not yet belong to A themselves;

C. the remaining nodes.

The branches are also subdivided into three sets:

I. the branches occurring in the minimal paths from node P to the nodes in set A;

II. the branches from which the next branch to be placed in set I will be selected; one and only one branch of this set will lead to each node in set B;

III. the remaining branches (rejected or not yet considered).

To start with, all nodes are in set C and all branches are in set III. We now transfer node P to set A and from then onwards repeatedly perform the following steps.

Step 1. Consider all branches r connecting the node just transferred to set A with nodes R in sets B or C. If node R belongs to set B, we investigate whether the use of branch r gives rise to a shorter path from P to R than the known path that uses the corresponding branch in set II. If this is not so, branch r is rejected; if, however, use of branch r results in a shorter connexion between P and R than hitherto obtained, it replaces the corresponding branch in set II and the latter is rejected. If the node R belongs to set C, it is added to set B and branch r is added to set II.

Step 2. Every node in set B can be connected to node P in only one way if we restrict ourselves to branches from set I and one from set II. In this sense each node in set B has a distance from node P: the node with minimum distance from P is transferred from set B to set A, and the corresponding branch is transferred from set II to set I. We then return to step I and repeat the process until node Q is transferred to set A. Then the solution has been found.”

 

------------------------------------------------------------------------------------------------

 

The beauty of this description is that it is very compact and straightforward, but doesn’t have anything to do with an implementation in some computer language. One can imagine Dijkstra sitting on the terrace of a café and drawing the different steps on a napkin.

 

There’s a video showing someone doing just that. Well, not on a napkin and probably not on a terrace, but certainly very illustrative:

 

How to find least-cost paths in a graph using Dijkstra's Algorithm.

 

This video shows two other noteworthy things:

 

1. The term Shortest Path doesn’t always have to be taken literally. The numbers assigned to an edge (or branch, as Dijkstra calls them) do not necessarily have to be a distance. It can also be a number signifying the cost of traversing via that edge. It should be noted here that Dijkstra’s solution only works if all numbers are non-negative!

2. Direction can be important. Travelling from some node A to some node B can be possible, whereas travelling in the reverse direction can be impossible or only possible with a higher cost.

 

The steps described on the Wikipedia page about Dijkstra’s solution, thought not language specific, are already formulated in such a way that they can be carried out by a computer program:

 

------------------------------------------------------------------------------------------------

“Let the node at which we are starting be called the initial node. Let the distance of node Y be the distance from the initial node to Y. Dijkstra's algorithm will assign some initial distance values and will try to improve them step by step.

1. Assign to every node a distance value. Set it to zero for our initial node and to infinity for all other nodes.

2. Mark all nodes as unvisited. Set initial node as current.

3. For current node, consider all its unvisited neighbors and calculate their tentative distance (from the initial node). For example, if current node (A) has distance of 6, and an edge connecting it with another node (B) is 2, the distance to B through A will be 6+2=8. If this distance is less than the previously recorded distance (infinity in the beginning, zero for the initial node), overwrite the distance. 

4. When we are done considering all neighbors of the current node, mark it as visited. A visited node will not be checked ever again; its distance recorded now is final and minimal.

5. If all nodes have been visited, finish. Otherwise, set the unvisited node with the smallest distance (from the initial node) as the next "current node" and continue from step 3.”

------------------------------------------------------------------------------------------------

 

Lots of implementations in many different languages can be found on the internet. Thus, I didn’t bother to write my own version from scratch. That would be re-inventing the wheel. Instead, I searched for a C# example showing just the basics of the algorithm. This turned out to be rather difficult, as many of the implementations add lots of bells and whistles to the basics I wanted to show. But in the end I found a very clear example on the MSDN site, originally written in 2003 by Scott Mitchell and updated by him in 2005 for C# 2.0 and using Generics. It can be found here.

 

I borrowed the essential parts of his code for a Visual Studio 2010 solution of my own. The reason that I did not simply use his solution was threefold: Firstly, Scott’s solution still contained a few non-Generic collection types I wanted to replace with their Generic counterparts. Secondly, in the sample code the algorithm sticks directly behind the user interface. I wanted to separate stuff a bit more to make it more reusable. And finally the algorithm works with one particular type representation of a node: a string. I wanted the algorithm to be able to work on lots of different types, thus making it still more reusable.

My solution is attached to this post.

 

When modeling the nodes and edges with their associated costs in a C# program (or a program in any other language for that matter) we can make use of a Graph. As Scott’s application already contained a clear and straightforward implementation of the Graph and a few dependent classes, I copied these over to a C# Class Library named RoutingTypes. The only things I changed here was a bit of cleaning up, some renaming of variables, changing an int to a double, and some extra validation of arguments. The classes were already using Generic types, so I didn’t need to change anything to let the Graph deal with all kinds of types.

 

The Graph class I’m using works with all kinds of .NET types, as it is defined as : public class Graph<T> : IEnumerable<T>(){…}. It contains a collection of GraphNode<T> objects. The GraphNode (which is defined as public class GraphNode<T> : Node <T>(){…}) has a collection of Node<T> objects, which contain the neighbouring Nodes, and a collection of doubles, which contain the distances to (or the cost associated with) these neighbouring Nodes.

 

Using these adjacency lists of neighbouring Nodes and the values associated with them is one way of storing Graph information. Another way would be to store all distance/cost values as an n-by-n matrix of doubles. Which of both alternatives is best depends on the type of Graph. In the case of a dense Graph (in which the number of edges is close to the maximal number of edges) a matrix will lead to faster performance, whereas in the case of a sparse Graph (a graph with only a few edges) adjacency lists will be fastest. Adjacency lists consume less memory in the case of sparse Graphs.

 

But let’s not bother too much about the possible performance issues in some cases of our implementation and turn to the rest of the code. The steps described by Dijkstra are actually implemented in another C# Class Library named Routing. The class Router<T> has a method GetShortestPath(). I’ve tried to map the steps taken there to the steps cited above from the Wikipedia page:

 

public class Router<T>
{
    public RoutingResult<T> GetShortestPath(Graph<T> items, T from, T to)
    {
        Dictionary<T, double>  distances = new Dictionary<T, double>();
        Dictionary<T, T>  routes = new Dictionary<T, T>();
        // 1. Assign to every node a distance value. Set it to zero for our initial
        //  node and to infinity for all other nodes.
        InitialiseDistancesAndRoutes(distances, routes, items);
        SetInitialDistanceOfStartToZero(from, distances);

       // 2. Mark all nodes as unvisited. Set initial node as current
        //  (done in the first call of GetNodeWithSmallestDistanceValue).
        NodeList<T> nodes = items.Nodes;

        while (nodes.Count > 0)
        {
            GraphNode<T> currentNode = GetNodeWithSmallestDistanceValue(distances, nodes);              

            // 3. For current node, consider all its unvisited neighbors and calculate
            //  their tentative distance (from the initial node). 
            if (currentNode.Neighbours != null)
            {
                for (int i = 0; i < currentNode.Neighbours.Count; i++)
                {
                    UpdateDistanceAndRoute(distances, routes, currentNode.Value,
                           currentNode.Neighbours[i].Value, currentNode.Costs[i]);
                }
            }
           // 4. When we are done considering all neighbors of the current node,
            //  mark it as visited. 
            nodes.Remove(currentNode);
           // 5. If all nodes have been visited, finish. Otherwise, set the unvisited
            //  node with the smallest distance (from the initial node) as the next
            //  "current node" and continue from step 3.
        }
        RoutingResult<T> result = CreateRoutingResult(distances, routes, from, to);
        return result;
    }

    private void InitialiseDistancesAndRoutes(Dictionary<T, double> distances,
  Dictionary<T, T> routes, Graph<T> items)
    {
        foreach (T item in items)
        {
            distances.Add(item, Double.MaxValue);
            routes.Add(item, default(T));
        }
    }

    private void SetInitialDistanceOfStartToZero(T from, Dictionary<T, double> distances)
    {
        distances[from] = 0;
    }

    private GraphNode<T> GetNodeWithSmallestDistanceValue(Dictionary<T, double> distances,
              NodeList<T> nodes)
    {
        double minimumDistance = double.MaxValue;
        GraphNode<T> minimumDistanceNode = null;
        foreach (GraphNode<T> node in nodes)
        {
            if ((distances[node.Value]) <= minimumDistance)
            {
                minimumDistance = distances[node.Value];
                minimumDistanceNode = node;
            }
        }
        return minimumDistanceNode;
    }

    private void UpdateDistanceAndRoute(Dictionary<T, double>  distances,
              Dictionary<T, T>  routes, T item, T neighbour, double cost)
    {
        double distanceToItem = distances[item];
        double distanceToNeighbour = distances[neighbour];

        if (distanceToNeighbour > distanceToItem + cost)
        {
            distances[neighbour] = distanceToItem + cost;
            routes[neighbour] = item;
        }
    }

    private RoutingResult<T> CreateRoutingResult(Dictionary<T, double>  distances,
   Dictionary<T, T>  routes, T from, T to)
    {
        RoutingResult<T> result = new RoutingResult<T> { TotalDistance = distances[to] };
        Stack<T> traceBackSteps = GetRoutingItemsInReverseOrder(routes, from, to);
        result.PathItems = new List<T>();
        while (traceBackSteps.Count > 0)
        {
            result.PathItems.Add(traceBackSteps.Pop());
        }
        return result;
    }

    private Stack<T> GetRoutingItemsInReverseOrder(Dictionary<T, T>  routeTable,T from, T to)
    {
        Stack<T> traceBackSteps = new Stack<T>();
        T current = to;
        traceBackSteps.Push(current);
        do
        {
            current = routeTable[current];
            traceBackSteps.Push(current);
        } while (!current.Equals(from));

        return traceBackSteps;
    }
}

 

The method is relatively short because it makes use of a bunch of helper methods. I’ve tried to give these methods names that clearly describe what they do. The helper methods are also short, so hopefully they are easy to read.

 

The result of the method GetShortestPath() is returned in the shape of a RoutingResult<T> instance. This instance should contain the obtained minimum distance between the two Nodes concerned, as well as the series of Nodes to be traversed when travelling this distance. In case no path can be found, the returned value is Double.MaxValue.

To illustrate that one can use different types in the Router<T> class, I created a third C# Class Library containing three types: City, Vertex, and Item. They are very simple:

 

public class City
{
    public string Name { get; set; }
    public Point Position { get; set; }
}
 
public class Vertex
{
    public string Name { get; set; }
}
 
public class Item
{
    public string Name { get; set; }
}
 

These types are used in a WPF application named RoutingTester. In this application three Graphs are created; one of type City (identical to the Graph in the article by Scott Mitchell), one of type Vertex (identical to the example Graph on the Wikipedia page), and one of type Item (identical to the Graph on the video shown above). They are used in the following way:

 
private void ShortestRouteBetweenCities()
{
    Graph<City> cityGraph = CreateCitiesTestGraph();
    City from = GetFirstItem(cityGraph);
    City to = GetLastItem(cityGraph);

    Router<City> router = new Router<City>();
    RoutingResult<City> result = router.GetShortestPath(cityGraph, from, to);
   
StringBuilder stringbuilder = new StringBuilder();
    stringbuilder.AppendLine(String.Format("The shortest path from {0} to {1} is {2}" +
        " miles and goes as follows: ", from.Name, to.Name, result.TotalDistance));
    foreach (City city in result.PathItems)
    {
        stringbuilder.AppendLine(city.Name);
    }
    MessageBox.Show(stringbuilder.ToString());
}

private void ShortestRouteBetweenVertices()
{
    Graph<Vertex> vertexGraph = CreateVerticesTestGraph();
    Vertex from = GetFirstItem(vertexGraph);
    Vertex to = GetLastItem(vertexGraph);

    Router<Vertex> router = new Router<Vertex>();
    RoutingResult<Vertex> result = router.GetShortestPath(vertexGraph, from, to);
   
StringBuilder stringbuilder = new StringBuilder();
    stringbuilder.AppendLine(String.Format("The shortest path from {0} to {1} is {2}" +
        " cost units and goes as follows: ", from.Name, to.Name, result.TotalDistance));
    foreach (Vertex vertex in result.PathItems)
    {
        stringbuilder.AppendLine(vertex.Name);
    }
    MessageBox.Show(stringbuilder.ToString());
}
 
private void ShortestRouteBetweenItems()
{
    Graph<Item> itemGraph = CreateItemsTestGraph();
    Item from = GetFirstItem(itemGraph);
    Item to = GetLastItem(itemGraph);

    Router<Item> router = new Router<Item>();
    RoutingResult<Item> result = router.GetShortestPath(itemGraph, from, to);
    StringBuilder stringbuilder = new StringBuilder();
    stringbuilder.AppendLine(String.Format("The shortest path from {0} to {1} is {2}" +
        " cost units and goes as follows: ", from.Name, to.Name, result.TotalDistance));
    foreach (Item item in result.PathItems)
    {
        stringbuilder.AppendLine(item.Name);
    }
    MessageBox.Show(stringbuilder.ToString());
}
 

The most relevant lines in these three methods are shown in red. They illustrate that the Router class can indeed be used for different types. The first two examples assume that a connection A – B also implies an equivalent connection B – A. The last example shows that the Router class can also be used for directed Graphs (i.e. Graphs where a connection A – B with a cost of 10 does not automatically imply a connection B - A with the same cost, but could have no connection B – A at all, or have a connection B – A with another cost factor).

 

For more details please do have a look at the solution attached to this post.

Routing.zip (24.08 kb)

Spatial Web Services in Silverlight, Part 4

by Willem 9. September 2010 04:16

As I stated in my last post, the Silverlight application I attached to it is still very basic. It doesn’t even show some basic information about the custom polygons added via the WCF RIA Services mechanism. So I tried to do something about that. The first thing that came to mind was providing information via a tooltip.

 

This turned out to be very simple. Microsoft has provided the ToolTipService class, which can be applied to any Silverlight class which inherits from System.Windows.DependencyObject. As both Bing’s MapPolygon class and Ricky Brundritt’s MapMultiPolygon class inherit indirectly from System.Windows.UIElement and thus from System.Windows.DependencyObject, instances of these classes could easily be provided with a tooltip.

 

In the LoadCompleted() method of the Home.xaml.cs class of my Silverlight application I replaced two statements (shown like this) by a call to a newly created method named AddPolygonToMap():

 

private void LoadCompleted(LoadOperation<MunicipalitiesView> args)
{
    foreach (MunicipalitiesView item in args.Entities)
    {
        if (IsMultiPolygon(item.Geometry))
        {
            MapMultiPolygon multiPolygon = CreateMultiPolygon(item.Geometry);

            map1.Children.Add(polygon);

                AddPolygonToMap(multiPolygon, item.GM_NAAM);
        }
        else
        {
            MapPolygon polygon = CreatePolygon(item.Geometry);

            map1.Children.Add(polygon);
            AddPolygonToMap(polygon, item.GM_NAAM);
        }
    }
}

 

 
private void AddPolygonToMap(System.Windows.UIElement polygon, string labelText)
{
    ToolTipService.SetToolTip(polygon, labelText);
    map1.Children.Add(polygon);
}

 

This method uses the ToolTipService’s static method SetToolTip() to add a text (in this case the municipality name) to the polygons. The result looks like this:

 

image

 

It is still very basic, but at least we can see some info about the polygons.

Spatial Web Services in Silverlight, Part 3

by Willem 1. September 2010 06:46

The Silverlight application attached to Part 2 of this series can show SQL Server Geometries on a Canvas, but it would be stretching it a little if we called it a Mapping application. A real Mapping application allows the user to zoom and pan, to get some information about the items shown, etc.

 

We could try to create our own Map Control, but that would take a lot of time and energy. Fortunately Microsoft has created one already: the Bing Maps Silverlight Control. Apart from the fact that this control already displays a map by default (one of the Bing Maps: Road or Aerial), it has an API which allows us to programmatically add our own spatial data on top of the default map.

 

So I decided to use this Map Control instead of rolling my own. I created a new application in which I repeated most of the steps described in Part 1 of this series.

I only changed two things: apart from using the Bing Maps Silverlight Control, I also changed the spatial dataset to be displayed. Here’s what I did:

 

I chose a freely available spatial dataset of the Dutch municipalities for displaying. It can be downloaded from Statistics Netherlands. It poses two complications (which is why I chose it instead of the OpenStreetMap data): it is in a binary format which is not supported by the SQL Server Import and Export Wizard and it has a Spatial Reference which is not usable in the Bing Maps Control.

 

The binary format is that of the shapefile. It is well documented and writing an importer would not be difficult. But Morten Nielsen already wrote one. It can be found here.

 

The Dutch Spatial Reference is a fairly complicated one. It is based on the European Reference System ETRS89. (which more or less coincides with the WGS84 Reference System, used in GPS systems; this is oversimplifying things somewhat. Look here for the details). Transforming ETRS89 coordinates in latitude-longitude to Dutch Rijksdriehoekstelsel coordinates takes no less than three steps and the calculations are very complex. However, a simpler approximation of this transformation has been published some years ago. And as we’re not interested in very high accuracies, we can use these published formulas.

 

I created a new SQL Server Express database named SpatialData. Using Morten Nielsens Shape2SQL tool, I imported the gem_2009_gen shapefile into the new spatial database and named the spatial dataset Municipalities. A primary key and a spatial index were automatically created by the import tool.

 

For the Silverlight application making use of this spatial database, I created a new Visual Studio 2010 project.  Once again I created a Silverlight Business Application and named it SqlServerAndBingInSilverlight. In the same way as described in the first part of this series, I created a Database View for the Municipalities table and then generated an ADO.NET Entity Model containing this view. I named it MunicipalitiesView and tweaked the SpatialModel.edmx XML file in the same way as shown before. Like in my earlier project I created a Domain Service Class named MunicipalitiesDomainService and included the MunicipalitiesView. An extra Property of type string was once again added via a partial class for MunicipalitiesView:

 

public partial class MunicipalitiesView
{

     [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);
                     return sqlGeometry.ToPolygonString();
                 }
             }
         }
         set
         {
             // TODO Implement this if you want to store data back to the database
         }
     }
}
 

The extension method ToPolygonString() looks somewhat like the extension method I used in the first and second parts(ToWpfGeometry() [a.k.a. ToSilverlightGeometry()]):

 

public static class SqlGeometryHelper
{
    public static string ToPolygonString(this SqlGeometry sqlGeometry)
    {
        StringBuilder stringBuilder = new StringBuilder();
        if (sqlGeometry != null && !sqlGeometry.IsNull)
        {
            sqlGeometry = sqlGeometry.MakeValid();
            for (int geometryIndex = 0; geometryIndex < (int)sqlGeometry.STNumGeometries();
                   geometryIndex++)
            {
                SqlGeometry subGeometry = sqlGeometry.STGeometryN(geometryIndex + 1);
                if (subGeometry.STGeometryType() == "Polygon" ||
                        subGeometry.STGeometryType() == "MultiPolygon")
                {
                    Point[] points = GetPointsFromSqlGeometry(subGeometry.STExteriorRing());
                    Point[] projectedPoints = RDNewToWGS84(points);
                    if (geometryIndex > 0)
                    {
                        stringBuilder.Append(";");
                    }
                    AddSegmentToGeometry(stringBuilder, projectedPoints);
                }
            }
        }
        return stringBuilder.ToString();
    }


    private static Point[] GetPointsFromSqlGeometry(SqlGeometry sqlGeometry)
    {
        Point[] points = new Point[(Int32)(sqlGeometry.STNumPoints())];

        for (int i = 0; i < sqlGeometry.STNumPoints(); i++)
        {
            SqlGeometry pointGeometry = sqlGeometry.STPointN(i + 1);
            points[i] = new Point(pointGeometry.STX.Value, pointGeometry.STY.Value);
        }
        return points;
    }

    private static void AddSegmentToGeometry(StringBuilder stringBuilder, Point[] points)
    {
        for (int i = 0; i < points.Length; i++)
        {
            if (i > 0)
            {
                stringBuilder.Append(",");
            }
            stringBuilder.Append(String.Format("{0},{1}",
                  points[i].X.ToString(CultureInfo.InvariantCulture), 
                  points[i].Y.ToString(CultureInfo.InvariantCulture)));
        }
    }

    private static Point[] RDNewToWGS84(Point[] points)
    {
        Point[] tranformedPoints = new Point[points.Length];
        for (int i = 0; i < tranformedPoints.Length; i++)
        {
            tranformedPoints[i] = RDNewToWGS84(points[i]);
        }
        return tranformedPoints;
    }

    private static Point RDNewToWGS84(Point point)
    {
        double dX = (point.X - 155000) * Math.Pow(10, -5);
        double dY = (point.Y - 463000) * Math.Pow(10, -5);

        double latitudeBasePoint = (3235.65389 * dY) + (-32.58297 * Math.Pow(dX, 2)) +
            (-0.2475 * Math.Pow(dY, 2)) + (-0.84978 * Math.Pow(dX, 2) * dY) +
            (-0.0655 * Math.Pow(dY, 3)) + (-0.01709 * Math.Pow(dX, 2) * Math.Pow(dY, 2)) +
            (-0.00738 * dX) + (0.0053 * Math.Pow(dX, 4)) +
            (-0.00039 * Math.Pow(dX, 2) * Math.Pow(dY, 3)) +
            (0.00033 * Math.Pow(dX, 4) * dY) + (-0.00012 * dX * dY);

        double longitudeBasePoint = (5260.52916 * dX) + (105.94684 * dX * dY) +
            (2.45656 * dX * Math.Pow(dY, 2)) + (-0.81885 * Math.Pow(dX, 3)) +
            (0.05594 * dX * Math.Pow(dY, 3)) + (-0.05607 * Math.Pow(dX, 3) * dY) +
            (0.01199 * dY) + (-0.00256 * Math.Pow(dX, 3) * Math.Pow(dY, 2)) +
            (0.00128 * dX * Math.Pow(dY, 4)) + (0.00022 * Math.Pow(dY, 2)) +
            (-0.00022 * Math.Pow(dX, 2)) + (0.00026 * Math.Pow(dX, 5));

        double latitude = 52.15517440 + (latitudeBasePoint / 3600);
        double longitude = 5.38720621 + (longitudeBasePoint / 3600);

        return new Point(longitude, latitude);
    }

}

 

However, it has two important differences. Firstly, I didn’t construct the coordinates in the form of a Silverlight Path string. As the Bing Maps Silverlight API works with a totally different format and as I’m constructing the Bing Maps Geometry instances at the Client side anyway, I simply constructed strings containing just coordinates, separated by commas (in case the coordinates belong to the same ring) and semicolons (in case a new ring starts). Secondly, I needed to transform the Dutch Rijksdriehoekstelsel coordinates to WGS84 coordinates. This is what the method RDNewToWGS84() does. This method performs the simplified transformation I mentioned earlier.

 

OK, apart from the pieces of code I showed here, at the server side there’s nothing new compared to the project I wrote about in the first two parts of this series. But things are a bit different at the (Silverlight) client side.

 

To start with, I added the Bing Maps Silverlight Control. After having added references to the

Microsoft.Maps.MapControl and Microsoft.Maps.MapControl.Common assemblies I added a namespace to the Home View XAML which had been created by the Silverlight Business Application wizard:

 

xmlns:bing="clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl"

I also added a Button and a MapControl to the StackPanel:

 
<StackPanel x:Name="ContentStackPanel" Style="{StaticResource ContentStackPanelStyle}" >

        <TextBlock x:Name="HeaderText" Style="{StaticResource HeaderTextStyle}"
                           Text="{Binding Path=ApplicationStrings.HomePageTitle,
                           Source={StaticResource ResourceWrapper}}" />
        <TextBlock x:Name="ContentText" Style="{StaticResource ContentTextStyle}"
                           Text="Dutch Municipalities in Silverlight" />
        <Button Content="Add Municipalities" Height="23" Name="button" Width="110"
               Click="OnClick" VerticalAlignment="Top" />
        <bing:Map Name="map1" Height="500" Width="Auto"
               CredentialsProvider="Add your API key here"/>
</StackPanel>
 

The Map Control has a property named CredentialsProvider. You have to provide a valid key value for it, otherwise the Bing Map won’t show up when running the application. A Bing Maps key can be requested after having created a Bing Maps Developer Account.

If a valid key has been provided, the Silverlight application shows up like this:

 

image

 

The code behind the button is identical to that in my last post (apart from some changes in the names):

 

private void OnClick(object sender, System.Windows.RoutedEventArgs e)
{
    MunicipalitiesDomainContext municipalitiesDomainContext = new MunicipalitiesDomainContext();

    EntityQuery<MunicipalitiesView> spatialQuery = from items in
                                    municipalitiesDomainContext.GetMunicipalitiesViewsQuery()
                                                   select items;

    municipalitiesDomainContext.Load(spatialQuery, LoadCompleted, null);
}

 

The LoadCompleted event handler, however, has changed quite a bit:

 

private void LoadCompleted(LoadOperation<MunicipalitiesView> args)
{
    foreach (MunicipalitiesView item in args.Entities)
    {
        if (IsMultiPolygon(item.Geometry))
        {
            MapMultiPolygon multiPolygon = CreateMultiPolygon(item.Geometry);
            map1.Children.Add(multiPolygon);
        }
        else
        {
            MapPolygon polygon = CreatePolygon(item.Geometry);
            map1.Children.Add(polygon);
        }
    }
}
 

All this method has to do is to extract the coordinates from the Geometry property in each MunicipalitiesView item, create Bing Maps Polygons (i.e. MapPolygon instances) from them and add them to the map.

 

However, I ran into a problem here. Bing Maps doesn’t provide built in support for MultPolygons (Polygons consisting of multiple outer rings and/or inner rings). So I couldn’t easily display Municipalities incorporating islands. Searching for a solution, I found that Ricky Brundritt had already provided one. I downloaded his Visual Studio solution and added its BingMapsSL.MultiShape project to my own Visual Studio solution without making any changes to it. Thus, I was able to construct Ricky’s MapMultiPolygon objects, which can contain multiple rings.

 

The private methods being used by the LoadCompleted event handler don’t need much explanation. For every coordinate pair (in latitude, longitude format, as they’ve already been transformed from the Dutch coordinate system on the server) a Bing Maps Location instance is created. For every ring these Location instances are added to a LocationCollection instance. One single LocationCollection defines a MapPolygon’s geometry. Ricky’s MapMultiPolygon on the other hand can contain a List of LocationCollection instances.

 

private readonly Color fillColor = Colors.Red;
private readonly Color strokeColor = Colors.Yellow;
private const int polygonStrokeThickness = 1;
private const double polygonOpacity = 0.7;
 
 
private bool IsMultiPolygon(string geometryString)
{
    return geometryString.Contains(";");
}

private MapMultiPolygon CreateMultiPolygon(string geometryString)
{
    MapMultiPolygon multiPolygon = new MapMultiPolygon();
    multiPolygon.Fill = new SolidColorBrush(fillColor);
    multiPolygon.Stroke = new SolidColorBrush(strokeColor);
    multiPolygon.StrokeThickness = polygonStrokeThickness;
    multiPolygon.Opacity = polygonOpacity;
    multiPolygon.Vertices = CreateRings(geometryString);
    return multiPolygon;
}

private MapPolygon CreatePolygon(string geometryString)
{
    MapPolygon polygon = new MapPolygon();
    polygon.Fill = new SolidColorBrush(fillColor);
    polygon.Stroke = new SolidColorBrush(strokeColor);
    polygon.StrokeThickness = polygonStrokeThickness;
    polygon.Opacity = polygonOpacity;
    polygon.Locations = CreateRing(geometryString);
    return polygon;
}
 
private IList<LocationCollection> CreateRings(string geometryString)
{
    string[] geometryStrings = geometryString.Split(new char[] { ';' });
    IList<LocationCollection> rings = new List<LocationCollection>();
    foreach (string item in geometryStrings)
    {
        LocationCollection ring = CreateRing(item);
        if (ring.Count > 2)
        {
            rings.Add(ring);
        }
    }
    return rings;
}

private LocationCollection CreateRing(string geometryString)
{
    LocationCollection ring = new LocationCollection();
    string[] coordinates = geometryString.Split(new char[] { ',' });
    for (int i = 0; i < coordinates.Length; i += 2)
    {
        Location location = new Location(double.Parse(coordinates[i + polygonStrokeThickness],
  CultureInfo.InvariantCulture), double.Parse(coordinates[i],
  CultureInfo.InvariantCulture));
        ring.Add(location);
    }
    return ring;
}
 

I ended up with an application showing the Dutch municipalities like this:

 

image

 

The application works as expected. One can zoom and pan and in my next post I’ll show that it is easy to add a tooltip to the geometries, providing the user with some basic information about them. So we have a basic Mapping application now.

 

But a few questions still remain.

 

In the first place we can observe that adding this amount of geometries (441 to be precise) makes the map sluggish. Why is that? Can we solve this?

In the second place we need to ask ourselves how accurate the Bing Maps stuff is. How reliable are the locations shown and how accurate are our custom geometries displayed? I’ll try to come up with some answers in later posts.

SqlServerAndBingInSilverlight.zip (1,78 mb)

Spatial Web Services in Silverlight, Part 2

by Willem 21. July 2010 19:58

As I showed in my first post about Spatial Web Services in Silverlight, it isn’t very complicated to serve up SQL Server geometries via RIA Web Services. Neither is it very difficult to serve them in a form that can be consumed in a Silverlight application. Especially if we use the Entity Framework.

 

It is unfortunate that this Framework can’t cope with User Defined CLR Types (not even if they’re designed and implemented by Microsoft .NET teams!). In my opinion the Entity Framework team should do something about that as soon as possible. It would make the SQL Server Spatial types a lot more useable.

 

In my last post I got as far as serving up the SQL Server Geometries contained in the Malta Polygon table via a RIA Web Service and showing them (as strings) in a Silverlight DataGrid. But of course I would like to to have them show up as Silverlight geometries on a Canvas.

 

In order to achieve this, I changed the Home.xaml View to include a Canvas instead of a DataGrid. I ended up with the following code:

 

<navigation:Page
  x:Class="SqlServerSpatialTypesInSilverlight.Home"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  xmlns:d=http://schemas.microsoft.com/expression/blend/2008 
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
  xmlns:navigation="clr-namespace:System.Windows.Controls;
                   assembly=System.Windows.Controls.Navigation"
  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"
  mc:Ignorable="d" d:DesignWidth="640" d:DesignHeight="480" 
  Style="{StaticResource PageStyle}">

  <Grid x:Name="LayoutRoot">
    <ScrollViewer x:Name="PageScrollViewer" Style="{StaticResource PageScrollViewerStyle}" >
      <StackPanel x:Name="ContentStackPanel" Style="{StaticResource ContentStackPanelStyle}">
        <TextBlock x:Name="HeaderText" Style="{StaticResource HeaderTextStyle}"
                           Text="{Binding Path=ApplicationStrings.HomePageTitle,
                           Source={StaticResource ResourceWrapper}}"/>
        <TextBlock x:Name="ContentText" Style="{StaticResource ContentTextStyle}"
                           Text="Home page content"/>
          <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>
                <Button Content="Load" Height="23" Name="loadButton"
  Width="75" Click="loadButton_Click" />
                <Canvas Height="369" Name="mapCanvas" Width="517" />
            </StackPanel>

    </ScrollViewer>
  </Grid>
</navigation:Page>

 

Apart from a DomainDataSource, which I already had in the earlier version that went with my last post, the XAML contains a Button and a Canvas. A click on the Button is meant to trigger a call to the RIA WebService in order to fetch a collection of Geometry strings, and subsequently the creation and addition to the Canvas of a collection of Silverlight Geometries.

 

It turned out to be a relatively easy task. I added the following code to the Click event of the Button:

 

private void loadButton_Click(object sender, System.Windows.RoutedEventArgs e)
{
    SpatialDomainContext spatialDomainContext = new SpatialDomainContext();
    EntityQuery<MaltaPolygonsView> spatialQuery =
         from items in spatialDomainContext.GetMaltaPolygonsViewsQuery()
         select items;
    spatialDomainContext.Load(spatialQuery, LoadCompleted, null);
}

 

This code took care of selecting all the Malta Polygons in the SQL Server database and dragging them over to the Silverlight application via the WebService. The LoadCompleted() method, in which I receive the results, looks like this:

 

private void LoadCompleted(LoadOperation<MaltaPolygonsView> args)
{
    CreatePaths(args);
    Rect originExtent = GetExtent(paths);
    Rect destinationExtent = new Rect(0, 0, mapCanvas.Width, mapCanvas.Height);
    TransformGroup transformGroup = CalculateResizeTransform(originExtent, destinationExtent);
    double scaleFactor = CalculateScaleFactor(originExtent, destinationExtent);
    foreach (Path path in paths)
    {
       path.StrokeThickness = 1 / scaleFactor;
       path.RenderTransform = transformGroup;
       mapCanvas.Children.Add(path);
    }
}
 

This method creates System.Windows.Shapes.Path items out of the geometry strings received, assigns a Transformation to them, and finally adds them to the Canvas. It has a few private helper methods which do most of the real work. Roughly half of these methods have been borrowed from my earlier WPF project:

 

private void CreatePaths(LoadOperation<MaltaPolygonsView> args)
{
    paths = new List<Path>();
    string pathNamespace =
       "<Path xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" Data=\"";
    foreach (MaltaPolygonsView item in args.Entities)
    {
        Path path = null;
        try
        {
            path = (Path)XamlReader.Load(String.Format("{0}{1}\"/>",
  pathNamespace, item.Geometry));
        }
        catch
        {
        }
        if (path != null)
        {
            path.Stroke = new SolidColorBrush{ Color = Colors.Blue };
            path.Fill = new SolidColorBrush { Color = Colors.Red };
            paths.Add(path);
        }
    }
}

private Rect GetExtent(List<Path> paths)
{
    Rect extent = Rect.Empty;
    foreach (Path path in paths)
    {
        extent.Union(path.Data.Bounds);
    }
    return extent;
}

private TransformGroup CalculateResizeTransform(Rect originExtent, Rect destinationExtent)
{
    double scaleFactor = CalculateScaleFactor(originExtent, destinationExtent);

    ScaleTransform scaleTransform = new ScaleTransform
  { ScaleX = scaleFactor, ScaleY = -scaleFactor };

    TranslateTransform translateTransform = new TranslateTransform
    {
        X = (destinationExtent.Width - (originExtent.Left + originExtent.Right)
              * scaleFactor) / 2,
        Y = (destinationExtent.Height + (originExtent.Bottom + originExtent.Top)
  * scaleFactor) / 2
    };

    TransformGroup transformGroup = new TransformGroup();
    transformGroup.Children.Add(scaleTransform);
    transformGroup.Children.Add(translateTransform);
    return transformGroup;
}

private double CalculateScaleFactor(Rect originExtent, Rect destinationExtent)
{
    double originAspectRatio = originExtent.Width / originExtent.Height;
    double destinationAspectRatio = destinationExtent.Width / destinationExtent.Height;
    double scaleFactor = 1.0;
    if (originAspectRatio < destinationAspectRatio)
    {
        scaleFactor = destinationExtent.Height / originExtent.Height;
    }
    else
    {
        scaleFactor = destinationExtent.Width / originExtent.Width;
    }
    return scaleFactor;
}

The only other thing I needed to change in the code I posted earlier was in the partial class which I had added to the generated MaltaPolygonsView class. In my last post I mentioned that I had to do a Replace() on the extension method ToWpfGeometry() because the Path Data string at some places contained semicolons instead of whitespaces. This turned out to be the umpteenth time I fell into the localization pitfall: changing the line

 

return geometry.ToString().Replace(";", " ");

 

to

 

return geometry.ToString(CultureInfo.InvariantCulture);

 

resulted in a lot more valid Geometry strings. The CurrentCulture on my computer was Dutch (Netherlands). Forcing the Culture to InvariantCulture suddenly produced lots of valid Geometry strings. The Replace() method didn’t make all the Geometry Strings valid for the XamlReader which I’m using in the CreatePaths() method, whereas using an InvariantCulture in the ToString() method does the trick for all but one of the Geometry Strings (I haven’t yet checked why this one instance remains invalid).

 

While I was at it, I also changed the method name ToWpfGeometry() to ToSilverlightGeometry().

 

I now have a very basic Silverlight application showing up the SQL Server spatial geometries as Silverlight geometries on a Canvas. It isn’t a very useful application yet, as it cannot pan, zoom, or perform other basic stuff one expects from a serious GIS application, but at least it shows how to get Spatial data from a database, convert them to native Silverlight types and display them in a Silverlight application.


SqlServerSpatialTypesInSilverlight.zip (1,19 mb)

Spatial Web Services in Silverlight

by Willem 8. July 2010 09:44

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:

 

image

 

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:

 

image

 

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:

 

image

 

My SpatialDatabase connection appeared automatically. The wizard obviously inferred it from the Data Connections in the Visual Studio Server Explorer:

 

image

 

I just clicked Next once again. I then answered Yes to the following question:

 

image

 

On the question which database objects I wanted to include, I answered like this:

 

image

 

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:

 

image

 

This worked. I ended up with the following basic model:

 

image

 

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:

 

image

 

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:

 

image

 

I named it SpatialDomainService. In the next screen I checked MaltaPolygonsView for inclusion and checked Enable editing as well.

 

image

 

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:

 

image

 

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.

 

 

About the author

My name is Willem Ligtendag (You can also call me Wim). I'm a software developer living in the Netherlands. I've designed and implemented various types of applications, but I've specialised in GIS applications for quite some years now. Apart from developing applications, I like trying to get my head around new stuff and teaching that stuff to other people. I also enjoy attacking things like difficult bugs and performance issues. Together with Joost Scholten and Ynze Baumfalk I've recently started a GIS Consultancy Company: De GISFabriek.

Tag cloud

    Page List