Combining sql server by geography columns

Question

I am using sql server 2008 R2 and I have two tables, regions and objects. Both have a column containing a geography element.

I want to calculate the union at the intersection of georegulation elements, for example:

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

which of course does not work. Regions are large polygons, and objects are points, each of which is contained in only one polygon.

I can write some (pseudo code)

 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 work with many, not elements, right?

So, is there a better way to do this?

thanks melanie

Answer 1

STIntersect (), like all logical functions in SQL Server, returns a bit so that it is 0 or 1.
This becomes your WHERE clause.

Preview 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