I’m sure that most people would agree (well GIS folks for sure) that the arrival of SqlServer 2008 with native spatial types was pretty exciting news. It’s only recently, though, that I’ve really had a chance to see it in action. I’ve just had the opportunity to be involved in a large scale GIS project involving the complete Microsoft stack: Sharepoint, .NET MVC, and SqlServer 2008.
So what’s it like working with Microsoft’s flagship database and spatial data? Well, in a word, “different”. Different compared to what you may ask. But first a little background. Up until working with 2008 I’d been involved in a few large open source projects. Marinemap.org’s Decision Support Tool and the just released Middle Eastern Geodatabase for Antiquities – Jordan. Both of these projects used Postgres with the PostGIS spatial extension for the database while MarineMap used GeoDjango for the web framework and MegaJordan used .NET MVC.
So how is using SqlServer 2008 different than PostGIS or ESRI SDE? For starters, there is no metadata that defines the SRID of spatial tables nor is the spatial type (point, line, poly, etc..) stored anywhere either. That means that you can put data with differing SRIDs and spatial types into a single table. If you’ve used SDE before, this may seem sacrilegious. Same goes for PostGIS. This has some benefits. Imagine you want to store features representing monuments. In it you have the Washington Monument represented as a point, the Vietnam Memorial as a line, and the reflecting pool in front of the Washington Monument as a polygon. If you’re using an ESRI based system you’re stuck maintaining 3 separate tables. Bummer! Ahhh, but not in SqlServer 2008. In that system you can have 1 table called monuments that can represent all 3 types.
So that works well for having mixed spatial types in a single table, but the same system also allows for mixed spatial reference systems in a single table. What, what, what!? That’s right, you can have 1 feature stored as 4326 and the next one as 3310. From the documentation: “Each spatial instance has a spatial reference identifier (SRID)”. If you’re not careful, someone could really mess up your data. That someone may be you. And as soon as you start to store data with mixed SRID’s you can forget about using spatial operators on the data (think intersect, or buffer). Again from the docs: “If two spatial instances do not have the same SRID, the results from a geometry or geography Data Type method used on the instances will return NULL.” Microsoft isn’t lying. I’ve sadly confirmed it myself….the hard way. That’s a little gotcha to keep in mind. There’s another thing you probably won’t believe. As of now, SqlServer 2008 doesn’t support reprojection. I know, I couldn’t believe it either. I’m sure it’s coming, though, right Microsoft? Because without this, it’s hard to image that anyone would consider this system seriously for storing spatial data.
Using SqlServer 2008 from within MVC .NET or Sharepoint, or any other .NET technology is the same as it always was, and with LINQ and/or Entity Framework it’s even easier. But….it isn’t. At least if you think you’re going to do spatial data manipulation in code. That’s because the spatial data types of geography and geometry aren’t supported in LINQ or EF. You know how much this sucks if you’ve ever used GeoDjango. Image buffering a geometry in code, or reprojecting it for display just as easily as you’d format a string. GeoDjango with PostGIS can do that (and they’re both free!).
Those are my major observations for now. They’re obviously not exhaustive, but the few things I’ve touched on are significant. I didn’t mean to bash the mother ship either. I’m sure at some point (r3 maybe?), we’ll be able to reproject our data natively using LINQ or EF, and when that day comes, we will weep (tears of joy) and thank MS for making our jobs just a little easier.
I’d love to hear what other people think….really. Post to the comments field or send an email.
