[postgis-users] Overlap Queries - speed

[Bill Binko]

Fair enough: you can replace my intersects() with distance(a, b) <= 0

I can't keep track of all the tricks, I just realized that his && was not 
going to be sufficient.

Bill
On Mon, 3 Oct 2005, Obe, Regina     DND\MIS wrote:

> Yes I thought so too and I thought in general that the GEOS functions were a
> little slower than the non-geos ones or has that changed?  Given that
> intersects is a GEOS function I would expect it to be slower.  I haven't
> tested to verify those assumptions though.
> 
>  Another check?  How much memory do you have allocated for worker_mem?  It
> doesn't sound like you have all that much memory to work with, but boosting
> that may help a little since you are working with fairly large geometries.
> 
> -----Original Message-----
> From: Ethan Alpert [mailto:ealpert at digitalglobe.com] 
> Sent: Monday, October 03, 2005 5:12 PM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] Overlap Queries - speed
> 
> 
> 
> 
> Bill I thought it was discovered that distance() was faster than
> intersects()
> 
> -e
> 
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bill
> Binko
> Sent: Monday, October 03, 2005 2:28 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Overlap Queries - speed
> 
> 
> On Mon, 3 Oct 2005, Robert Burgholzer wrote:
> 
> > 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.
> 
> Well, one thing that might help would be to add an intersects() to your 
> where clause.  If you shapes are very irregular, your && will cause many
> 
> false positives causing too many empty intersection shapes to be created.  
> (Someone correct me if I'm wrong).
> 
> So you might try:
>  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 AND 
> intersects(a, b);
> 
> Another thing (obviously) is to make sure that your indexes are being 
> used.  Run a EXPLAIN  on that query and post the results.
> 
> Bill
>  
> 
> _______________________________________________
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>