Directions Magazine
Hello. Login | Register

Articles

All Articles | Post Comment

My Top 6 Takeaways from SQL Server Spatial 2012

Bookmark and Share
Monday, June 18th 2012
Comments
Classified Ads:

Summary:

SQL Server’s spatial capabilities have evolved significantly since their introduction in the 2008 release. Given the well documented beta (CTP), the March RTM, and April release, there has been a great deal of coverage of the new spatial features in SQL Server 2012. (See great posts by Alastair Aitchison and Lenni Lobel, and be sure to check out Ed Katibah’s blog. My favorite summary is a 12 minute video by Greg Low.)

I’d like to reflect on the six spatial enhancements to SQL Server 2012 that are of most interest to myself and Safe Software.

My Top 6 Takeaways from SQL Server Spatial 2012

1. Curves

Following the OGC model (like PostGIS), curve support comes with three new types: circular strings (sequences of circular arcs), compound curves (sequences of circular arcs and straight lines), and curve polygons (areas bounded by curved and/or straight segments).

SQL Server 2012 brings three major innovations to the table:

  • Full support for existing operations: For example, you can get a curve’s length, or find the shortest line between anywhere on a curve and another geometry.
  • round-earth definition of curves: You can define curves in the geodetic “geography” type – an industry first – and again, all of the spatial operations just work. We see lots of lat/long data with curves inside. With SQL Server, you can now query and manipulate this data without stroking arcs into lines or projecting your data onto a locally-flat coordinate system.
  • Buffering with curves: Buffering expands a geometry by a specified distance in every direction – and typically results in lots of round edges. SQL Server 2012 optionally represents the rounds bits as arcs, which makes the results more accurate and compact. As above, this works with both the locally-flat-earth (geometry) and globally-round-earth (geography) types.

2. Fullglobe

The round-earth geography type can now represent shapes that don’t fit inside a hemisphere (very roughly: the ones that can’t be fully seen from any point in space). Taking that idea to the limit, there is also new way to specify “the whole earth”: FULLGLOBE. Bob Beauchemin has a nice pair of posts on the subject.

While there isn’t a lot of data that falls into this case, when you need it, it’s a big deal. It also means you can express concepts like “everywhere except ”, which can make for more expressive queries.

This extra power comes with extra responsibility – the sole difference between “somewhere” and “everywhere else” comes down to coordinate order – so more care is required to get this right.

3. Spatial Indices

Creating spatial indices in SQL Server 2008 is hard. Specifically, it’s not obvious how to choose good index parameters (and by good I mean “makes queries faster at all”). This is a big deal for two reasons: (i) For real-world data volumes, you need spatial indices to get reasonable performance, and (ii) If you’re using SQL Azure, you can’t even load spatial data without them.

So I am very excited that SQL Server 2012 lets you skip these parameters (you still need to know the bounds for projected data), and it figures everything out automatically.

4. Invalid Geography

One of the biggest hassles with SQL Server 2008’s round-earth geography type is that invalid data could not be loaded at all. SQL Server 2012 lets you load this data and repair it after – a huge convenience.

The new release also adds an improved validation method, IsValidDetailed(), that provides detailed answers to the question “why was my geometry considered invalid?”

As an experiment, I loaded a Shapefile containing country boundaries in lat/long into SQL Server 2008 and 2012. SQL Server 2008 rejects Fiji (because the data breaks it into two pieces along the 180 degree meridian, and multipolygons can’t share a common edge) and Antarctica (for a similar reason, but more generally due to the non-geodetic nature of the input data and issues near the poles). SQL Server 2012 loads all the data, and then provides tools to inspect and repair afterwards. Interestingly, it detects three invalid features, with Russia joining Fiji and Antarctica, perhaps due to precision issues (more on that below).

5. Improved Precision

SQL Server 2012 uses more precision (48 bits instead of 27) for geometric operations, resulting in fewer slivers, less visible rounding of coordinates, and generally better results. In some cases, the improvements are dramatic: In Alastair’s blog, he gives an example of two lines; SQL Server 2008 says they don’t intersect, and SQL Server 2012 says they do. I’ve reproduced his results, and would like to add only one thing: a picture.

precision in SQL Server

 

At Safe, we’ve spent a phenomenal amount of time battling problems like these, so this result isn’t entirely surprising. I do find it interesting how much easier it is for a person to see the answer, though (at least for this data).

An important aside: The increased precision is about processing, not storage. Coordinates are still stored as IEEE doubles, with their 53 bits of precision. Why do we care? It means the internal representation of existing geometric types (points, lines, …) hasn’t changed.

6. Relationship to SQL Azure

Today, SQL Server 2012 has more spatial functionality than SQL Azure – Azure is limited to operations that work on the old geometry types. Over time, that role will reverse, with new spatial features debuting in Azure, and eventually making their way to the next major release of SQL Server. This reinforces an ongoing trend – the cloud is getting more important.

Overall, I see SQL Server 2012’s spatial support as a well-thought out, significant evolution from the 2008 release, with improvements for all the major pain points. Are you using SQL Server Spatial? If so, what do you think of the new functionality?
 
Reprinted with permission from the Safe Software Blog, It's All About Data.

Bookmark and Share


Stay Connected

Twitter RSS Facebook LinkedIn Delicious Apple Devices Android Blackberry






Recent Comments


DirectionsMag.com

About Us | Advertise | Contact Us | Web Terms & Conditions | Privacy Policy
© 2014 Directions Media. All Rights Reserved