[postgis-users] Overlap Queries - speed

[rb]

Sorry to be an idiot. I belive I have found the answer to the adding of
a boundary box by reading the friendly manual.


On Mon, 2005-10-03 at 19:59, challu at fas.harvard.edu wrote:
> >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
> >
> 
> 
> 
> 
> _______________________________________________
> 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