Interesting - I was expecting st_distance() to perform a bit better than intersects (but then on second thinking I realized maybe not).
It seems to me that all st_intersects has to do is find one point shared by the 2 geometries and it can declare victory plus I imagine that there are certain higher rules of topology it has at its disposal that make the order of searching of points a bit more efficient than st_distance. On the downside there is also the issue that st_intersects will throw an error if you feed it some invalid geometries whereas st_distance will not. st_distance except in the special case (where distance is 0) has to basically verify that there is no point that has a distance lower than the last lowest it checked. I'm sure there are some short-cuts here without going deep into topology land, but I suspect fewer short-cuts and that is the cause for its poorer perfomance when dealing with more complex geometries. Am I safe in saying that distance treats the case of distance() == 0 as a special case - e.g. you can't get lower than 0 for distance so there is no point in checking any further? Thanks, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Neufeld Sent: Wednesday, October 24, 2007 7:45 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] noding problem Well, there you have it. Thanx for pointing that out Chris. Goes to show that there often many ways to accomplish the same thing in PostGIS. I suppose it also really depends on the geometries (size, type, etc). For the type of queries I often perform, checking distance is sometimes faster than computing intersects(). This obviously isn't the case for you. Good work. Glad to hear you're making progress. For my own curiosity I timed a few similar queries as yours. For two sets of ~3000 polygons, using intersects() is significantly faster. For two sets of ~5000 linestrings using intersects() is 1.5 times faster. For a set of ~300000 linestrings and ~300000 points using distance()==0 is 1.5 times faster. For a set of ~17000 polygons and 33000 points using distance()==0 is 1.9 times faster. Cheers, Kevin Chris Hermansen wrote: > Kevin makes some suggestions as to why I have empty geometry collections > and what is the quickest way to get rid of them. Based on his advice, I > thought I'd report some potentially useful info back to the list. > > I have 11,228 polygons in "fc" and 4553 polygons in "results". I have a > late-model Core2 Duo based Dell running Ubuntu 7.04. This is > postgis1.3.1 and geos3.0.0RC4. > > I have three test versions of a select statement that delivers the > intersection of "fc" and "results": > > 1. create temp table step1a as > select > map,standid,opening_id,intersection(fc.the_geom,results.the_geom) > as the_geom > from fc,results > where fc.the_geom && results.the_geom; > > which takes 28.113 seconds on my machine > > 2. create temp table step1a as > select > map,standid,opening_id,intersection(fc.the_geom,results.the_geom) > as the_geom > from fc,results > where fc.the_geom && results.the_geom > and st_distance(fc.the_geom,results.the_geom) = 0.0; > > which takes 4 minutes 5.903 seconds on my machine > > 3. create temp table step1a as > select > map,standid,opening_id,intersection(fc.the_geom,results.the_geom) > as the_geom > from fc,results > where st_intersects(fc.the_geom,results.the_geom); > > which takes 26.203 seconds on my machine > > Versions 2 and 3 both eliminate the empty geometry collections. > > Kevin mentions (see below) that version 2 might be the fastest, but at > least in my case it is in fact the slowest of the bunch. > > I hope the list readers find this useful! > > Kevin, thanks a bunch for getting me on the right track!!! > > Kevin Neufeld wrote: > >> Chris Hermansen wrote: >> >>> ... >>> If I execute: >>> >>> select geometryType(st_intersection(pblk.geom,pvri.geom)) >>> from pblk,pvri >>> where pblk.geom && pvri.geom; >>> >>> I get something quite different than what I expect. I expect to get >>> polygons, and sometimes multipolygons - which I assume arise when the >>> intersection process splits up polygons into several constituent parts. >>> >>> But I also get a bunch of geometrycollections. And these >>> geometrycollections are apparently empty, for example, the first few >>> lines of output from the above select: >>> >>> numgeometries | geometrytype ---------------+-------------------- >>> | POLYGON >>> | POLYGON >>> 0 | GEOMETRYCOLLECTION >>> 0 | GEOMETRYCOLLECTION >>> | POLYGON >>> 0 | GEOMETRYCOLLECTION >>> >>> Are these supposed to be there? What conceivable purpose could they >>> serve? >>> >>> >>> >> Hi Chris, >> >> Consider the cases where geometry bounding boxes intersect but the >> geometries do not. In these cases, the intersection will be empty. >> You probably want to add an additional filter to your query. "WHERE >> pblk.geom && pvri.geom AND distance(pblk.geom, pvri.geom) = 0". (You >> could alternatively use intersects(geometry, geoemetry), but >> distance==0 is faster). >> >> Does this help? >> -- Kevin >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
