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;
You must adapt this function so that it returns the nearest edge. First you must modify the link_point type (add nearest_link field):
CREATE TYPE link_point AS
name character varying,
ALTER TYPE link_point
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'
IF row.id is null THEN
res.id = -1;
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.