[GIS] 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))