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

Step2, Ask our Question
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?

Lets start by calculating the data that we will need, the total land area and then the new land area per country?

SELECT top 5
    w.[Name] as 'Country'
,    w.[geom].STArea() as 'Area'
,    (Sum(w.[geom].STArea()) over() / sum(s.[2004]) over() * s.[2004]) as 'new Area'
FROM
      dbo.SimpleWorld w
JOIN
      dbo.[WorldCO2Emissions] s ON w.[Name] = s.[Country]
ORDER BY s.[2004] DESC

Our new Area is simply the Sum of all the countries Area (= total land area of the world), divided by the sum of all the emissions multiplied by the actual emission from that country. EG if the total world land mass was 10 units and there are 50 total units of emissions, a country with 5 emission units should have an area of 1 unit.

Now there is no way to set a new Area on a Geography object, we can however use a method called STBuffer() to enlarge or shrink the Geography.

First lets look at the World’s landmass as simple circles



SELECT [geom].EnvelopeCenter().STBuffer(SQRT(abs([geom].STArea())/pi()))
FROM dbo.SimpleWorld

EnvelopeCenter() gives as an approximate centre of our Geography object, The US for example is shifted way off to left due to Hawaii and Alaska.

STBuffer() increases a Geography by a number of meters, in the case of a circle this is the radius.

The Square Root of the Area divided by Pi gives us the radius of each country if it was a circle.

Using this concept it would be quite easy to resize the countries to their new Area based on CO2 emissions:



SELECT
      w.[NAME]
,     w.[geom].EnvelopeCenter().STBuffer(SQRT((Sum(w.[geom].STArea()) over() / sum(s.[2004]) over() * s.[2004])/pi()))
FROM
      dbo.SimpleWorld w
JOIN
      dbo.[WorldCO2Emissions] s ON w.[Name] = s.[Country]

Although this is very fast to execute, gives accurate land mass it does not get across the visualisation we ultimately want, it hard to identify visually what country is what. We really get no impact of whether a country has grown or shrunk.

Lets put the actual regions in with these results to give some context.



SELECT
      w.[NAME]
,     w.[geom].EnvelopeCenter().STBuffer(SQRT((Sum(w.[geom].STArea()) over() / sum(s.[2004]) over() * s.[2004])/pi()))
FROM
      dbo.SimpleWorld w
JOIN
      dbo.[WorldCO2Emissions] s ON w.[Name] = s.[Country]
UNION ALL
SELECT REGION
,    dbo.GeographyUnionAggregate([geom], 0.5)
FROM [SpatialTest].[dbo].[SimpleWorld]
GROUP BY REGION