Using ‘Reduce’ to simplify polygons
The data visualisation we are about to perform is very sensitive to the complexity of the Geography fields we use. If we don’t need the extreme detail at the level we will view from, we can remove points using the Reduce() method. For example here is a highly complex and detailed geography of Australia with Reduce set to 100,000, 10,000, 1,000, 100 and 0.
SELECT geom.Reduce(X) FROM dbo.AUS0
Statistical Data
The UN data was available as an Excel spread sheet, drop all the cells that where irrelevant and import as “WorldCO2Emissions”
We can carry out a simple query like:
| Country | 2004 CO2 Emissions |
| United States | 6049436 |
| China | 5010170 |
| Russia | 1524993 |
| India | 1342962 |
| Japan | 1257963 |
SELECT TOP 5 Country, [2004] FROM dbo.WorldCO2Emissions ORDER BY [2004] DESC
Clean up
Our next step is to join the country polygons with the statistical data, we will have to join on country name but unfortunately these can be slightly different. I found this was a short manual process of matching those currently not joined as best as possible by renaming to match. Many data sets use the 3 letter country code which is more reliable.
Important to note is that the world is an ever changing place, the names of countries and their borders shift often. For example the statistical data for 1990 contains CO2 emissions for the USSR. I would need to match to a set of World geography from 1990 to be accurate. I will stick with 2004 only here.
I find the best thing to do with data visualisation is to ask a specific question of the data. In this case we ask, What does the World look like if its land mass is reallocated based on CO2 emissions per country in 2004? In part 2 will show how we can anser this question with SQL Server 2008.
]]>