[postgis-users] Polygon Combinations

[Andy Anderson]

I could be misunderstanding your intent or your SQL, but in your  
inner select/where clause, I wonder about the order of your tests.  
Shouldn't you make sure that a.gid=c.gid first, and then do the other  
tests? Otherwise it appears that for every parcel, you're checking  
every parcel against every tree polygon, i.e. N * (N * M) calculations.

You might want to look at this section of the Postgres manual where  
it discusses how to force the order of evaluation:

	http://www.postgresql.org/docs/8.3/interactive/sql- 
expressions.html#SYNTAX-EXPRESS-EVAL

-- Andy

On May 15, 2008, at 3:21 PM, Bryan K R Manuel wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users