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


You can do this with ST_ClusterIntersecting:

SELECT attr, unnest(ST_ClusterIntersecting(geom))
FROM lines
GROUP by attr;
  • 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 8: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 gis.stackexchange.com/q/94203/18189
    – dbaston
    Apr 14 '16 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.