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.
