Here we have installed the additional SQL Sever Spatial Tools from codeplex. These tools give us extra functionality including the GeographyUnionAggregate Aggregate Function that allows us to group our country geography objects into regions as demonstrated.
By using a UNION ALL we can show two sets of Geography in the one viewer as above.
We start to give context here but it is not clear what circles belong to what country. Although the land mass of the circles = that of the country geometry, it certainly doesn’t look equal. The solid circles are too efficient at representing area and an unrealistic representation of countries.
What if we simply buffered the actual country geography?
As you recall we need to set a new area on our countries, we can modify an existing geography by applying a buffer. Lets build a function that takes a Geography, a desired Area, a guess of what the buffer should be and a tolerance. Although we can’t set a new Area, we can test a STBuffer and compare the new Area with the desired result. It will not be fast as we will have to test many times per country but will eventually be accurate.
We actually split this into two functions, first we need a range that the buffer must be between and then we can simply use recursion to split the range until we hit our tolerance or hit the maximum 32 levels of recursion allowed in SQL Server.
CREATE FUNCTION [dbo].[GetBufferForAreaByGuess]
(
@Geo geography,
@DesiredArea real,
@Tollerance real,
@BufferGuess real
)
RETURNS real
AS
BEGIN
DECLARE @Buffer real
DECLARE @PrevBufferGuess real
DECLARE @finished bit
DECLARE @Area real
–Check that the guess is the correct direction, if not reverse
IF (@Geo.STArea() – @DesiredArea > 0 AND @BufferGuess > 0) OR (@Geo.STArea() – @DesiredArea < 0 AND @BufferGuess < 0) BEGIN
set @BufferGuess = -@BufferGuess
END
set @PrevBufferGuess = 0
set @finished = 0
WHILE (@finished = 0) BEGIN
select @Area = @Geo.STBuffer(@BufferGuess).STArea()
–shortcut if guess is within tollerance
IF ((@Area > (@DesiredArea – @Tollerance)) AND (@Area < (@DesiredArea + @Tollerance))) BEGIN
SELECT @Buffer = @BufferGuess
SET @finished = 1
END ELSE BEGIN
IF (@BufferGuess > 0) BEGIN
IF @Area > (@DesiredArea – @Tollerance) BEGIN
SELECT @Buffer = dbo.GetBufferForAreaByBounds (@Geo, @DesiredArea, @Tollerance, @PrevBufferGuess, @BufferGuess, 1)
SET @finished = 1
END
END ELSE BEGIN
IF @Area < (@DesiredArea + @Tollerance) BEGIN
SELECT @Buffer = dbo.GetBufferForAreaByBounds (@Geo, @DesiredArea, @Tollerance, @BufferGuess, @PrevBufferGuess, 1)
SET @finished = 1
END
END
SET @PrevBufferGuess = @BufferGuess
SET @BufferGuess = @BufferGuess*2
END
END
RETURN @Buffer
END
This first method validates the guess is in the right direction, negative to decrease area and positive to increase area. It then doubles the guess until it exceeds the Desired Area, this gives us a range to pass to the next function:
CREATE FUNCTION [dbo].[GetBufferForAreaByBounds]
(
@Geo geography,
@DesiredArea real,
@Tollerance real,
@MinBuffer real,
@MaxBuffer real,
@RecursionLevel int
)
RETURNS real
AS
BEGIN
DECLARE @BufferGuess real
select @BufferGuess = (@MaxBuffer + @MinBuffer) / 2
set @RecursionLevel = @RecursionLevel + 1
if (@RecursionLevel < 32) BEGIN
IF (@Geo.STBuffer(@BufferGuess).STArea() > @DesiredArea + @Tollerance) BEGIN
SELECT @BufferGuess = dbo.GetBufferForAreaByBounds (@Geo, @DesiredArea, @Tollerance, @MinBuffer, @BufferGuess, @RecursionLevel)
END ELSE IF (@Geo.STBuffer(@BufferGuess).STArea() < @DesiredArea – @Tollerance) BEGIN
SELECT @BufferGuess = dbo.GetBufferForAreaByBounds (@Geo, @DesiredArea, @Tollerance, @BufferGuess, @MaxBuffer, @RecursionLevel)
END
END
RETURN @BufferGuess
END
A simple recursive function that splits the range until the answer is within the required tolerance or the maximum number of recursive steps (32) has been exceeded.
On my computer it took about 4 min to create the visualisation of all countries:
SELECT
w.[Name] as ‘name’
, w.[geom].STBuffer(dbo.GetBufferForAreaByGuess (
w.[geom]
, (Sum(w.[geom].STArea()) over() / sum(s.[2004]) over() * s.[2004])
, 1000000
, SQRT((Sum(w.[geom].STArea()) over() / sum(s.[2004]) over() * s.[2004])/pi()) – SQRT(abs(w.[geom].STArea())/pi()))
)
FROM
dbo.SimpleWorld w
JOIN
dbo.[WorldCO2Emissions] s ON w.[Name] = s.[Country]
The guess we make for the buffer is based on our circles, if a country was a circle then the buffer would be modified by the difference between the two radius.