10

I have a big PostGIS table storing line features.

I would like to merge features that are both connected to each other and have given attribute values identical. Here is below an example of what I'd like to achieve.

  • The outmost features (black and blue ones) are not merged because there are not contiguous even if their attribute values are the same
  • Features green and red are merged because they match both conditions
  • Feature yellow is left as before.

How would you achieve that with a SQL query? enter image description here

dericke
  • 992
  • 7
  • 18
wiltomap
  • 2,690
  • 2
  • 22
  • 52

1 Answers1

7

You can do this with ST_ClusterIntersecting:

SELECT attr, unnest(ST_ClusterIntersecting(geom))
FROM lines
GROUP by attr;
dbaston
  • 13,048
  • 3
  • 49
  • 81
  • Thanks @dbaston! I'll try it in the coming days and let you know. – wiltomap Apr 12 '16 at 14:59
  • The function ST_ClusterIntersecting() is available from PostGIS 2.2... Is there a solution with an older version? I'm on PostGIS 2.1.8 and I can't upgrade PostGIS for the moment. – wiltomap Apr 14 '16 at 08:09
  • Nothing that I'd consider good....it's actually this situation that motivated me to write ST_ClusterIntersecting. Some pre-2.2 ideas are at http://gis.stackexchange.com/q/94203/18189 – dbaston Apr 14 '16 at 13:14