Combining the output of ST_Collect/ST_Union to create linestring from multilinestring?

Question

I am using pgr_dijkstra() to compute the shortest path and then using ST_Collect (or ST_Union) to get the shortest path geometry. However, the collection returned from the ST_Collect is a multi-linestring and not amenable to my further analysis which includes using functions like ST_LineLocatePoint etc. which need a linestring as an argument.

I am having to use ST_Collect() instead of ST_MakeLine() directly on the pgr_dijkstra output as ST_MakeLine ends up connecting the sub-geometries out of order resulting in a garbled line (described here).

Function code:

CREATE OR REPLACE FUNCTION public.sp_od(
    orig integer,
    dest integer)
    RETURNS TABLE(shortest_path geometry) 
LANGUAGE 'sql'

AS $BODY$   

SELECT st_makeline(geom) as shortest_path
FROM pgr_dijkstra(
    'SELECT id, source, target, st_length(geom, true) as cost FROM public."WA_roads"',
(SELECT source FROM public."WA_roads"
 ORDER BY ST_StartPoint(geom) <->
     (select ST_SetSRID(ST_MakePoint(CAST(ocentx as double precision), CAST(ocenty as double precision)), 4326) from all_trips_non_zero where origin = orig LIMIT 1) ASC
LIMIT 1),
(SELECT source FROM public."WA_roads"
 ORDER BY ST_StartPoint(geom) <->
     (select ST_SetSRID(ST_MakePoint(CAST(dcentx as double precision), CAST(dcenty as double precision)), 4326) from all_trips_non_zero where destination = dest LIMIT 1) ASC
LIMIT 1), directed := false
) as pt
JOIN public."WA_roads" rd ON pt.edge = rd.id;

$BODY$;

I tried using ST_SnapToGrid() and ST_LineMerge(), still the resulting output from ST_Collect is a multilinestring.

I seek suggestions to either get the output from pgr_dijkstra() in the right order or for converting the ST_Collect() multilinestring to linestring.

Answer 1

ST_LineMerge can now merge lines that have different orientations, using the optional directed parameter.

Answer 2

This is not the answer to your question but I cannot comment right now. what I think the problem is there in your network data geometry. They are not snapped to each other properly. So that's why when you use st_linemerge it is still giving you multilinestring Try ST_NumGeometries on your multilinestring output and you will get the number of geom in your ouput.You can also use ST_GeometryN to select a part of your multilinestring and see where there is error