How to STUnion a STIntersect

Question

Please excuse if this has been covered elsewhere but I'm relatively new to Spatial elements within SQL Server.

I'm looking to create a proc in SQL server that when passed a simple polygon, will check a table to see if the new polygon intersects any existing polygons in that table.  If it does, Union the polygon to the existing ones.  If no intersect exists add the original polygon to the table as a new record.

I think it sounds simple but am struggling to find a solution.

Many thanks for any help.

WJRH

Answer 1

If I’m not mistaken, what you want to return is the spatial union of all the polygons in the table that intersect an input polygons. If you have SQL Server 2012 or SQL Azure Database, this would be the SQL code in the stored procedure below (where @g is your input polygon, @gout is you union of all the polygons that intersect it).  In this example, I’m using a dbo.Congress table that has a column named shapegeog to match against.

 

create proc dbo.UnionIntersect (@g geography, @gout geography output)

as

with w as

(select shapegeog from dbo.congress

where shapegeog.STIntersects(@g) = 1

)

select @gout = geography::UnionAggregate(w.shapegeog) from w;

 

If you want to include the input polygon in the union, you could change it to:

 

create proc dbo.UnionIntersect (@g geography, @gout geography output)

as

with w as

(select shapegeog from dbo.congress

where shapegeog.STIntersects(@g) = 1

union all

select @g

)

select @gout = geography::UnionAggregate(w.shapegeog) from w;

 

-- invoke it like this

declare @a geography = 'POLYGON((-124.5 44.5, -122.5 44.5, -122.5 45.3, -124.5 45.3, -124.5 44.5))';

declare @b geography;

exec UnionIntersect @a, @b output;

select @b;

 

Just change the procedure slightly (remove references to the @gout variable)  to return the output geography as a one column, one row resultset.

 

If you don’t have SQL Server 2012 or SQL Azure Database you can get this aggregate from the SQL Server Spatial Tools project on Codeplex.

 

Cheers, Bob

 

Answer 2

After rereading your request, I think you’ll want the second variation. The first variation returns NULL if there are no matching polygons.

 

You can add the insert statement you mention using the variable @gout, rather than returning it to the caller.

 

Cheers,

Bob

Answer 3

Hi Bob,

I agree with your answer. Out of curiosity, is there a reason why you use a CTE in the query pattern?

i.e. instead of:

with w as
(select shapegeog from dbo.congress
where shapegeog.STIntersects(@g) = 1
)
select @gout = geography::UnionAggregate(w.shapegeog) from w;

you could use:

select @gout = geography::UnionAggregate(shapegeog) from dbo.congress
where shapegeog.STIntersects(@g) = 1;

Didn't know if that was just a personal style thing, or whether there's a performance/other reason....?

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 4

Thank you Bob for the help, two quick questions, 1) would this work the same for Geometry instead of Geography? 2) I'm currently using SQL Server 2008, how does the SQL server Spatial tools work?

Sorry for the basic questions, but learning quickly and loving spatial data!

Answer 5

SQL Spatial Tools is a library of spatial extensions put out (mostly) by the folks on the SQL Server spatial team. Some of them (like the aggregates) eventually made their way into the product. You download the library (it’s a ..NET assembly) and there’s a registration DDL script that catalogs (IIRC) user-defined aggregates, functions, etc. Check the syntax, I’m sure it’s a little bit different from the product syntax.

 

Hmm, looking at the provided code, it doesn’t look like there’s a native GeometryUnionAggregate as part of the package. But it should be trivial to code up using by cloning the code for GeographyUnionAggregate and simply changing the data type. Or code a table-valued function in SQLCLR that does the aggregation and returns a 1-column, 1-row resultset. Else, you’re using a cursor or multi-statement TVF in T-SQL, and it’s going to be order of magnitude slower. Or, it’s a good reason to upgrade to SQL Server 2012.

 

And I had no special reason to use the CTE syntax that I did except that, if you wanted the input geography as well, it seeming more straightforward to add it in

with UNION ALL.

 

Hope this helps,

Bob

Answer 6

I had no special reason to use the CTE syntax that I did except that, if you wanted the input geography as well, it seeming more straightforward to add it in with UNION ALL.

I only wondered in case you knew of some super-secret performance-enhancing query pattern that used CTEs... ;)

---

twitter: @alastaira blog: http://alastaira.wordpress.com/