RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

ASP.NET / ADO.NET

SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

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

By : John O'Brien
Nov 25, 2008

Page 2 / 3

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.

<< Prev Page Next Page>>