1

I have a layer of pipes,water conductors, that I merged under the condition of having the same material and they're touching each other, it did a good work but some features which have the same material but not touching each other are also merged. the code below shows the query I executed to merge the features :

drop table if exists touches_material;
CREATE TABLE touches_material AS
SELECT ROW_NUMBER() OVER() AS id,
       sub_query.*
FROM (
    SELECT array_agg(DISTINCT a.id) AS old_id, a.material,
           ST_Union(a.geom) AS fusion
    FROM pipe AS a,
         pipe AS b
    WHERE a.id <> b.id
      AND a.material = b.material
      AND ST_touches(a.geom, b.geom)
    GROUP BY a.material
) AS sub_query

So I was thinking about finding a function that clears the merge when the touch is not realized and the pipes have the same material. How can I achieve this goal?

3

1 Answer 1

2

Since I just learned about ST_ClusterIntersecting from this answer, I'll provide a way better solution than the query above (that I wrote as a solution to your initial question...). Try this (don´t forget to fill in <YOUR_SRID>):

SELECT array_agg(pipe.id) AS old_id,
       cluster.material,
       cluster.geom
FROM (
  SELECT material,
         ST_SetSRID(
           ST_CollectionHomogenize(
             unnest( 
               ST_ClusterIntersecting(
                 pipe.geom
               )
             )
           ),
           <YOUR_SRID>
         ) AS geom
  FROM pipe
  GROUP BY pipe.material
) AS cluster,
  pipe
WHERE ST_Intersects(pipe.geom, cluster.geom)
  AND pipe.material = cluster.material
GROUP BY cluster.material,
         cluster.geom

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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