[postgis-users] Overlap Queries - speed

[Ethan Alpert]

Bill I thought it was discovered that distance() was faster than
intersects()

-e

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bill
Binko
Sent: Monday, October 03, 2005 2:28 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Overlap Queries - speed


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
 

_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users