PostGIS - Merging linestrings into multilinestrings in a particular order

Question

I have a set of linestrings, which I want to group into a single multilinestring, and in a specific order. However, when I use ST_Collect, it merges them out of order, like this:

Geometries as linestrings:

Order at which the multilinestring is being generated:

I know this "order" is only in my head really, and the function will use some parameter to determine the order. A quick research has given me that ST_Collect does its magic ordering by the row id, but for reasons I won't bother going through here I can't reorder my ids in the "aggregating order".

I do, however, have a separate table with the correct order, that's referencing the geom ids, so a simple join would suffice to put them in order, but ST_Collect is ignoring that. Is there a way to aggregate linestrings into a multilinestring using the ordering from this second table?

(the drawings are merely illustrative, but depict a real case, that is, some of the lines are not connected, and must remaind so. Therefore, I can't use ST_LineMerge to "force" a correct order out of it)

Answer 1

Postgres lets you control the order in which rows are fed into an aggregate function, using an ORDER BY construct within the aggregate's parameters (see docs). For example (it looks a bit odd):

CREATE TEMPORARY TABLE lines (geom geometry);
INSERT INTO lines VALUES 
  ('LINESTRING (0 0, 1 1)'), 
  ('LINESTRING(1 1, 2 2)'),
  ('LINESTRING(0 -1, 2 7)');

SELECT ST_AsText(ST_Collect(geom ORDER BY ST_Length(geom) DESC)) FROM lines;
-- MULTILINESTRING((0 -1,2 7),(0 0,1 1),(1 1,2 2))

SELECT ST_AsText(ST_Collect(geom ORDER BY ST_YMin(geom) DESC)) FROM lines;
-- MULTILINESTRING((1 1,2 2),(0 0,1 1),(0 -1,2 7))

Answer 2

Without knowing anything about your schema and what you have tried so far, this may help you progress a bit

This little test shows that you can influence the order in which ST_Collect puts together the lines. So you should be able to do what you want.

CREATE TABLE Lines (ID INT NOT NULL, SEQ INT, Geom Geometry);
INSERT INTO Lines
VALUES
(1,2,ST_GeomFromText('LINESTRING(0 0, 10 0)')),
(2,1,ST_GeomFromText('LINESTRING(15 0, 15 5)')),
(3,4,ST_GeomFromText('LINESTRING(15 5, 10 10)')),
(4,3,ST_GeomFromText('LINESTRING(5 5, 0 15)'));

SELECT ST_AsText((ST_Dump(C)).geom)
FROM (
    SELECT ST_Collect(Geom) C FROM LINES  A
    )C;

SELECT ST_AsText((ST_Dump(C)).geom)
FROM (
    SELECT ST_Collect(Geom) C FROM (SELECT * FROM LINES ORDER BY SEQ) A
    )C;

SELECT ST_AsText((ST_Dump(C)).geom)
FROM (
    SELECT ST_Collect(Geom) C FROM (SELECT * FROM LINES ORDER BY SEQ DESC) A
    )C;