PostGIS - Merging linestrings into multilinestrings in a particular order

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)

Postgres lets you control the order in which rows are fed into an aggregate function, using anORDER BYconstruct 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))

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;

Watch the video: Intro To PostgreSQL Databases With PgAdmin For Beginners - Full Course (October 2021).