1

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.

the figure below shows the features before the merge enter image description here

but After the merge I got this result :

enter image description here

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 :

"{"type":"MultiLineString","coordinates":[[[682821.581150485,932933.184053176],[682924.496135696,932931.700912332]],[[682924.496135696,932931.700912332],[683051.797527389,932929.241991928]],[[681203.446905443,934810.249810717],[681416.162125822,934558.859550916]],[[681416.162125822,934558.859550916],[681600.569591916,934340.92384924]],[[681416.162125822,934558.859550916],[682130.080788701,935099.070761343]],[[682957.121560403,942132.07067953],[682924.496135696,932931.700912332]]]}"
"{"type":"MultiLineString","coordinates":[[[680896.081954356,934607.2711282],[681288.16977209,934104.375883715]],[[681288.16977209,934104.375883715],[681581.439704468,933728.225318273]],[[681958.88310308,934612.237488708],[681600.569591916,934340.92384924],[681288.16977209,934104.375883715]]]}"

If you have any idea how to fix this problem tell me and it would be so great to get some help from you.

9
  • Thank you for your feedback but I don't see what you mean. Can you please be more clear. Oct 3 '17 at 15:58
  • sorry misinterpreted the question...maybe try st_intersects instead of st_touches?
    – ziggy
    Oct 3 '17 at 17:23
  • No the features that intersects don't want them to be merged. I'm treating those which are in touche. Oct 4 '17 at 7:45
  • your query looks correct to me. is there anyway you can upload the data?
    – ziggy
    Oct 5 '17 at 15:32
  • yeah it's correct but it merges those which don't touch each other because of the group by clause which is done by material. Now is there any way to separate or clear the merge when the touch is false and the pipes have same material???? Oct 5 '17 at 15:45
1

As you see the features in yellow are the ones who have the same material of construction (iron from example) but noticeably not touching.

The problem is probably with the logic of the inner join, which says that everything that touches something with the same material should be joined. So if a1 touches a2, a3 touches a4, then a{1,2,3,4} will be joined/unioned. Hence the problem you mentioned.

You probably need to be more specific and only join a1 to a2 and a3 to a4 by joining to a specific object one at a time. This can be done, e.g. using LATERAL joins along the lines of:

drop table if exists merged_line5;
CREATE TABLE merged_line5 AS
SELECT ROW_NUMBER() OVER() AS id,
       sub_query.*
FROM line AS b, LATERAL (
    SELECT array_agg(DISTINCT a.id) AS old_id,
           ST_Union(a.geom) AS fusion
    FROM line AS a
    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
;

Now if b1 b2 and b3 are all connected, and you want to ST_UNION them into one piece, you will probably need to repeat the above process a couple of times.

2
  • thanks for your answer. Your update of the query gave me 3 rows as expected yet there is no merge, just one pipe on each row. Oct 13 '17 at 8:00
  • I don't see how to execute the process more than one time Oct 13 '17 at 9:20

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.