[postgis-users] Overlap Queries - speed

[Bill Binko]

On Mon, 3 Oct 2005, Robert Burgholzer wrote:

> I have a question regarding performance, running postGIS 1.0.4 on
> postgreSQL 8.0.3. running on RedHat Enterprise ES, 1.113 GHz Inter
> Pentium III, 376 Megs of RAM
> 
> I am performing an intersection calculation on two spatial tables, the
> tables are as follows:
> 
> bmp_multiseg : 6579 records
> p5lrsegs : 2364 records
> 
> QUERY:
>    select a.msid, b.lrseg, b.landseg, b.riverseg,   
>    area2d(intersection(a.the_geom, b.the_geom))/area2d(b.the_geom) from 
>    bmp_multiseg as a, p5lrsegs as b where a.the_geom && b.the_geom;
> 
> basically, there are several inputs in bmp_multiseg at a number of
> overlapping resolutions that I am distributing amongst the second table
> p5lrsegs, with an average of ~ 6 overlaps from bmp_multiseg to p5lrsegs,
> making a total of ~ 15,000 records in the resulting query. This query
> takes over an hour to complete with spatial indices on both tables
> geometry field.
> 
> The geometries are not simple, they range in size from between 5-20K per
> row, perhaps this is part of the problem?
> 
> Any insight would be appreciated. Even if you tell me that this is
> reasonable and I have to just suck it up, or look for another tool to do
> my calculations, those kinds of insights are good to know.

Well, one thing that might help would be to add an intersects() to your 
where clause.  If you shapes are very irregular, your && will cause many 
false positives causing too many empty intersection shapes to be created.  
(Someone correct me if I'm wrong).

So you might try:
 QUERY:
    select a.msid, b.lrseg, b.landseg, b.riverseg,   
    area2d(intersection(a.the_geom, b.the_geom))/area2d(b.the_geom) from 
    bmp_multiseg as a, p5lrsegs as b where a.the_geom && b.the_geom AND 
intersects(a, b);

Another thing (obviously) is to make sure that your indexes are being 
used.  Run a EXPLAIN  on that query and post the results.

Bill