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');
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,