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:

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)))

image

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).

About these ads
This entry was posted in Spatial, SQL Server and tagged , , , , . Bookmark the permalink.

7 Responses to SQL Server 2012 – What’s New for Spatial?

  1. Anthony says:

    There are a couple of things I’m interested in and perhaps if you have some time you could blog about them?
    Was wondering if producing heat maps in SQL2012 was any easier using SSRS rather than the previous example where you rendered it using HTML5 in IE9?

    Also was interested how you might do a drive-time analysis with many data points, i.e. Whats the quickest way from point 1 to point 2 and then plot some sort of time graph over the map to show how long it would take to drive to different zones? I know you can use the Bing\Google API to do a route distance calculation but this probably won’t be good enough for thousands of routes…

  2. alastaira says:

    Hi Anthony,

    I can’t think of anything in SQL 2012 that would affect either heatmaps or drive-time polygons…
    Heatmaps are really just visual representations of point data, and so are almost always calculated in a presentation layer (normally on the client itself) rather than in the database layer.

    Drivetime polygons require relatively complex logic to calculate (and, also, a lot of data!). You certainly *could* calculate drivetime polygons in a CLR procedure in SQL Server, but I don’t think SQL 2012 would provide any advantage here over what you can already do in SQL 2008. In addition to creating the routing logic, you’d also need a dataset that had details of the entire road network, including speed limits on each segment, turn restrictions etc. and that’s why it’s almost always easier to use an application dedicated to the task. If you don’t want to make thousands of calls to a Google/Bing webservice, remember that you can do this locally using the Mappoint desktop application: https://alastaira.wordpress.com/2011/01/30/drivetime-polygons-with-mappoint-and-bing-maps/

  3. Anthony says:

    Both thanks for the post. Will give them a go! Would be nice to generate SSRS polygons for the heat map that mirror those of the map itself, like a layer overlay. Will look at the code to see if I can see a way…

  4. Pingback: My Top 6 Takeaways from SQL Server Spatial 2012 | It's All About Data

  5. Bharat says:

    Hi Alastair

    I read your post and found it really helpful. I am working on a scenario where I need to plot data on a heat map in SQL 2102 at a level of customized geography definition (which has a mapping to the zips in the physical geography). I was wondering if you would know how this can be achieved in SSRS using SQL 2012.

    Thanks in advance

    Regards
    BT

  6. Bharat says:

    @Jason

    Liked the road blogs and comforts me to think of the help available around implementing the squarified heat maps ! Would be great to work on this in some of my other implementations.

    However, focusing on the one I mentioned in my previous post (mapping logical geographies to US map) !

    Regards
    BT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s