I wrote a query to merge some pipes features in condition to touch each other , using the function
st_touches() from postgis, and if they have the same material of construction. The code below describes it :
drop table if exists merged_line4; CREATE TABLE merged_line4 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 line AS a, line AS b WHERE a.id <> b.id AND ST_touches(a.geom, b.geom) = true AND a.material = b.material GROUP BY a.material ) AS sub_query
the merge is very well executed but it doesn't give me the result I want, because the query dissolves also the pipes that have the same material but not touching.
but After the merge I got this result :
As you see the features in yellow are the ones who have the same material of construction (iron from example) but noticeably not touching. So in this case those features don't have to be merged. In my query I specified in the where clause that the merge, represented here by the st_union, should be done when the conditions are filed both in the same time. here is the result I'm getting :
If you have any idea how to fix this problem tell me and it would be so great to get some help from you.