How to update geometry column with merged linestrings based on an array of their id inPostgreSQL?-CodePudding

I have two data tables. One is containing linestring geometries with their id-s. The other contains array of line id-s from the first table. In some cases it there is just one id in the array, in other cases there are multiple, but it can be basically any number. One thing is for sure, the linestrings in the array are connecting at their endpoints.

If there are more than one id in the array attribute, I would like to merge the linestrings based on their id-s. In case of there is just one id, than I would like to copy the original geometry.

Here are two example tables. ( I added a geom_astext column with WKT geometry for better understanding.)

CREATE TABLE me.test_lines
(gid serial PRIMARY KEY,
the_geom geometry(LineString,4326));

INSERT INTO me.test_lines (the_geom)
VALUES 
(ST_MakeLine(ST_Point(1,2), ST_Point(3,4))),
(ST_MakeLine(ST_Point(0,0), ST_Point(2,2))),
(ST_MakeLine(ST_Point(2,2), ST_Point(2,4))),
(ST_MakeLine(ST_Point(2,4), ST_Point(3,4))),
(ST_MakeLine(ST_Point(5,5), ST_Point(6,6))),
(ST_MakeLine(ST_Point(6,2), ST_Point(4,0)));

CREATE TABLE me.test_lines_merged
(id serial PRIMARY KEY,
 gid_array int[],
the_geom geometry(LineString,4326))
geom_astext text;

INSERT INTO me.test_lines_merged (gid_array)
VALUES 
('{1}'),
('{2,3,4}'),
('{5}'),
('{6}');

me.test_lines:

gid geom_astext
1   "LINESTRING(1 2,3 4)"
2   "LINESTRING(0 0,2 2)"
3   "LINESTRING(2 2,2 4)"
4   "LINESTRING(2 4,3 4)"
5   "LINESTRING(5 5,6 6)"
6   "LINESTRING(6 2,4 0)"

me.test_lines_merged

id  gid_array   geom_astext
1   {1} 
2   {2,3,4} 
3   {5} 
4   {6}

What I would like to have is this:

id  gid_array geom_astext
1   {1}       "LINESTRING(1 2,3 4)"
2   {2,3,4}   "LINESTRING(0 0,2 2,2 4,3 4)"
3   {5}       "LINESTRING(5 5,6 6)"
4   {6}       "LINESTRING(6 2,4 0)"

I know how I can update the geometry where there is only 1 element of the array:

UPDATE me.test_lines_merged a
SET the_geom = b.the_geom,
geom_astext = ST_AsText(b.the_geom)
FROM me.test_lines b
WHERE a.gid_array[1] = b.gid
AND array_length(a.gid_array,1) = 1

I also have an idea about how to merge the lines, but I can only make it just for one line:

UPDATE me.test_lines_merged 
SET the_geom = b.the_geom,
geom_astext = (SELECT ST_AsText(ST_MakeLine(the_geom)) 
FROM me.test_lines b 
WHERE gid in 
    (SELECT UNNEST(gid_array) FROM me.test_lines_merged WHERE id = 2))

CodePudding user response：

To get the desired line, you must join the two tables based on the ID occurence in the target array (ANY()). You must then group by target ID and union the segments, which builds a multi-linestring, and then apply st_line_merge to "stitch" the segments into a simple line.

SELECT tlm.id, st_asText(ST_LineMerge(st_union(tl.the_geom)))
FROM  test_lines_merged tlm
 JOIN test_lines tl
  ON tl.gid = ANY(tlm.gid_array)
GROUP BY tlm.id;

 id |          st_astext
---- -----------------------------
  1 | LINESTRING(1 2,3 4)
  2 | LINESTRING(0 0,2 2,2 4,3 4)
  3 | LINESTRING(5 5,6 6)
  4 | LINESTRING(6 2,4 0)

From there, you can use this query in an update statement, joining the target table to this subquery:

UPDATE test_lines_merged
SET the_geom = sub.merged_geom,
     geom_astext = st_asText(sub.merged_geom)
FROM (
 SELECT tlm.id, 
        ST_LineMerge(st_union(tl.the_geom)) AS merged_geom
 FROM  test_lines_merged tlm
   JOIN test_lines tl
   ON tl.gid = ANY(tlm.gid_array)
 GROUP BY tlm.id
) AS sub
WHERE test_lines_merged.id = sub.id;