# Create closest point on multilinestring to use in shortest_path()

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

-

## 1 Answer

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.

-
thanks for answer. I think it may work. I am not able to test it on my data. problem is my road network is MULTILINESTRING. so it it not accepting it. can you give me solution for this? – apaleja Jun 14 '12 at 13:15
you can use postgis.org/documentation/manual-2.0/ST_Dump.html to get its constituent linestrings. The syntax is a bit clunky, but it boils down to this: (ST_Dump(multilinestring geometry)).geom [the extra parens are not optional]. – lynxlynxlynx Jun 14 '12 at 13:27
@ lynxlynxlynx ..Thank you very much for quick response. actually I solved it other way (please check my latest edit). now problem is `Shortest_path()` not accepting `the_geom()` of created point as its functionality is to route between vertex. any thoughts ?? how can I overcome this problem?? – apaleja Jun 15 '12 at 6:32
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) 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. – lynxlynxlynx Jun 15 '12 at 8:21
@ lynxlynxlynx .. yes you are right. it is very crucial. but I saw few sites developed with this feature, one of site is this one: ridethecity.com can you please have a look at it. – apaleja Jun 16 '12 at 6:53