[postgis-users] noding problem

[Kevin Neufeld]

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