.NET Rocks!
Road Trip Tracking

Fig 0 - Tracking a RoadTrip


Overview

Microsoft is on the move this fall. Win8 is the big news, but Visual Studio 2012, .Net 4.5, a revamped Azure, WP8, Office 2013, and even a first foray into consumer hardware, Surface Tablets (not tables), all see daylight this fall.

The Net Rocks duo, Carl Franklin and Richard Campbell, are also on the move. Carl and Richard head out this week for a whirl wind tour through 36 states in 72 days or roughly 1728 hours. The DNR Road Trip Tracking application, http://www.web-maps.com/RoadTrip2012/, keeps tabs on the .Net Rocks RV with Tweet encouragement for the road weary travelers. All are welcome to follow the DNR RV online and add Tweet comments at #dnrRoadTrip. The app gives event information and up to the minute updates of time to next show with tweets along the route. It even gives turn by turn directions for those inclined to catch the .Net Rocks RV and follow along in the real world – .NET Rocks stalking.

Technical Overview

Project Outline

Fig 1 – .Net Rocks Road Trip Tracking app project outline

Backend:

SQL Server Azure is the key resource for the DNR tracking app. GPS feeds are pushed at 1 minute intervals from a commercial Airlink GPS unit to a Windows service listening for UDP packets. This Feed Service turns incoming UDP packets into Feed records stored in SQL Azure with a geography point location and datetime stamp.

On the same system, a Twitter Query service is checking for Tweets on a 30 second interval using the Twitter REST API. Tweets are also turned into Feed records in SQL Azure. However, the geography point locations for Tweets are pulled from the latest GPS record so they are associated in time with the location of the GPS unit in the DNR RV.

Frontend:

Fig2 – Windows 8 IE10 browser showing stop and routes

On the front end, an Azure WebRole provides the UI and WCF service for communicating with the SQL Azure Feed data. In order to handle the widest spectrum of devices, this UI leverages jQuery Mobile sitting on top of HTML5. jQuery Mobile Supported Platforms

Inside the app divs (jQuery Mobile <div data-role=”page”..> ) are maps leveraging Bing Maps Ajax v7 API. The UI client also accesses Bing Geocode and Bing Route services through a proxy.

Fig 3 – IE9 on a laptop with GPS dots and Tweet icons along the way

Some more details:

Routes

Since there are several thousand points for each ‘event to event’ route, these are stored in SQL Azure as geography LineStrings. Using SQL Server spatial functions, the routes can be simplified on query for improved performance in both network latency and map rendering. SQL Azure’s geography Reduce(factor) function is a thinning algorithm that reduces the number of vertices of geography features while maintaining shape integrity. In this app the reduce factor is tied to zoomlevels of the map, thinning the number of points returned to the UI.

The map viewport is used as a bounding box STIntersect so only the visible routes are returned. Obviously Carl and Richard may find reasons to take a different route so the GPS breadcrumbs may wander from the Bing generated routes.

Tweets

Fig 4 – WebMatrix iPad simulator

The Twitter REST API queries are simple search by hashtag queries:
http://search.twitter.com/search.json?q=%23dnrroadtrip

To avoid returning lots of duplicate Tweets the search is limited by the last since_id in the Feed table. There are some caveats to REST Twitter searches:
“Search is focused on relevance and not completeness. This means that some
Tweets and users may be missing from search results

Fig 5 - webmatrix WP7 emulator

Fig 6 - iPhone simulator

GPS points

GPS points are generated every 60 seconds while the RV GPS unit is powered on. When the vehicle is off, and power is scarce, the unit still sends a packet once every 4 hours. Carl and Richard will be driving a lot of hours and there will be lots of points generated over the next 72 days and roughly 1728 hours. Assuming a 25% driving time over the duration, there could be as many as 1728/4 *60 = 25,920 GPS locations. Even Bing Maps Ajax v7 will choke trying to render this many locations.

In order to keep things more reasonable, there is another thinning algorithm used in the GPS query service. This is again tied to zoomlevel . At lower zoom levels points are thinned using a type of decimation – every 20th, 10th, 5th point, etc is kept depending on the zoomlevel. In addition only points required by the viewport bounding box are returned. Once the map is zoomed to higher resolution (zoom > 11) all of the points will be returned.

GPS map locations include a rollover infobox with time and detected speed at the location. We can all check up on Carl’s driving (moving: 86.99mph) and keep track of coffee stops (moving: 0.0 mph).

Bing Routes

Routing services are provided for user position to the latest GPS location and Stop venues selected on the map or from the Stop list. In addition to the route map a turn by turn directions list is provided as a page option. The GeoLocation API is used for identifying a user’s location for these routing requests. Geolocation API is an opt in API, so users need to allow location sharing to have their location automatically available. If allowed, getCurrentPosition returns a latitude, longitude which is run through the Bing reverse geocoding service to get an address used as the ‘from’ field for routing requests.

Fig 7 - Stop Detail with Maps.Themes.BingTheme()

Fig 8 - Bing Route Denver to Seattle Stop

Fig 9 - Bing Turn by Turn directions

jQuery Mobile

jQuery Mobile is a javascript library for abstracting away some of the complexity of supporting a large number of devices. WP7, Win8 tablets, iPads, iPhones, and Android devices are multiplying while traditional laptop and desktop systems have a number of browser choices and versions. jQuery Mobile is not perfect but it is a great help in a project that had to be ready in about ten days from start to finish.

One interesting feature of jQuery Mobile is the page transition effect. These are based on CSS3 and are not supported by all browsers. It adds a little pizazz to see slide, flip, and pop effects for page transitions.

JQuery Mobile apps do not have access to device native sensors such as accelerometer, compass, gyrometer, etc , so jQuery Mobile webapps will not have the full range of capabilities found in custom apps for Win8, WP7, iOS, and Android. However, just one web UI for all is an enticing benefit, while deployment is ordinary webapp rather than a series of more complex app store submissions. This approach allows easy updates over the course of the tour.

Fig 10 – Microsoft Way on an Android AVD emulator

Heatmaps
Collecting some locations always leads to the possibility of heatmaps. These are value gradients which are helpful for analyzing geospatial data.

Fig 11 – Tweet heatmap along tour route from Seattle to Wyoming

Maybe it’s pretty obvious where Tweets are concentrated, but how about locations of app users who share their location. Australia is hot, India is not. Guess who listens to .NetRocks! Or, at least who’s less cautious about sharing location with GeoLocation API. User heatmaps bring to mind some intriguing possibilities for monitoring use, markets, and promotion effectiveness.

Fig 12 - GeoLocation users world wide

Summary

On the Road Again

Map Clipping with Silverlight



Fig 1 – Clip Map Demo

Bing Maps Silverlight Control has a lot of power, power that is a lot of fun to play with even when not especially practical. This weekend I was presented with a challenge to find a way to show web maps, but restricted to a subset of states, a sub-region. I think the person making the request had more in mind the ability to cut out an arbitrary region and print it for reporting. However, I began to think why be restricted to just the one level of the pyramid. With all of this map data available we should be able to provide something as primitive as coupon clipping, but with a dynamic twist.

Silverlight affords a Clip masking mechanism and it should be simple to use.

1. Region boundary:

The first need is to compile the arbitrary regional boundary. This would be a challenge to code from scratch, but SQL Server spatial already has a function called “STUnion.” PostGIS has had an even more powerful Union function for some time, and Paul Ramsey has pointed out the power of fast cascaded unions. Since I’m interested in seeing how I can use SQL Serrver, though, I reverted to the first pass SQL Server approach. But, as I was looking at STUnion it was quickly apparent that this is a simple geo1.STUnion(geo2) function and what is needed is an aggregate union. The goal is to union more than just two geography elements at a time, preferably the result of an arbitrary query.

Fortunately there is a codeplex project, SQL Server Tools, which includes the very thing needed, along with some other interesting functions. GeographyAggregateUnion is the function I need, Project/Unproject and AffineTransform:: will have to await another day. This spatial tool kit consists of a dll and a register.sql script that is used to import the functions to an existing DB. Once in place the functions can be used like this:

SELECT dbo.GeographyUnionAggregate(wkb_geometry)) as Boundary
FROM [Census2009].[dbo].[states]
WHERE NAME = ‘Colorado’ OR NAME = ‘Utah’ or NAME = ‘Wyoming’

Ignoring my confusing choice of geography column name, “wkb_geometry,” this function takes a “geography” column result and provides the spatial union:

Or in my case:


Fig 3 – GeographyUnionAggregate result in SQL Server

Noting that CO, WY, and UT are fairly simple polygons but the result is 1092 nodes I tacked on a .Reduce() function.
dbo.GeographyUnionAggregate(wkb_geometry).Reduce(10) provides 538 points
dbo.GeographyUnionAggregate(wkb_geometry).Reduce(100) provides 94 points
dbo.GeographyUnionAggregate(wkb_geometry).Reduce(100) provides 19 points

Since I don’t need much resolution I went with the 19 points resulting from applying the Douglas-Peuker thinning with a tolerance factor of 1000.

2. Adding the boundary

The next step is adding this union boundary outlining my three states to my Silverlight Control. In Silverlight there are many ways to accomplish this, but by far the easiest is to leverage the builtin MapPolygon control and add it to a MapLayer inside the Map hierarchy:

<m:MapLayer>
  <m:MapPolygon x:Name=”region”
  Stroke=”Blue” StrokeThickness=”5″
    Locations=”37.0003960382868,-114.05060006067 37.000669,-112.540368
    36.997997,-110.47019 36.998906,-108.954404
         .
        .
        .
    41.996568,-112.173352 41.99372,-114.041723
     37.0003960382868,-114.05060006067 “/>
</m:MapLayer>


Now I have a map with a regional boundary for the combined states, CO, WY, and UT.

3. The third step is to do some clipping with the boundary:

UIElement.Clip is available for every UIElement, however, it is restricted to Geometry clipping elements. Since MapPolygon is not a geometry it must be converted to a geometry to be used as a clip element. Furthermore PathGeometry is very different from something as straight forward as MapPolygon, whose shape is defined by a simple LocationCollection of points.

PathGeometry in XAML:


<Canvas.Clip>
  <PathGeometry>
    <PathFigureCollection>
      <PathFigure StartPoint=”1,1″>
        <PathSegmentCollection>
          <LineSegment Point=”1,2″/>
          <LineSegment Point=”2,2″/>
          <LineSegment Point=”2,1″/>
          <LineSegment Point=”1,1″/>
        </PathSegmentCollection>
      </PathFigure>
    </PathFigureCollection>
  </PathGeometry>
</Canvas.Clip>


The easiest thing then is to take the region MapPolygon boundary and generate the necessary Clip PathGeometry in code behind:

  private void DrawClipFigure()
  {
    if (!(MainMap.Clip == null))
    {
     &nbspMainMap.ClearValue(Map.ClipProperty);
    }
    PathFigure clipPathFigure = new PathFigure();
    LocationCollection locs = region.Locations;
    PathSegmentCollection clipPathSegmentCollection = new PathSegmentCollection();
    bool start = true;
    foreach (Location loc in locs)
    {
      Point p = MainMap.LocationToViewportPoint(loc);
      if (start)
      {
       clipPathFigure.StartPoint = p;
       start = false;
     }
     else
     {
      LineSegment clipLineSegment = new LineSegment();
      clipLineSegment.Point = p;
      clipPathSegmentCollection.Add(clipLineSegment);
     }
    }
    clipPathFigure.Segments = clipPathSegmentCollection;
    PathFigureCollection clipPathFigureCollection = new PathFigureCollection();
    clipPathFigureCollection.Add(clipPathFigure);

    PathGeometry clipPathGeometry = new PathGeometry();
    clipPathGeometry.Figures = clipPathFigureCollection;
    MainMap.Clip = clipPathGeometry;
  }

This Clip PathGeometry can be applied to the m:Map named MainMap to mask the underlying Map. This is easily done with a Button Click event. But when navigating with pan and zoom, the clip PathGeometry is not automatically updated. It can be redrawn with each ViewChangeEnd:
private void MainMap_ViewChangeEnd(object sender, MapEventArgs e)
{
  if (MainMap != null)
  {
    if ((bool)ShowBoundary.IsChecked) DrawBoundary();
    if ((bool)ClipBoundary.IsChecked) DrawClipFigure();
  }
}


This will change the clip to match a new position, but only after the fact. The better way is to add the redraw clip to the ViewChangeOnFrame:

MainMap.ViewChangeOnFrame += new EventHandler<MapEventArgs>(MainMap_ViewChangeOnFrame);

private void MainMap_ViewChangeOnFrame(object sender, MapEventArgs e)
{
  if (MainMap != null)
  {
    if ((bool)ShowBoundary.IsChecked) DrawBoundary();
    if ((bool)ClipBoundary.IsChecked) DrawClipFigure();
  }
}


In spite of the constant clip redraw with each frame of the navigation animation, navigation is smooth and not appreciably degraded.

Summary:

Clipping a map is not terrifically useful, but it is supported with Silverlight Control and provides another tool in the webapp mapping arsenal. What is very useful, are the additional functions found in SQL Server Tools. Since SQL Server spatial is in the very first stage of its life, several useful functions are not found natively in this release. It is nice to have a superset of tools like GeographyAggregateUnion, Project/Unproject,
and AffineTransform::.

The more generalized approach would be to allow a user to click on the states he wishes to include in a region, and then have a SQL Server query produce the boundary for the clip action from the resulting state set. This wouldn’t be a difficult extension. If anyone thinks it would be useful, pass me an email and I’ll try a click select option.



Fig 4 – Clip Map Demo

Hauling Out the Big RAM

Amazon released a handful of new stuff.

“Make that a Quadruple Extra Large with room for a Planet OSM”

Big Mmeory
Fig 1 – Big Foot Memory

1. New Price for EC2 instances

US EU
Linux Windows SQL Linux Windows SQL
m1.small $0.085 $0.12 $0.095 $0.13
m1.large $0.34 $0.48 $1.08 $0.38 $0.52 $1.12
m1.xlarge $0.68 $0.96 $1.56 $0.76 $1.04 $1.64
c1.medium $0.17 $0.29 $0.19 $0.31
c1.xlarge $0.68 $1.16 $2.36 $0.76 $1.24 $2.44

Notice the small instance, now $0.12/hr, matches Azure Pricing

Compute = $0.12 / hour

This is not really apples to apples since Amazon is a virtual instance, while Azure is per deployed application. A virtual instance can have multple service/web apps deployed.

2. Amazon announces a Relational Database Service RDS
Based on MySQL 5.1, this doesn’t appear to add a whole lot since you always could start an instance with any database you wanted. MySQL isn’t exactly known for geospatial even though it has some spatial capabilities. You can see a small comparison of PostGIS vs MySQL by Paul Ramsey. I don’t know if this comparison is still valid, but I haven’t seen much use of MySQL for spatial backends.

This is similar to Azure SQL Server which is also a convenience deployment that lets you run SQL Server as an Azure service, without all the headaches of administration and maintenance tasks. Neither of these options are cloud scaled, meaning that they are still single instance versions, not cross partition capable. SQL Azure Server CTP has an upper limit of 10Gb, as in hard drive not RAM.

3. Amazon adds New high memory instances

  • High-Memory Double Extra Large Instance 34.2 GB of memory, 13 EC2 Compute Units (4 virtual cores with 3.25 EC2 Compute Units each), 850 GB of instance storage, 64-bit platform $1.20-$1.44/hr
  • High-Memory Quadruple Extra Large Instance 68.4 GB of memory, 26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute Units each), 1690 GB of instance storage, 64-bit platform $2.40-$2.88/hr

These are new virtual instance AMIs that scale up as opposed to scale out. Scaled out options use clusters of instances in the Grid Computing/Hadoop type of architectures. There is nothing to prohibit using clusters of scaled up instances in a hybridized architecture, other than cost. However, the premise of Hadoop arrays is “divide and conquer,” so it makes less sense to have massive nodes in the array. Since scaling out involves moving the problem to a whole new parallel programming paradigm with all of its consequent complexity, it also means owning the code. In contrast scaling up is generally very simple. You don’t have to own the code or even recompile just install on more capable hardware.

Returning us back to the Amazon RDS, Amazon has presumably taken an optimized compiled route and offers prepackaged MySQL 5.1 instances ready to use:

  • db.m1.small (1.7 GB of RAM, $0.11 per hour).
  • db.m1.large (7.5 GB of RAM, $0.44 per hour)
  • db.m1.xlarge (15 GB of RAM, $0.88 per hour).
  • db.m2.2xlarge (34 GB of RAM, $1.55 per hour).
  • db.m2.4xlarge (68 GB of RAM, $3.10 per hour).

Of course the higher spatial functionality of PostgreSQL/PostGIS can be installed on any of these high memory instances as well. It is just not done by Amazon. The important thing to note is memory approaches 100Gb per instance! What does one do with all that memory?

Here is one use:

“Google query results are now served in under an astonishingly fast 200ms, down from 1000ms in the olden days. The vast majority of this great performance improvement is due to holding indexes completely in memory. Thousands of machines process each query in order to make search results appear nearly instantaneously.”
Google Fellow Jeff Dean keynote speech at WSDM 2009.

Having very large memory footprints makes sense for increasing performance on a DB application. Even fairly large data tables can reside entirely in memory for optimum performance. Whether a database makes use of the best optimized compiler for Amazon’s 64bit instances would need to be explored. Open source options like PostgreSQL/PostGIS would let you play with compiling in your choice of compilers, but perhaps not successfully.

Todd Hoff has some insightful analysis in his post, “Are Cloud-Based Memory Architectures the Next Big Thing?”

Here is Todd Hoff’s point about having your DB run inside of RAM – remember that 68Gb Quadruple Extra Large memory:

“Why are Memory Based Architectures so attractive? Compared to disk, RAM is a high bandwidth and low latency storage medium. Depending on who you ask the bandwidth of RAM is 5 GB/s. The bandwidth of disk is about 100 MB/s. RAM bandwidth is many hundreds of times faster. RAM wins. Modern hard drives have latencies under 13 milliseconds. When many applications are queued for disk reads latencies can easily be in the many second range. Memory latency is in the 5 nanosecond range. Memory latency is 2,000 times faster. RAM wins again.”

Wow! Can that be right? “Memory latency is 2,000 times faster .”

(Hmm… 13 milliseconds = 13,000,000 nanoseconds
so 13,000,000n/5n = 2,600,000x? And 5Gb/s / 100Mb/s = 50x? Am I doing the math right?)

The real question, of course, is what will actual benchmarks reveal? Presumably optimized memory caching narrows the gap between disk storage and RAM. Which brings up the problem of configuring a Database to use large RAM pools. PostgreSQL has a variety of configuration settings but to date RDBMS software doesn’t really have a configuration switch that simply caches the whole enchilada.

Here is some discussion of MySQL front-ending the database with In-Memory-Data-Grid (IMDG).

Here is an article on a PostgreSQL configuration to use a RAM disk.

Here is a walk through on configuring PostgreSQL caching and some PostgreSQL doc pages.

Tuning for large memory is not exactly straightforward. There is no “one size fits all.” You can quickly get into Managing Kernel Resources. The two most important parameters are:

  • shared_buffers
  • sort_mem
“As a start for tuning, use 25% of RAM for cache size, and 2-4% for sort size. Increase if no swapping, and decrease to prevent swapping. Of course, if the frequently accessed tables already fit in the cache, continuing to increase the cache size no longer dramatically improves performance.”

OK, given this rough guideline on a Quadruple Extra Large Instance 68Gb:

  • shared_buffers = 17Gb (25%)
  • sort_mem = 2.72Gb (4%)

This still leaves plenty of room, 48.28Gb, to avoid dreaded swap pagein by the OS. Let’s assume a more normal 8Gb memory for the OS. We still have 40Gb to play with. Looking at sort types in detail may make adding some more sort_mem helpful, maybe bump to 5Gb. Now there is still an additional 38Gb to drop into shared_buffers for a grand total of 55Gb. Of course you have to have a pretty hefty set of spatial tables to use up this kind of space.

Here is a list of PostgreSQL limitations. As you can see it is technically possible to run out of even 68Gb.


Limit

Value
Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns per Table 250 – 1600 depending on column types
Maximum Indexes per Table Unlimited

Naturally the Obe duo has a useful posting on determining PostGIS sizes: Determining size of database, schema, tables, and geometry

To get some perspective on size an Open Street Map dump of the whole world fits into a 90Gb EBS Amazon Public Data Set configured for PostGIS with pg_createcluster. Looks like this just happened a couple weeks ago. Although 90Gb is just a little out of reach for a for even a Quadruple Extra Large, I gather the current size of planet osm is still in the 60Gb range and you might just fit it into 55Gb RAM. It would be a tad tight. Well maybe the Octuple Extra Large Instance 136Gb instance is not too far off. Of course who knows how big Planet OSM will ultimately end up being.
See planet.openstreetmap.org

Another point to notice is the 8 virtual cores in a Quadruple Extra Large Instance. Unfortunately

“PostgreSQL uses a multi-process model, meaning each database connection has its own Unix process. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. PostgreSQL does not use multi-threading to allow a single process to use multiple CPUs.”

Running a single connection query apparently won’t benefit from a multi cpu virtual system, even though running multi threaded will definitely help with multiple connection pools.

I look forward to someone actually running benchmarks since that would be the genuine reality check.

Summary

Scaling up is the least complex way to boost performance on a lagging application. The Cloud offers lots of choices suitable to a range of budgets and problems. If you want to optimize personnel and adopt a decoupled SOA architecture, you’ll want to look at Azure + SQL Azure. If you want the adventure of large scale research problems, you’ll want to look at instance arrays and Hadoop clusters available in Amazon AWS.

However, if you just want a quick fix, maybe not 2000x but at least a some x, better take a look at Big RAM. If you do, please let us know the benchmarks!

Silverlight MapControl and PostGIS


PostGIS Silverlight MapControl
Fig 1 – PostGIS and Silverlight MapControl

It is worth revisiting connecting Silverlight MapControl to a database in order to try duplicating the SQL Server layers in PostgreSQL/PostGIS.

Why?
SQL Server 2008 is still lacking some of the spatial capabilities found in PostGIS and also a rough performance comparison between the two databases would be interesting.

The same process is used in accessing a database from inside the Silverlight MapController for either SQL Server or PostGIS. A Data service is used with an [OperationContract] to expose the results of a DB query to the Silverlight through a Service Reference on the Silverlight side. From there it is a matter of using Silverlight MapController events to send a request to the service and process the results into shapes on the map.

The main difference is the code to handle the PostGIS connection, command query, and result reader. Microsoft includes these for SQL Server in the System.Data.SqlClient namespace as part of a default project setup. Naturally PostGIS doesn’t happen to be in the Microsoft System.Data.dll, however, there is a nice project that provides access to PostgreSQL inside .NET: Npgsql – .Net Data Provider for Postgresql

I downloaded Npgsql version 2.04 binaries and referenced it into my previous GeoTest. Now I can add a new [OperationContract] to the existing SFBayDataService that uses an Npgsql connection to PostgreSQL. Since it would be interesting to compare SQL Server and PostgreSQL/PostGIS, my new OperationContract includes the ability to switch from one to the other.
   [OperationContract]
  public List<string> GetGeom(string dbtype, string table, string bbox, string layer)

dbtype can be ‘PostGIS’ or ‘SQL Server’. I also refactored to allow selection of table and layer as well as a single string bbox.

Morten Nielsen has a blog on using npgsql here: SharpMap Shp2pgsql He used npgsql to create a nice little import tool to load shp format data into PostGIS with a GUI similar to the SQL Server shp loader. I used it on the SF Bay test data after modifying by adding a necessary System.Int64 type to the Type2PgType list and also to the Type2NpgsqlType. It then worked fine for loading my three simple test tables.

I can use npgsql to open a connection and add a query command for a PostGIS database:
   NpgsqlConnection conn = new NpgsqlConnection(“Server=127.0.0.1;Port=5432;User Id=your id;Password=your password;Database=TestShp;”);
   conn.Open();
   NpgsqlCommand command = new NpgsqlCommand(“Select *, asText(the_geom) as geom from \”" + table + “\” WHERE the_geom && SetSRID(‘BOX3D(” + bbox + “)’::box3d,4326);”, conn);

Using the npgsql reader lets me process through the query results one record at a time:
  NpgsqlDataReader rdr = command.ExecuteReader();
  while (rdr.Read()) {…}

which leads to a decision. SQL Server lets me grab a geom field directly into a geography data type:
   SqlGeography geo = (SqlGeography)rdr["geom"];
but npgsql has no equivalent convenience. Rather than work out the WKB stream I took the easy way out and converted geom on the PostGIS query like this: asText(the_geom) as geom
Now I can take the text version of the geometry and put it back together as a result string for my Silverlight MapControl svc_GetGeomCompleted.

[OperationContract]
public List GetGeom(string dbtype, string table, string bbox, string layer)
{
    List records = new List();

    if (dbtype.Equals("SQL Server"))
    {
        SqlServer ss = new SqlServer(layer, table, bbox);
        records = ss.Query();
     }
    else if (dbtype.Equals("PostGIS"))
    {
        PostGIS pg = new PostGIS(layer, table, bbox);
        records = pg.Query();
    }
    return records;
}

Listing 1 – Data Service Operation Contract

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;
using System.Text.RegularExpressions;
using System.Text;
using System.Configuration;

namespace GeoTest.Web
{
 public class PostGIS
 {
  private string _layer;
  private string _bbox;
  private string _table;

  public PostGIS(string layer, string table,string bbox )
  {
   this._layer = layer;
   this._table = table;
   this._bbox = bbox;
  }

  public List Query()
  {
    StringBuilder sb;
    List records = new List();
    string[] fields;
    string geomType;
    string connStr = ConfigurationManager.AppSettings["PGconnecttionStr"];
    NpgsqlConnection conn = new NpgsqlConnection(connStr);
    conn.Open();
    NpgsqlCommand command = new NpgsqlCommand("Select *, asText(the_geom) as geom from \"" +
 _table + "\" WHERE the_geom && SetSRID('BOX3D(" + _bbox + ")'::box3d,4326);", conn);
    try
    {
     NpgsqlDataReader rdr = command.ExecuteReader();
     while (rdr.Read())
     {
      sb = new StringBuilder(_layer + ";");
      for (int i = 0; i < rdr.FieldCount - 2; i++)
      {
       sb.Append(rdr[i].ToString() + ";");
      }
      fields = Regex.Split(rdr["geom"].ToString(), @"\(+(.*?)\)+");
      geomType = fields[0];
      switch (geomType) {
       case "POINT":
        {
         sb.Append((fields[1].Split(' '))[1]);//latitude
         sb.Append(" " + (fields[1].Split(' '))[0]);//longitude
         break;
        }
       case "LINESTRING":
       case "MULTILINESTRING":
        {
         string[] pts = fields[1].Split(',');
         for (int i = 0; i < pts.Length; i++)
         {
          if (i > 0) sb.Append(",");
          sb.Append((pts[i].Split(' '))[1]);//latitude
          sb.Append(" " + (pts[i].Split(' '))[0]);//longitude
         }
         break;
        }
      }

      records.Add(sb.ToString());
     }
     rdr.Close();
    }
    catch (Exception e)
    {
     records.Add(e.Message);
    }
    conn.Close();
    return records;
   }
 }
}

Listing 2 – PostGIS class with Query Result as Text

This works after a manner, as long as MultiLineStrings are really just simple LineStrings along with other simplifications, but it would be much better to have a generalized OGC Simple Feature reader as well as a more efficient WKB reader. I am too lazy to work out all of that, so it is better to look around for a solution. In java the wkbj4 project is useful, but I didn’t find an equivalent wkb4c#. The npgsqltypes list features like point, path, polygon, but these are PostgreSQL types not the more useful OGC Simple Features found in PostGIS geometries. However, this port of JTS, Java Topology Suite, to C# is useful: Net Topology Suite

Code Geometry Type Coordinates
0 GEOMETRY X,Y
1 POINT X,Y
2 LINESTRING X,Y
3 POLYGON X,Y
4 MULTIPOINTe X,Y
5 MULTILINESTRING X,Y
6 MULTIPOLYGON X,Y
7 GEOMCOLLECTION X,Y

Table 1: OGC Simple Feature Geometry type codes (not all features)

Modifying the PostGIS class to take advantage of Net Topology Suite’s WKB and WKT readers results in this improved version:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using GeoAPI.Geometries;
using GisSharpBlog.NetTopologySuite.IO;
using Npgsql;

namespace GeoTest.Web
{
  public class PostGIS
  {
    private string _layer;
    private string _bbox;
    private string _table;

    public PostGIS(string layer, string table, string bbox)
    {
      this._layer = layer;
      this._table = table;
      this._bbox = bbox;
    }

    public List Query(bool wkb)
    {
      StringBuilder sb;
      List records = new List();
      string connStr = ConfigurationManager.AppSettings["PGconnecttionStr"];
      NpgsqlConnection conn = new NpgsqlConnection(connStr);
      conn.Open();
      NpgsqlCommand command;

      if (wkb) command = new NpgsqlCommand("Select *, AsBinary(the_geom) as geom from \"" +
_table + "\" WHERE the_geom && SetSRID('BOX3D(" + _bbox + ")'::box3d,4326);", conn);
      else command = new NpgsqlCommand("Select *, AsText(the_geom) as geom from \"" +
_table + "\" WHERE the_geom && SetSRID('BOX3D(" + _bbox + ")'::box3d,4326);", conn);
      try
      {
        NpgsqlDataReader rdr = command.ExecuteReader();
        while (rdr.Read())
        {
          IGeometry g;
          sb = new StringBuilder(_layer + ";");
          //concatenate other fields
          for (int i = 0; i < rdr.FieldCount - 2; i++)
          {
            sb.Append(rdr[i].ToString() + ";");
          }

          if (wkb)
          {
            WKBReader binRdr = new WKBReader();
            Byte[] geoBuf = new Byte[Convert.ToInt32((rdr.GetBytes(rdr.FieldCount - 1,
 0, null, 0, Int32.MaxValue)))];
            long cnt = rdr.GetBytes(rdr.FieldCount - 1, 0, geoBuf, 0, geoBuf.Length);
            g = binRdr.Read(geoBuf);
          }
          else //WKT
          {
            WKTReader wktRdr = new WKTReader();
            g = wktRdr.Read(rdr["geom"].ToString());
          }
          switch (g.GeometryType.ToUpper())
          {
            case "POINT":
              {
                sb.Append(g.Coordinate.Y);//latitude
                sb.Append(" " + g.Coordinate.X);//longitude
                break;
              }
            case "LINESTRING":
            case "MULTILINESTRING":
              {
                for (int i = 0; i < g.Coordinates.Length; i++)
                {
                  if (i > 0) sb.Append(",");
                  sb.Append(g.Coordinates[i].Y);//latitude
                  sb.Append(" " + g.Coordinates[i].X);//longitude
                }
                break;
              }
            case "POLYGON":
            case "MULTIPOLYGON":
              {
                for (int i = 0; i < g.Coordinates.Length; i++)
                {
                  if (i > 0) sb.Append(",");
                  sb.Append(g.Coordinates[i].Y);//latitude
                  sb.Append(" " + g.Coordinates[i].X);//longitude
                }
                break;
              }
          }
          records.Add(sb.ToString());
        }
        rdr.Close();
      }
      catch (Exception e)
      {
        records.Add(e.Message);
      }
      conn.Close();
      return records;
    }
  }
}

Listing 3 – PostGIS class improved using Net Topology Suite

Summary

Using just a rough response sense, the peroformance in best to worst order looks like this:

  1. PostGIS WKB
  2. PostGIS WKT
  3. MS SQL Server 2008

No real surprise here. This isn’t a precise comparison, but it does help me get a feel for response using these three approaches. Once OR/M is available for the geography data type, it will be of interest to see how a Linq OR/M generated version compares. All versions could be improved by using Base64 encoding to reduce the latency of pushing query results to the client view. An OR/M generated version should handle the DataService export to clients more efficiently.

Both npgsql and the .Net Topology Suite will be useful to anyone adapting .net c# code for use with spatial databases, especially considering the ubiquity of OGC Simple Feature types.

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.