Put two countries together

Sep 8, 2011 at 2:05 PM

Hi!

I'm a newbie in spatial data and maybe this is a very easy (stupid) question...

I wan't to combine two separate geo-structures like Canada and US into one geo-area.

Is there a function for this? Or is it a programatic way to solve it ?

Another function I would like to see is the opposite: Split one region into two. But i think that one is thouger to manipulate.

Best regards, Joakim

Developer
Sep 8, 2011 at 3:03 PM
Edited Sep 8, 2011 at 3:04 PM

Hi Joakim -

>I wan't to combine two separate geo-structures like Canada and US into one geo-area.

Basically, what you want to create is an aggregate.  There are several ways to do this. You can do this easily with two spatial objects at a time using the STUnion() method:

DECLARE @us GEOGRAPHY = (SELECT geog FROM COUNTRIES WHERE name = 'US');
DECLARE @canada GEOGRAPHY = (SELECT geog FROM COUNTRIES WHERE name = 'Canada');
SELECT @us.STUnion(@Canada);

For SQL Server 2008, a more flexible way to do this would be to use the GeographyUnion() function in the CodePlex SQL Server Spatial Tools Project:

SELECT GeographyUnionAggregate(geog) FROM Countries WHERE name in ('US', 'Canada');

For SQL Server Code-Named "Denali" (currently available as Denali CTP3), there is a new built-in aggregate, UnionAggregate, which supports this operation:

SELECT GEOGRAPHY::UnionAggregate(geog) FROM Countries WHERE name in ('US', 'Canada');

>Another function I would like to see is the opposite: Split one region into two. But i think that one is thouger to manipulate.

You can functionally do this a number of ways but I would encourage you to investigate the STIntersection() method.

I hope this help,

- Ed

 

 

Sep 8, 2011 at 9:39 PM

GREAT Ed!

How wonderful to take giant steps in the development with your help!

It works perfect!

Best regards, Joakim