1

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.

  • 1
    You're looking to UN-combine the multipart features, so you want to research using ST_Dump. – Vince Sep 14 '19 at 10:45
  • ST_Dump takes a step back to individual geometries that I combined using ST_Collect. I need a single line (shortest path) that I want to perform operations. – Chintan Pathak Sep 14 '19 at 17:13
  • ST_Collect does not do anything other than combine the geometries into an array. There is no inherent ordering, and no geometric unioning. pgr_dijkstra will return node pointers in the correct order. Please post some code, because what you are saying makes absolutely no sense at all. – John Powell Sep 15 '19 at 17:18
  • @JohnPowell: Edited the question with code for the function used, also see linked question: gis.stackexchange.com/questions/334302/… for detailed issue. – Chintan Pathak Sep 15 '19 at 19:09
  • @JohnPowell: The issue I am facing seems to be as prophecized here: gis.stackexchange.com/a/96090/18956 . The directions of the individual segments are not the same as the traversed path. – Chintan Pathak Sep 16 '19 at 8:36
0

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

|improve this answer|||||

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.