[postgis-users] Overlap Queries - speed

[Robert Burgholzer]

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.


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