Wednesday, December 08, 2010

Spatial Notes–Getting Started

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
  • 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: