pgr_withpoints() How to select edges geometry till points?

Question

I have two tables, table poi consist of points with point id pid, nearby/associated edge_id,fraction and point geometry the_geom. And edge table ways which has all the fields required(source, target, cost, reverse_cost, the_geom) for pgRouting.

While trying to get route from poi to poi using pgr_withpoints(), I am bit stuck with getting geometries till the poi. With the query I will get the exact cost till the poi, but I am not able to get the geometries?

select * from pgr_withpoints
('select id, source, target, cost, reverse_cost from ways ORDER BY id',
'select pid, edge_id, fraction, side from poi', -101, -121);

Now to draw lines I can join the pgr results with ways table.

select seq,path_seq,node,edge,pgr.cost,agg_cost,st_astext(the_geom) as geom 
from pgr_withpoints('select id, source, target, cost, reverse_cost 
from ways ORDER BY id', 'select pid, edge_id, fraction, side 
from poi', -101, -121) pgr join ways w on pgr.edge = w.id ORDER BY pgr.path_seq;

But since ways are linestring geometry, route draws till end of the edge. How can I select geometries till poi with pgr_withpoints() ?

Answer 1

You'll need to replace the last linestring with a linestring that is split at your point.

There are a couple of ways to do this, but I'd suggest the easiest is to split the_geom at your end point using ST_Split(lines.the_geom, ST_ClosestPoint(lines.the_geom, poi.the_geom)). You could add an outer SELECT to your query above using CASE WHEN to split the linestring when edge is the last edge, and return the unsplit linestring otherwise.