[postgis-users] noding problem

[Chris Hermansen]

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


-- 
Regards,

Chris Hermansen · mailto:clh at timberline.ca
tel:+1.604.714.2878 · fax:+1.604.733.0631
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5

C'est ma façon de parler.