SQL Server 2012 – What’s New for Spatial?

So Microsoft launched SQL Server 2012 today, and you can download the SQL 2012 Express edition right now from http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

No doubt there will be lots of people writing blog articles about the various new features – AlwaysOn, Column Store , Power View etc. but I thought I’d stick to what I know and write about what’s new for spatial.

In fact, I don’t really even need to do that, because there’s always a pretty good whitepaper about that from the SQLCAT website, here. This whitepaper is written for Denali CTP3 but, to my knowledge (and from my limited playing around with RTM this morning), there don’t appear to be any new features introduced between then and now.

There’s also already a very useful table here that compares the spatial features currently available in SQL Azure to those in SQL Server 2012.

SQL Server 2012 Spatial Was My Idea

So, what’s left to do? Well – I might start by mentioning the fact that I’m quite chuffed about some of the new features that have been included. Do you remember the Windows 7 advertising slogan with people saying things they did with their PC and that “Windows 7 was my idea”? Well, that’s how a little bit like how I feel about SQL 2012:

• Firstly, there’s the new ShortestLineTo() method, which solves the problem I described here: http://social.msdn.microsoft.com/Forums/en/sqlspatial/thread/cb094fb8-07ba-4219-8d3d-572874c271b5
• There’s also the new IsValidDetailed() method, which helps to address the problems of fixing invalid data that I described here: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/22043a53-75fd-45a5-919a-0e180dd9006b
• There’s much greater parity between the range of methods of the geometry and geography datatypes, which I’ve often commented on.
• Also, some (but not all) of the bugs I’ve reported on MS Connect have been fixed.

I’m not suggesting that these features were introduced solely to appease my personal whims, but don’t let it ever be said that Microsoft don’t listen to customer feedback!

Upgrading a Spatial Application from SQL Server 2008/R2?

Finally, some words of warning for anyone upgrading existing spatial databases from SQL Server 2008/R2 to SQL Server 2012:

Remember to account for Curved Geometries!

The introduction of curved geometry datatypes mean that the range of possible geometries that can be returned by existing methods is increased, and you may need to write additional code paths to deal with that. Take the STConvexHull() method, for example, which returns the convex hull around a geometry. In SQL Server 2008/R2, that method would always return a Polygon (other than in the special case in which all the input points lay on a straight line, in which case it returned a LineString). In SQL 2012, however, if any of the inputs are themselves curved geometries, the convex hull returned by STConvexHull() can be a CurvePolygon, e.g.:

DECLARE @g geometry = 'GEOMETRYCOLLECTION(CIRCULARSTRING(0 0, -5 5, 0 10), CIRCULARSTRING(10 10, 15 10, 10 0))';
SELECT
  @g.STConvexHull(),
  @g.STConvexHull().STAsText();

The result of STConvexHull() in this example is:

CURVEPOLYGON (COMPOUNDCURVE (CIRCULARSTRING (18.090169943749487 5.0000000000000213, 16.351988430497684 9.051195518771241, 12.240554713995163 10.584146142748239), (12.240554713995163 10.584146142748239, -0.23001591065449833 9.9947064659342839), CIRCULARSTRING (-0.23001591065449833 9.9947064659342839, -4.99999999999994 5.0000000000000213, -0.23001591065449833 0.00529353406575872), (-0.23001591065449833 0.00529353406575872, 12.240554713995163 -0.5841461427481951), CIRCULARSTRING (12.240554713995163 -0.5841461427481951, 16.351988430497684 0.94880448122880079, 18.090125812525294 4.9777873878621213), (18.090125812525294 4.9777873878621213, 18.090169943749487 5.0000000000000213)))

Calculated Results may differ due to Increased Precision

SQL Server 2012 now uses 48 bit precision for spatial calculations rather than 27 bit as under SQL Server 2008/R2. This means that results of certain spatial queries will differ between those obtained under SQL Server 2008/R2. This has been mentioned before, but it really is worth iterating because it’s both unusual and potentially application-breaking behaviour.

The following very simple example demonstrates the issue:

DECLARE @line1 geometry = 'LINESTRING(0 11, 430 310)';
DECLARE @line2 geometry = 'LINESTRING(0 500, 650 0)';

SELECT @line1.STIntersection(@line2).STIntersects(@line1);

• When executed under SQL Server 2008/R2, you’ll get the result 0.
• Upgrade to SQL Server 2012, and you’ll suddenly get the result 1.

For a method that returns a bit value, that’s pretty much the greatest difference it’s possible to get…. so be sure to check your code (especially if you ever rely on exact equality testing between instances).