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

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.

## No comments yet... Be the first to leave a reply!