2

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 :

enter image description here

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 :

enter image description here

but this is what I' getting with my query :

enter image description here

1 Answer 1

3

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.

3
  • Thank you for your answer. I tried your solution but it didn't return the results I'm expecting. I tired it on a test data kind of a sample from where I expect having one line merged but it gave me 4, which means that there is some duplicated geomtries. Oct 2, 2017 at 16:14
  • it should only be able to return one record for each material (it actually returns a union of ALL pipes that intersect with others of the same material, so if you have different clusters of connecting pipes with the same material, it will union them into a large multiline)
    – geozelot
    Oct 2, 2017 at 16:14
  • Thanks your query works fine. But I want it not to merge the pipes which have the same material and don't touche each other. The merge should be done where the both condition are true at the same time. Thanks again for your precious help Oct 3, 2017 at 11:29

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.