[postgis-users] Overlap Queries - speed

[strk at refractions.net]

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