THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and runs training courses around the world in SQL Server and BI topics.

Spatial data from shapefiles (for T-SQL Tuesday #006)

I’m giving a presentation on May 12th at the Adelaide .Net User Group, around the topic of spatial data, and in particular, the visualization of said data. Given that it’s about one the larger types, this post should also count towards Michael Coles’ T-SQL Tuesday on BLOB data.

I wrote recently about my experience with exploded data, but what I didn’t go on to talk about was how using a shapefile like this would translate into a scenario with a much larger number of shapes, such as all the postcode areas in the US and Australia, plus high-level postcodes from the UK or Canada (US and Aus roughly have a postcode per city/suburb, whereas the UK & Canada use a postcode for a much smaller group of addresses, with the city/suburb being typically the first half of the postcode).

The issue comes down to the fact that the shapefile isn’t filtered. It contains all the shapes. So if you want to display the ones near a point of interest, you’re having to trawl through the lot still. Maybe not what you’re after. So the trick is to use polygons stored in a geography field in SQL, and use that instead. Basically rejecting that “ESRI shapefile” option that Report Builder 3.0 presented. And in particular, put a spatial index on that geography field.

I could go into a ton of detail about the way that spatial indexes work, about how they apply a grid over the world, and then break the squares that result into smaller squares, until they get into quite some level of detail – but I’ll let you research that through Books Online or the like. I just want to point out that the geography type CAN be indexed, and that this allows you to handle a much larger set of regions without incurring the performance hit that you’d get if you had massive shapefiles.

Unfortunately, shapefile data isn’t trivial to get into a geography type, but Morten Nielsen has put a great tool together which you can use for this. It works very well indeed.

In my presentation, I’ll go into a lot more stuff that I’ve learned about shapefiles and the like, but I’ll let you come along and discover that in person. If there’s interest in this stuff, I might even submit a talk on this for some of the upcoming conferences, such as TechEd AU/NZ or PASS.

Published Tuesday, May 11, 2010 3:57 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Stacia Misner said:

Host Michael Coles ( blog | twitter ) has selected LOB data as the topic for this month's T-SQL Tuesday

May 11, 2010 3:30 PM
 

Michael Coles: Sergeant SQL said:

T-SQL Tuesday this month was all about LOB (large object) data. Thanks to all the great bloggers out

May 13, 2010 7:51 PM
 

Rob Farley said:

Interestingly, my favourite new feature of SQL Server 2008 R2 isn’t any of the obvious things. 

June 7, 2010 8:09 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement