Thursday, 22 February 2018

postgis - Features merging


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.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...