[postgis-users] Overlap Queries - speed

[rb]

OK,
By skipping the geoprocessing portions of the query (intersection and
area2d) I trim execution time to approximately 2 minutes, about a 30
fold speed improvement. So the upshot is, the existing bbox shema was
working well, but I am running into the performance hit that is
engendered by calling the GEOS functions.

Running this query using only area2d calls puts the time up slightly,
about an additional 15-20 seconds, so it seems that the intersection
call is the real heavy hitter.

I previously used the scot_gpc implementations, Nicolas Ribot put this
together a while back. I am thinking to try using the intersection
routine from there to se if it will improve performance. Any suggestions
on compiling these with todays version of postgis Nicolas?

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