[postgis-users] Overlap Queries - speed

[rb]

Thanks for the feedback, I have tried using the intersects() clause and
it is still running (about 10 minutes going). You are right that there
were many false posiives with the other. Here is the results of the
explain :


satest=# explain 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;
                                       QUERY
PLAN                                                                                 
----------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..14498.24 rows=17870 width=28135)
   ->  Seq Scan on p5lrsegs b  (cost=0.00..98.64 rows=2364 width=11555)
   ->  Index Scan using bmm_gix on bmp_multiseg a  (cost=0.00..6.00
rows=1 width=16580)
         Index Cond: (a.the_geom && "outer".the_geom)
(4 rows)


it seems to me that the index is being used. It has been analyzed and
vacuumed about six brazillion times so far.

So, I feel like a teenager: "Is this normal??"

Thanks again,
r.b.







On Mon, 2005-10-03 at 16:27, Bill Binko wrote:
> 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
-- 
Non-point Source Data Analyst
University of Maryland, College Park
Chesapeake Bay Program Office
410 Severn Avenue, Suite 305B
Annapolis, MD, 21403
Phone: (410) 267-5779

rburghol at chesapeakebay.net