sql server union on geography columns

Question

I'm using sql server 2008 R2, and I have two tables, Regions and Facilities. Both have a column containing a geography element.

I want to calculate the union on the intersection of the geograpy elements, like this:

SELECT * from Regions join Facilities on [Regions].[geography].STIntersects([Facilities].[geography])

which of course doesn't work. The regions are large polygons, and the facilities are points each of which is contained in only one polygon.

I can write some kind of (pseudocode)

for each r in Regions:
    for each f in Facilities:
        if f.[geography].STIntersects(r.[geography]):
            print r, f

but the whole point of using a database is to operate on the set not the elements, surely?

So, is there a better way to do this?

thanks Melanie

Doesn't this print (r,f)? `SELECT r.geography, f.geography from Regions r join Facilities f on r.geography.STIntersects(f.geography)=1` — RichardTheKiwi, Oct 10 '12 at 03:49
yes, it does - put it as an answer and I'll accept it - thanks. The magic is in putting the =1 on the end. — Melanie, Oct 10 '12 at 05:35

score 6 · Accepted Answer · answered Oct 10 '12 at 06:44

STIntersect(), like all boolean SQL Server functions, returns a bit so it's 0 or 1.
That becomes your WHERE condition.

The foreach of r and f are implicit in the JOIN statement.

SELECT r.geography, f.geography
from Regions r
join Facilities f on r.geography.STIntersects(f.geography)=1

1 Answers1