Geographic Information Systems Asked on February 14, 2021
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.
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
Answered by Arun on February 14, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP