[postgis-users] Polygon Combinations

[Bryan K R Manuel]

Hello, 

I have a table of parcels and a table of trees, and I need to determine the parts of each parcel that are covered in trees and the parts that are not covered in trees.  The following code seems to work, but takes forever to run (40 hours).

create table ttemp (
id int4,
usage char(1),
the_geom geometry
) without oids;

insert into ttemp (id, usage, the_geom)
  select c.gid, 'A',
    difference(c.the_geom,
      ( select geomunion(b.the_geom) from parcels a, trees b
         where a.the_geom && b.the_geom
           and overlaps(a.the_geom, b.the_geom)
           and a.gid=c.gid
      )
    )
  from parcels c group by the_geom, gid;

--Time: ~20hr

insert into ttemp (id, usage, the_geom)
  select c.gid, 'W',
    intersection( c.the_geom,
        ( select geomunion(b.the_geom) from parcels a, trees b
          where a.the_geom && b.the_geom
            and overlaps(a.the_geom, b.the_geom)
            and a.gid=c.gid
      )
    )
  from parcels c group by the_geom, gid;

--Time: ~20hr

I thought that instead of having the inner join within the difference/intersection parts, it would be faster to make a separate table first, except what I've done so far is still taking a long time to run:

create table tptemp (
id int4,
the_geom geometry
) without oids;


insert into tptemp (id, the_geom)
  select a.gid,
    (select geomunion(b.the_geom) from parcels a, trees b
      where a.the_geom && b.the_geom
        and overlaps(a.the_geom, b.the_geom)
     )
  from parcels a group by gid;

insert into ttemp (id, usage, the_geom)
  select c.gid, 'A',
    difference(c.the_geom,
      ( select b.the_geom from tptemp b
         where c.gid=b.id
      )
    )
  from parcels c group by the_geom, gid;

insert into ttemp (id, usage, the_geom)
  select c.gid, 'W',
    intersection( c.the_geom,
      ( select b.the_geom from tptemp b
         where c.gid=b.id
      )
    )
  from parcels c group by the_geom, gid;

I am relatively new to PostGIS and PostgreSQL, so if anyone can help try to improve the performance of this code it would be greatly appreciated!

Thanks