merge features n time

Question

I'm working on a project of water supply where I have to merge line features representing pipes if they have the same material of construction and if they are touching each other. The merge is done two by two what it means that some features will be duplicated in some cases like described in the figure below :

this shows exactly my issue. After the merge I will get three records but what I want is just one record which encompasses the whole pipes that fill the conditions put in the where clause :

Here is the query that helps me do the merge :

drop table if exists touches_material;
create table touches_material as 
select   distinct a.*,
st_Union(a.geom,b.geom) as fusion  from pipe a, pipe b
where  a.id < b.id and a.material = b.material  and  
st_touches(a.geom,b.geom) 
group by a.id,a.geom,b.geom

the following picture picture shows the expected result on a test data, it's realized via QGIS GIS software :

but this is what I' getting with my query :

Answer 1

It´s not as simple as it might seem. Your query is not far off, but has some substantial logical errors;

• Since you are merging different features into one, you will have to define how the former attributes (like their id) will be handled. Using them in GROUP BY clause or as a distinction will result in one record for each. One option here is to use arrays to aggregate the attributes of all merged features and create a new id column for the result set.
• Using ST_Union with two geometries will, as you said, merge them pairwise. You will need to get a collection of all intersecting geometries and use ST_Union as an aggregate on a single geometry column.
• If you want to differentiate by the material, one option is to group your selection by it.
  (e.g. GROUP BY a.material)

The following query does quite what you want by sub-selecting the union of all geometries that intersect and whose materials are equal. It also generates an array for their respective old id (array_agg(DISTINCT a.id) AS old_id), which you can copy and reuse for other attributes if necessary. The resulting features will then be given a new, unique id (ROW_NUMBER() OVER() AS id) in the outer query. Be aware however that this query will return unified (multi)geometries only if one former feature has another intersecting it (e.g. single pipes not intersecting with another of the same material will NOT be in the resulting table):

CREATE TABLE touches_material AS
    SELECT ROW_NUMBER() OVER() AS id,
           sub_query.*
    FROM (
        SELECT array_agg(DISTINCT a.id) AS old_id,
               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_Intersects(a.geom, b.geom)
        GROUP BY a.material
    ) AS sub_query

This is one possible solution that is close to your initial query, see if it helps you.