Wrong order of elements after GROUP BY using ST_MakeLine

Question

I have a table (well, it's CTE) containing path, as array of node IDs, and table of nodes with their geometries. I am trying to SELECT paths with their start and end nodes, and geometries, like this:

SELECT *
FROM (
    SELECT t.path_id, t.segment_num, t.start_node, t.end_node, ST_MakeLine(n.geom) AS geom
      FROM (SELECT path_id, segment_num, nodes[1] AS start_node, nodes[array_upper(nodes,1)] AS end_node, unnest(nodes) AS node_id
        FROM paths            
      ) t
      JOIN nodes n ON n.id = t.node_id
      GROUP BY path_id, segment_num, start_node, end_node
    ) rs

This seems to be working just fine when I try it on individual path samples, but when I run this on large dataset, small number of resulting geometries are bad - clearly the ST_MakeLine received points in wrong order. I suspect parallel aggregation resulting in wrong order, but maybe I am missing something else here?

How can I ensure correct order of points into ST_MakeLine?

If I am correct about the parallel aggregation, postgres docs are saying that Scans of common table expressions (CTEs) are always parallel restricted, but does that mean I have to make CTE with unnested array and mark it AS MATERIALIZED so it does not get optimized back into query?

Answer 1

Thanks for reminding me of ST_MakeLine(geom ORDER BY something) possibility, ST_MakeLine is aggregate function after all. I dont have any explicit ordering column available (order is position in nodes array, but one node can be present multiple times). Fortunately, unnest can be used in FROM clause with WITH ORDINALITY and therefore create an ordering column for me. Working solution:

SELECT *
FROM (SELECT t.path_id, t.segment_num, t.start_node, t.end_node, ST_MakeLine(n.geom ORDER BY node_order) AS geom
      FROM (SELECT path_id, segment_num, nodes[1] AS start_node, nodes[array_upper(nodes,1)] AS end_node, a.elem AS node_id, a.nr AS node_order
        FROM paths, unnest(nodes) WITH ORDINALITY a(elem, nr)
      ) t
      JOIN nodes n ON n.id = t.node_id
      GROUP BY path_id, segment_num, start_node, end_node
    ) rs