Create closest point on multilinestring to use in shortest_path()

Question

I am working a routing based application where I need to create a route between two points of interest. The problem which I am facing occurs after calculating network topology using assign_vertex_id(). When I run shortest_path I get the path between vertexes and not from the point of interest (as shown in image).

Can you please help me out with this: how can I use this query or any other function to do it?

Thanks for any tips.

EDIT 1

I am using this function called multiline_locate_point() similar to line_locate_point() which helps me to find nearest LINESTRING from MULTILINESTRING to the point.

here it is:

-- Function: multiline_locate_point(geometry, geometry)

-- DROP FUNCTION multiline_locate_point(geometry, geometry);

CREATE OR REPLACE FUNCTION multiline_locate_point(amultils geometry, apoint geometry)
  RETURNS geometry AS
$BODY$
DECLARE
    mindistance float8;
nearestlinestring geometry;
nearestpoint geometry;
i integer;

BEGIN
    mindistance := (distance(apoint,amultils)+100);
    FOR i IN 1 .. NumGeometries(amultils) LOOP
    if distance(apoint,GeometryN(amultils,i)) < mindistance THEN
        mindistance:=distance(apoint,GeometryN(amultils,i));
        nearestlinestring:=GeometryN(amultils,i);
    END IF;
END LOOP;

nearestpoint:=line_interpolate_point(nearestlinestring,line_locate_point(nearestlinestring,apoint));
    RETURN nearestpoint;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;
ALTER FUNCTION multiline_locate_point(geometry, geometry) OWNER TO postgres;

this gives me the_geom() of point on line. Now the problem is shortest_path() only takes vertex (integer number) as input to create route. So how can I pass this point to shortest_path() so that it creates route from the point and not from any vertex ??

Answer 1

You can use ST_ClosestPoint to make a projection of the POI on the line. Then compute the shortest distance between the two projected points.

To split the multilinestring into parts, use ST_Dump first.

re edit1: Since it doesn't take points as inputs, I see no other way than to create the extra vertices (by intersecting the points and lines or via ST_Line_Substring) and choosing how to assign costs to the resulting pairs of lines. You could simply use linear interpolation and multiply the cost of traveling the whole line with the result of line_locate_point (for the first segment) and 1-line_locate_point (for the second segment). BUT in this case, the orientation of the line is crucial, since it affects from which end the line_locate_point ratio is measured.

edit:2 line_locate_point ratio is measured from the source_geom of a particular road. While creating a virtual vertex, gives a unique primary key and unique end_id for the virtual vertex. Take the source geom as the original road source_geom and end _geom as the actual geom of the location to which you want to route.In the virtual vertex tuple, update the length value based on the fraction you got. and run the shortest path query between source id o the start location and the end id of the virtual vertex.Tried it and successful in routing to exact point.