How to add offsets to the route?

Edited:

I want to ilustrate my question. Suppose that you're in "Point A" and want to go to "Point B". This points wouldn't be in "at_2po_4pgr" table cause aren't source/targets nodes. Then, I would search for the nearest node for points A and B (green points). After this, I might perform a shortest_path call using green points ids and I would obtain "orange" path. But to obtain the real path cost (distance) in first case I would have to substract "offsetA" and in second case add de "offset B". To calculate distance between red-points and green-points, I run the following query:

```SELECT * FROM st_distance( ST_GeomFromText('POINT(-3.6963314 42.3498066)',4326), ST_GeomFromText('POINT(-3.6954276 42.3479634)',4326))```.

How I would know when add or substract the offset?

Sorry for my english!

-
Welcome to gis.stackexchange. This is a Q&A site where each thread should contain exactly one question and its answers. Please open a separate thread for question #3. #2 is answered in gis.stackexchange.com/questions/33471/… – underdark Nov 14 '12 at 10:22
I have the same problem. did you find any solution? Thanks a lots – Robert Jan 24 '13 at 14:28
Please post your solution in the answer section. Then it can be upvoted. – underdark Feb 2 '13 at 10:53

I do not think that you can rely on the nearest vertex. Imagine source and target are located on the same edge close to the same vertex.

You'd rather considered three! different cases:

1. a vertex is the nearest point.
2. a form node of the edge is the right one
3. the edge-line itself is nearer. (orthogonal)
-

You can find such a function here: https://github.com/pgRouting/pgrouting-contrib/blob/master/wrapper/routing_core_smart.sql#L69

It searches for the nearest link in the network, which usually gives a better result. If you use Shooting Star you can then start routing from/to this link. For A* or Dijkstra you either select start or end point of the link, or you create a "virtual" node by splitting the link into two.

-

I'm going to explain the solution that I've found (perhaps don`t be the best).

According to the post image, let's suppose that we are in Point A and we gonna go to Point B. As I explained above this points aren`t vertex (source/targets in table generated with osm2po tool).

Due to this, we need to know the walking/driving direction. If we go from nearest vertex to Point A (point green) through orange path we would have to substract the offset between Point A and point green (nearest vertex). But if we had to go through Calle Almirante Bonifaz street, then we should add the offset to the length of this edge (from point green to intersection between Calle Almirante Bonifaz and Calle San Juan).

I run the following query to obtain the shortest path (you need the pgRouting extension explained here pgRouting - installation and requirements here installation&requirements):

``````SELECT gid, cost, st_astext(the_geom) as the_geom FROM dijkstra_sp_delta('xx_2po_4pgr', source_vertex, target_vertex, 0.1);
``````

This results in a set of edges that represents the complete route. For example, one possible output for this query might be:

Where the field gid (id in osm2po generated table) represents the edge identifier. Well, we must check the offsets at the start and at the end (Points A/B).

If we check the start offset, we must check if the first edge of the set of edges obtained in the above query is the same to the nearest path to Point A. If they match, then we will substract the offset. If they don`t match, we will add the offset. To obtain de nearest link to a point, I run the following query:

``````SELECT * FROM find_node_by_nearest_link_within_distance(point, 0.1, 'xx_2po_4pgr') as id;
``````

``````CREATE TYPE link_point AS
(id integer,
name character varying,
OWNER TO postgres;
``````

You must also modify the find_node_by_nearest_link_within_distance. Just add the last line (I only show an extract from the function):

``````-- Searching for a nearest link

FOR row in EXECUTE 'select id from find_nearest_link_within_distance('''||point||''', '||distance||', '''||tbl||''') as id'
LOOP
END LOOP;
IF row.id is null THEN
res.id = -1;
RETURN res;
END IF;
``````

Then you need to know what is the distance between point (Point A/Point B) and the nearest edge(offset). For this purpose I run this query:

``````SELECT ST_Line_Locate_Point(geom , point)as offset;
``````

Where geom is the the_geom field in osm2po generated table.

At this point, we would have the offset to add or substract.

Finally, you would need to know the edge legth to apply the value obtained in the query above and adjust the real (if you work with geometry type, you will have to normalize to meters the value obtained. Just multiply 111000 by the length obtained in the query):

``````select st_length(the_geom) from (select ST_ASTEXT(the_geom) as the_geom FROM dr_2po_4pgr WHERE id= edge_identifier)t";
``````

If we would check the end offset, then we would have to check if the last path of the set of paths obtained in the above query is the same to the nearest path to the ending point (Point B) and we would add/substract at the same way as before.

Excuse my english.

-

In pgrouting, pgr_trsp - Turn Restriction Shortest Path (TRSP) does exactly what you are looking for.

Instead of specifying source and target nodes, you can specify source and target edges, and the fraction along the edge where your origin and destination are located.

(You can use ST_Line_Locate_Point to get that fraction from your point geometry, assuming you know the closest edge.)

-