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).
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_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.