[postgis-users] replicating features on self-join

William Kyngesburye woklist at kyngchaos.com
Tue Sep 25 10:48:36 PDT 2012

I'm trying to do a series of joins to classify some lines based on other lines they are touching in the same table and whether they are in polygons in another table, and lines are replicating.

in a simplified form, it's something like (I imported with GDAL, so all my geometry fields are wkb_geometry):

select a.wkb_geometry, a.id, a.name, a.class,
    when a.class = 'a' and b.class is not NULL and (d1.newclass = 'b' or d2.newclass = 'b') then 'b'
    else a.class
  as newclass
from source as a left outer join polys as b st_coveredby(a.wkb_geometry, b.wkb_geometry)
left outer join source as c1 on st_touches(st_startpoint(a.wkb_geometry), c1.wkb_geometry)
left outer join source as c2 on st_touches(st_endpoint(a.wkb_geometry), c2.wkb_geometry)

All lines are replicated 4-9 times (maybe more that I didn't notice), each duplicate with different newclass values though some newclass values duplicated also (I suppose dependent on which join it came from).

How can I stop the replication?  Probably some SQL thing, the different join types still confuse me some.

Adding a DISTINCT on the id to the select (and a sort to make that work) adds a LOT to the processing time processing time.

William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>

"Oh, look, I seem to have fallen down a deep, dark hole.  Now what does that remind me of?  Ah, yes - life."

- Marvin

More information about the postgis-users mailing list