[postgis-users] Function OVERLAPS() versus &&

[James G Wilkinson]

This is a followup to a question that I had a couple of days ago...

Here is an example query:

CREATE TALBE us_pophu_prj AS
     SELECT a.gid, b.gid AS sec_gid, GEOMUNION(a.the_geom,b.the_geom)
     FROM us_pophu a, us_tribes b
     WHERE a.the_geom && b.the_geom AND
     OVERLAPS(a.the_geom,b.the_geom);

When I first wrote this query, I was expecting the selection to return
a coverage that had all polygons from US_POP and US_TRIBES.
Obviously, this did not occur since this query is designed only to return
the union of polygons that overlap one another.

Okay, so I said...."hmmmm....just drop the WHERE clause:"

CREATE TALBE us_pophu_prj AS
     SELECT a.gid, b.gid AS sec_gid, GEOMUNION(a.the_geom,b.the_geom)
     FROM us_pophu a, us_tribes b;

And without thinking about, I kicked the query off.  Now, 18 hours later,
the query is still processing. 

Soooo, now I am really trying to think through what I really want instead
of just willy-nilly trial-and-error (everyone can now have a good chuckle
at my expense :-) )

Okay, so here is what I really want.  I want a query that will return all
the polygons in US_POPHU plus those new polygons that result from
overlapping polygons from US_POPHU and US_TRIBES.  I have tried
using LEFT JOIN without success, as in the following example:

CREATE TALBE us_pophu_prj AS
     SELECT a.gid, b.gid AS sec_gid, GEOMUNION(a.the_geom,b.the_geom)
     FROM us_pophu a
     LEFT JOIN us_tribes b
     ON a.gid >= b.gid OR a.gid <= b.gid 
     WHERE a.the_geom && b.the_geom;

Any help that can prevent me from further knocking my head against the wall
will most certainly be appreciated.

Regards,

Jim