[postgis-users] Overlap Queries - speed

[challu at fas.harvard.edu]

>From what I gathered from the documentation (postgis.pdf), && tests overlapping
of bounding boxes. If my understanding is correct, then, it doesn't matter the
actual shape of the forms. The only important thing is to add bounding boxes in
the fields. That was in fact my experience: a similar query (although perhaps
with fewer cases) took too long to perform. I didn't matter the two geometry
fields were indexed. What made the difference was redoing the geometry field
with a bounding box. After that the query took a couple of seconds, not 10
minutes and it sped up considerably other spatial operations such as
contains().
I hope this helps...
Amilcar


Quoting Robert Burgholzer <rburghol at chesapeakebay.net>:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>