query to get predicted location after traveling on pgrouting route for some hours, postgis 2, postgresql 9.3

Question

I am using Postgis 2.0 on Postgresql 9.3 on Ubuntu 14.04 x64. I am trying to find an efficient way to fulfill this routing request. So I am able to get the shortest route using the source and target locations. However once I have that route, I would like to know where a car or person will be x hours after starting from the source location. I know that the cost function has the travel time in hours, so I should be able to find the longitude and latitude location after traveling for some number of hours. However, I am not familiar enough with pgrouting to know the most efficient way to run this kind of query. Any suggestions?

Answer 1

In this case clause with and window functions will be helpfull. In clause with you have to create table with counted total distance in each edge, and from this table select first record with total cost > desired.

Example query:

with total_cnt as
   (
   SELECT *, sum(rte.cost) over (order by seq) as total
   FROM edge_table pgr, (SELECT * FROM pgr_astar('select * from edge_table', :start, :stop, true, true)) AS rte  
   WHERE pgr.id = rte.id2
   )
select * from total_cnt where total > :desired_total limit 1