pgRouting: route starting from closest node along edge

Question

I am trying to find a way to route from the closest point on a linestring to my current location (lat, long). So far I am able to get the shortest path but it starts from the very beginning of the linestring (aka source). I am using prg_trsp http://docs.pgrouting.org/2.0/en/src/trsp/doc/index.html because it has a feature to specify the starting position along the linestring. I am able to correctly calculate the distance along the linestring and pass the values to the function but cannot figure out how to use the results from the function (pgr_costResult[]) to specify where the route should start (partially along the closest linestring).

I have a feeling I am doing something wrong with the join when I go to join the results from the routing algorithm to my edge table to get the geometry because when I join it uses the edge table's full geometry and not segments. Although, looking at the documentation, I don't see where you get a returned segment from the routing function.

Below is a screenshot of what I am trying to do (red line) and what I have (blue line) the point is the current location. The red line comes from using the pgrouting plugin in qgis with the trsp(edge) selection.

See code below:

Any help would be much appreciated!

SELECT st_linemerge(edgeTable.geom_way) FROM pgr_trsp('SELECT id, source, target, cost FROM edgeTable', 
    (SELECT id FROM origin),
    (SELECT * FROM sourcePos),
    (SELECT id FROM destination),
    (SELECT * FROM destPos),
    false, false) AS shortestPath
    JOIN edgeTable ON shortestPath.id2 = edgeTable.id;

origin is the id of the starting route

sourcePos is how far along the linestring to offset

destination is the id of the end linestring

destPos is the fraction of the end linestring

all as specified here: http://docs.pgrouting.org/2.0/en/src/trsp/doc/index.html

Arun · Answer 1 · 2018-09-15T04:55:52.073

Its because pgr_trsp() function doesn't give the output your are excepting. Pg_routing Plugin in QGIS does the snapping of the route generated from pgr_trsp(). So you have the output of red line snapped close to your point. So just pgr_trsp() won't give you your desired output. What you are trying to do is a bit complicated but possible. Here is how I solved this problem

    WITH 
    --Make a start point
    start_pt as (
            select st_setsrid(st_makepoint(204845.95, 2410097.47), 32643) as starting),
    --Make a End Point
    end_pt as (
            select st_setsrid(st_makepoint(204937.15, 2409430.86), 32643) as ending),
    --Select Closest source node and its geom for start point
    source_code AS (
            select source, geom from edgeTable order by st_distance(geom, (select starting from start_pt)) limit 1),
    --Select closest target node and its geom for end point
    target_code AS (
            select target, geom  from edgeTable order by st_distance(geom, (select ending from end_pt)) limit 1), 
    --Route Union from pgr_trsp()
    route as (
            SELECT ST_LineMerge(ST_union(geom)) as geom, round( CAST(float8 (st_length(ST_union(geom))/1000) as numeric), 2) as length from (
                SELECT geom FROM pgr_trsp(
                    'SELECT feat_id as id, source, target, cost_len as cost, geom FROM edgeTable',
                    (select source from source_code), (select target from target_code), false, false
                    ) as di JOIN edgeTable
                ON di.id2 = edgeTable.id) as foo)

--Finaly snap the route to precisely matach our start and end point  
select ST_Line_Substring(geom,
                            ST_LineLocatePoint(geom, (select starting from start_pt)),
                            ST_LineLocatePoint(geom, (select ending from end_pt)))
                                from route

The only issue I have is that I have to switch Starting and Ending Point in last select statement. This can be handled via writing a function. Here is my output

Hope this Help...

I'm not 100% sure, but if your source and target points happen to be the other way round, would this not mean that your final line would not include the edges that are closest to the required points? — Geoff, Apr 25 '19 at 11:27
It should still work and include the edges closest to the desired point as I am selecting them in my source_code and target_code query and merging them with my final route output — Arun, Apr 26 '19 at 12:11
Thank you. In newer PostGIS versions the function ```ST_Line_Substring``` is called ```ST_LineSubstring``` — Jonas Frei, Dec 06 '21 at 16:37

pgRouting: route starting from closest node along edge

1 Answers1