Skip to main content
added 317 characters in body
Source Link
Jendrusk
  • 2.6k
  • 10
  • 16

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

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

Source Link
Jendrusk
  • 2.6k
  • 10
  • 16

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);