Return to Revisions

I had the same problem few weeks ago and solved it by manually drawing lines from nearest node. I don't know if it's one and only resolution, it's quite hard and tricky but its working quite good.

Firstly I'm selecting start edge, stop edge and result (path from routing) into record variables, then doing some post-procesing:

1. Cut the result road if it's near start and end point

    if st_within(start_edge.geom_way, result.geom) then
      result.geom = ST_Difference(result.geom, start_edge.geom_way);
      result.km = result.km - start_edge.km;
      result.cost = result.cost - start_edge.cost;
    end if;
   
    if st_within(stop_edge.geom_way, result.geom) then
      result.geom = ST_Difference(result.geom, stop_edge.geom_way);
      result.km = result.km - stop_edge.km;
      result.cost = result.cost - stop_edge.cost;
    end if;
   

2. Draw missing pieces by splitting start_edge and stop_edge. Unfortunately PostGIS is having troubles with splitting line by a point (case of rounding), so you have to draw the line from starting point intersecting cutting edge (I'm drawing line from point of start to point that is in direction of nearest point on line and 2xdistance)

    select foo.geom 
    from (
    select (st_dump(
       st_split(start_edge.geom_way, 
       st_makeline(ST_setsrid(ST_makepoint(start_x_4326,start_y_4326),4326), 
       (st_project(
         ST_setsrid(ST_makepoint(start_x_4326,start_y_4326),4326)::geography, 
         ST_distance(ST_setsrid(ST_makepoint(start_x_4326,start_y_4326),4326)::geography,st_closestpoint(start_edge.geom_way,ST_setsrid(ST_makepoint(start_x_4326,start_y_4326),4326)))*2, 
         ST_azimuth(ST_setsrid(ST_makepoint(start_x_4326,start_y_4326),4326)::geography,st_closestpoint(start_edge.geom_way,ST_setsrid(ST_makepoint(start_x_4326,start_y_4326),4326)))))::geometry)))).geom as geom) as foo 
    where st_touches(foo.geom, result.geom) 
    into add_start_edge;
   
   
    select foo.geom 
    from (
      select (st_dump(
       st_split(stop_edge.geom_way, 
         st_makeline(ST_setsrid(ST_makepoint(stop_x_4326,stop_y_4326),4326), 
       (st_project(
         ST_setsrid(ST_makepoint(stop_x_4326,stop_y_4326),4326)::geography, 
         ST_distance(ST_setsrid(ST_makepoint(stop_x_4326,stop_y_4326),4326)::geography,st_closestpoint(stop_edge.geom_way,ST_setsrid(ST_makepoint(stop_x_4326,stop_y_4326),4326)))*2, 
         ST_azimuth(ST_setsrid(ST_makepoint(stop_x_4326,stop_y_4326),4326)::geography,st_closestpoint(stop_edge.geom_way,ST_setsrid(ST_makepoint(stop_x_4326,stop_y_4326),4326)))))::geometry)))).geom as geom) as foo 
      where st_touches(foo.geom, result.geom) 
    into add_stop_edge;
   

3. Now all you have to do is add this lines to result

    result.km = result.km + (start_edge.km * (ST_Length(add_start_edge) / ST_Length(start_edge.geom_way)));
    result.cost = result.cost + (start_edge.cost * (ST_Length(add_start_edge) / ST_Length(start_edge.geom_way)));
    result.geom = ST_Union(result.geom, add_start_edge);
   
    result.km = result.km + (stop_edge.km * (ST_Length(add_stop_edge) / ST_Length(stop_edge.geom_way)));
    result.cost = result.cost + (stop_edge.cost * (ST_Length(add_stop_edge) / ST_Length(stop_edge.geom_way)));
    result.geom = ST_Union(result.geom, add_stop_edge);
   

4. And return result from procedure

    return query select result.km, result.cost, ST_Transform(result.geom, epsg);
   

PS. Keep in mind that if closest edge of your starting or target point is one-way not always the closest vertex of an edge is good point to paste into routing query. In your example if Riberio would be shortest your routing will end on Real-Riberio crossing and then you have to drive counter the road direction... :)