2

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() ?

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.

  • right now I am doing the same. Splitting start and end node linestring and attaching second and first half from first and slpit line to start and end of linestring. I was expecting a single query for this. And pgr_withpoints() is pretty slow compared to pgr_dijkstra(). It will take around 7-8 seconds to compute. Any pointer to improve performance? – Aparichith Oct 30 '17 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.