Powerful Geographical Visualisations made easy with SQL 2008 Spatial (Part 1)

I’m always impressed by visualisations that can take complex data and present the user with something so meaningful and simple that it gets the message across instantly. I was impressed and inspired by this presentation at TED and thought how hard would it be to do this within the new tools of SQL 2008 Spatial? Join me for a look at what SQL 2008 can and can’t do in the quest to duplicate that amazing spatial visualisation.



Countries resized by Carbon dioxide emissions, 2004, Data Source CDIAC.

The Spatial components for SQL Server are new to the 2008 release, they are a core part of the server product (excluding compact edition) rather then an extra add-on or product version. There are some great walk through's of the full functionality available, I highly recommend David Lean’s samples 1 through 9 for a run through of all the new functionality available.

Something very simple to visualise within the built-in spatial viewer of Management Studio are the countries of the World:



SELECT geom FROM dbo.SimpleWorld

What if we could redraw the countries based on actual CO2 emissions? What if the countries size was based on their emission output?

Step 1, get the data
In order to create this visualisation first we need some Geo-spatial data. In this example we need two sources, country polygons (as seen above) and the actual CO2 emissions per country. Data sources are available online and vary in cost and quality. I’ve chosen the World Polygons from Geocommons and the Co2 emissions from the UN.

Country Polygons
The finder site at Geocommons is awesome, I highly recommend it and would encourage you to contribute data back to the project. I downloaded the Shapefile of the data and then used Shape2SQL from Morten Nielsen to import the data into my SQL 2008 database. Specifically I set the type the Geography, the SRID to 4326 and selected the .shp file from the zip file.



Finally I renamed the table to something more appropriate, “SimpleWorld”. Important to note is I have been unable to rename a Geography field later, so you may want a better name then ‘geom’ at this point.