[postgis-users] Overlap Queries - speed

[Mark Cave-Ayland]

> -----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