[postgis-users] Overlap Queries - speed

[rb]

OK,
So as far as I can tell the boundary boxes have already been added by
default with my tables. I had already entered the bbox commands given by
Mark, however, and the query is now running (after vacuuming full and
analyzing of course). We are at 30 minutes and counting. My guess thus
far is that if they already had their bounding boxes, I am going to be
no better off, although I certainly appreciate the efforts.

I am going to retry without the intersection and area2d components, to
see what that yields, and will report the results back to the list.

r.b.



On Tue, 2005-10-04 at 08:44, strk at refractions.net wrote:
> Note that bboxes should be already added by default.
> To find out you can use the summary(the_geom) function.
> It will tag geometries with [B] if they contain a bounding box.
> --strk;
> 
> On Tue, Oct 04, 2005 at 01:13:08PM +0100, Mark Cave-Ayland wrote:
> > 
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net 
> > > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> > > Behalf Of Robert Burgholzer
> > > Sent: 03 October 2005 21:19
> > > To: PostGIS Users Discussion
> > > Subject: [postgis-users] Overlap Queries - speed
> > > 
> > > 
> > > 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.
> > > 
> > > Thanks,
> > > r.b.
> > 
> > 
> > Hi Robert,
> > 
> > As per Amilcar's suggestion, if you would like to try explicitly adding
> > bounding boxes to your geometries in order to see if that makes a
> > difference, you should do:
> > 
> > UPDATE bmp_multiseg SET the_geom = addBBOX(the_geom);
> > UPDATE p5lrsegs SET the_geom = addBBOX(the_geom);
> > VACUUM FULL bmp_multiseg;
> > VACUUM FULL p5lrsegs;
> > VACUUM ANALYZE;
> > 
> > Then try your query again and see how long it takes in comparison.
> > 
> > 
> > Kind regards,
> > 
> > Mark.
> > 
> > ------------------------
> > WebBased Ltd
> > South West Technology Centre
> > Tamar Science Park
> > Plymouth
> > PL6 8BT 
> > 
> > T: +44 (0)1752 791021
> > F: +44 (0)1752 791023
> > W: http://www.webbased.co.uk
> > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> 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