Thought this would be harder than it turned out to be.
Objective: chart some metrics across geographic regions (in my case looking at customer metrics over New Zealand).
To get started I took advantage of SQL Server Spatial. Initially appearances are a bit daunting until you try it… and it’s fairly easy.
- Finding the GIS data
- for NZ Statistics NZ publish digital boundaries – versioned by publication year (2007, 2001, 1996 etc: http://www.stats.govt.nz/browse_for_stats/people_and_communities/geographic-areas/download-digital-boundaries.aspx
- The data is available in both ESRI Shapefile format and MapInfo format. You can use shape2sql to load this data into sql server spatial. Unfortunately… it has a bug, it won’t create a table from the shp file inside shape2sql – I had to use SQL Profiler to capture the create table instruction and run it interactively – after that the data load works fine - not sure why.
- The data has a hierarchy of increasingly larger geometrical areas made up of units of the smallest size (the meshblock). There’s Area Units, Urban Areas, Regional Councils etc.
- Connecting people to areas
- Either pay for the GeoPAF file from NZ Post – this gives the meshblock for a delivery address and a NZ Map Grid coordinate. (Not so useful NZ Post… NZMG has been superceeded by NZTM which conforms with WGS84.)
- Or, use the freely obtainable streetlink data from Stats NZ: http://www.stats.govt.nz/browse_for_stats/people_and_communities/geographic-areas/streetlink.aspx
- Aggregating
- Use SQL Server Analysis Services, or much faster
- Use PowerPivot – for me this allowed me to very quickly aggregate customer metrics from an internal database across areas loaded from the Stats NZ files
- Reporting
- It’d be nice if Excel had spatial reporting support, but it doesn’t – big thumbs down Microsoft…
- I used SQL Server Reporting Services and the Map control. Problem is that I need to have the map data in SQL/SSAS. It’d be nice to load from PowerPivot but I can’t do that unless I store the Excel PowerPivot doc in Sharepoint 2010. Jumping through hoops to do this though… Microsoft – just get the spatial support into Excel please!
No comments:
Post a Comment