Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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).

enter image description here

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

share|improve this question
add comment

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.

share|improve this answer
    
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? –  anand 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?? –  anand 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. –  anand Jun 16 '12 at 6:53
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.