PostGIS – Union Multiple LineStrings and Convert MultiLineString to LineString

postgispostgresql

I have three LineStrings which I want to ST_Union() by a column id to become one LineString.

SELECT id, ST_Union(geom) geom
FROM lines
GROUP BY id

The resulting geom however, is of type MultiLineString. The problem is, the three LineStrings are connected at only one point, which, to my understanding prohibits the conversion to a simple LineString.

Q1: Am I right about what causes the problem?

Q2: What would be the best strategy to convert the result to a simple LineString?

Thoughts:

I guess the best thing would be to move one LineString a little up or down from the intersection, though I don't know how.
I'm aware of this question which suggests ST_LineMerge() and ST_SnapToGrid(), however this doesn't help here. I have added an order column ord, which might be of use.

Data:

DROP TABLE IF EXISTS lines;
CREATE TABLE lines (
    id text,
    ord integer,
    geom geometry(linestring)
);

INSERT INTO lines (id, ord, geom)
    VALUES ('A', 2, 'LINESTRING(16.280400356066927 47.216336860661826,16.290056688442558 47.208554734195836,16.294002182428958 47.20832563395537,16.296415675891705 47.20637988119092,16.29904588434292 47.206227010472816,16.302665643639184 47.203308193909514,16.30255714413932 47.202411729058845,16.30376362613302 47.20143876421667,16.30507859935233 47.20136224888468,16.307491381368997 47.19941625001404,16.308806299392447 47.199339689963836,16.312425069238106 47.19642054751458,16.313739906218256 47.19634392835274,16.316152150699274 47.19439773730669,16.31746693248523 47.19432107343637,16.318890462556478 47.19514084338737,16.320205259683807 47.19506414616617,16.321411256830896 47.19409098633403,16.32283484342812 47.19491070397743,16.32414963004369 47.194833959010055,16.325355549643387 47.193860755996,16.32798506065721 47.19370718952946)');
INSERT INTO lines (id, ord, geom)
    VALUES ('A', 1, 'LINESTRING(16.195963964551947 47.26362244867695,16.19959318295299 47.26070706468893,16.199272462830137 47.25801739154895,16.20169161749477 47.25607374016665,16.201477756806256 47.25428062559339,16.2026872333405 47.2533087814729,16.202580292361564 47.252412224256794,16.20378970761015 47.25144036754284,16.203682753641726 47.25054381068199,16.204892107611705 47.24957194137545,16.204785140656753 47.24867538486973,16.2059944333552 47.2477035029715,16.207310684841566 47.24762816221188,16.207203682487236 47.246731607557074,16.21083116851101 47.24381584022403,16.21477960221936 47.24358959685534,16.2184064634655 47.24067357796471,16.21829929245872 47.239777032788496,16.21950814143081 47.23880500039948,16.220824153228868 47.23872949782424,16.22324166882937 47.23678536365338,16.224557625447424 47.23670981627613,16.22576629208277 47.23573771449803,16.23102998051405 47.23543530793056,16.23223850017395 47.23446313492139,16.233554387766333 47.23438747901777,16.237179608015023 47.231470835696115,16.239811217641673 47.23131938952016,16.241234553619933 47.232140165273044,16.246497806979995 47.23183701201528,16.248914076132287 47.22989231180807,16.25417706705843 47.22958878826417,16.2553850329154 47.22861636022656,16.256700746099956 47.228540425093776,16.25790864258901 47.227567969144495,16.26185568369637 47.227340024907704,16.263063459092557 47.22636751220824,16.262955565828495 47.22547101220386,16.2642711909928 47.22539498602308,16.269101683686557 47.22150474644033,16.275679240050074 47.221124088281584,16.276886625201996 47.2201514226022,16.279517571638966 47.2199990190283,16.280724861589306 47.2190263110439,16.280400356066927 47.216336860661826)');
INSERT INTO lines (id, ord, geom)
    VALUES ('A', 1, 'LINESTRING(16.26944533923136 47.2133601815456,16.273715381676944 47.215821307383926,16.2763461182927 47.21566898262223,16.27776959560581 47.21648928399112,16.280400356066927 47.216336860661826)');
--

Best Answer

I have completely overlooked that a LineString is not allowed to branch off. Hence, all clear now.

Related Question