3

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: enter image description here

Order at which the multilinestring is being generated:

enter image description here

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)

3

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))
  • Oh! I tried using 'order by' after the 'from' clause, to no avail. Never thought of using it inside the function! Thanks a lot! – Roberto Ribeiro Oct 20 '15 at 21:45
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.