[postgis-users] Correct routing path, segmets of geometry

[Emiliano Romero]

Hi Paul. I had the same problem as you. I had SOME geometries of my 
database as MultiLineString (Imported from ESRI).

First sorry about my poor english.

I dont know if this message should go here or in pgrouting list.

I'm going to try to explain what are the things you have to do to get 
the complete route, splited in the nearest points of start and end.

1) You have to get the nearest segment of your points.
2) Use source and target of that segments to get the route.
3) Now you have to get the first segment, but starting in the nearest 
point of your start point. But be careful here. Yo have to see the 
direction of the segment. You can do that comparing the source and 
target of the the segment 1 and 2. Here You will know when to use:
    Select ST_LINE_SUBSTRING(
                    ST_LineMerge(line),
                    0,
                    
ST_line_locate_point(st_linemerge(line),<startPoint>)) from route;
    Or
    Select ST_LINE_SUBSTRING(
                    ST_LineMerge(line),
                    ST_line_locate_point(st_linemerge(line),<startPoint>),
                    1) from route;  

4) Now get all the other segments.
5) Detect the LAST segment (This is going to be the same segment that 
you get nearest your point). And compare this segment with the segment 
before this one. Here you have to do the same you do with the first segment.

Remember that st_line_locate_point return a number between 0-1 that 
represent nearest position IN the line of the startPoint.

If you want you could send an email to pgrouting mailing list and maybe 
there we can do some Store Procedure to get what you want.

Hope this helps.

Regards

Emiliano Romero




paweluz wrote:
> Hi everybody!
>
> First sorry for my poor English. My name is Paul. I have some problem with
> the routing. I was doing tutorial form page
>
> http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007
> http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007 
>
> I got pretty good. The results can be see at page:
>
> http://img509.imageshack.us/img509/9250/routing.jpg
> http://img509.imageshack.us/img509/9250/routing.jpg 
>
> I you can see, it does work but not to the end. I have to segment the start
> end ending geometry. 
> Get the right start and end point. I read a lot about this in the Internet.
> I found some 
> informations. I know that I have to use this 2 functions
>
> line_locate_point();
> line_substring();
>
> Then I read more, and I found that I actually cant use them because I don't
> have coreect geometry. 
>
> I should have linestring but I have multilinestring - as you can see on the
> screen 
>
> http://img22.imageshack.us/img22/2484/dbase900913.jpg
> http://img22.imageshack.us/img22/2484/dbase900913.jpg 
>
> So I started to look for other function and I found out about function
> 'line_interpolate_point'. I 
>
> try to use it but all I got were empty rows. I used like this. 
>
> SELECT line_interpolate_point(GeometryN(the_geom,900913),
> line_locate_point(GeometryN(the_geom,900913),PointFromText('POINT(1725140.73233
> 6788749.88889)'))) 
>
> FROM zielona_gora_routing 
>
> But it gives me only empty rows :( SO I don't know what to do now. I am so
> close to the answer but 
>
> I don't know how to get it right. Can anybody please help me. I thinking
> that I have to update 
>
> this function
>
>
> $start = split(' ',$_REQUEST["startpoint"]);
> $startPoint = array($start[0], $start[1]);
>
>   // Retrieve end point
> $end = split(' ',$_REQUEST["finalpoint"]);
> $endPoint = array($end[0], $end[1]);
>
> $startEdge = findNearestEdge($startPoint);
> $endEdge   = findNearestEdge($endPoint);
>
>
> //FUNCTION!!!!!!
> unction findNearestEdge($lonlat) {
>   	    // Connect to database
>     $con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER);
> 	$lonlat1=$lonlat[0]-200;
> 	$lonlat2=$lonlat[1]-200;
> 	$lonlat3=$lonlat[0]+200;
> 	$lonlat4=$lonlat[1]+200;
>
>     $sql = "SELECT gid, source, target, the_geom,
> 			 distance(the_geom, GeometryFromText(
>                   'POINT(".$lonlat[0]." ".$lonlat[1].")', 900913)) AS dist
>             FROM ".TABLE."
>             WHERE the_geom && setsrid(
>                   'BOX3D(".$lonlat1."
>                          ".$lonlat2.",
>                          ".$lonlat3."
>                          ".$lonlat4.")'::box3d, 900913)
>             ORDER BY dist LIMIT 1";
>
>     $query = pg_query($con,$sql);
>
>     $edge['gid']      = pg_fetch_result($query, 0, 0);
>     $edge['source']   = pg_fetch_result($query, 0, 1);
>     $edge['target']   = pg_fetch_result($query, 0, 2);
>     $edge['the_geom'] = pg_fetch_result($query, 0, 3);
>
>     // Close database connection
>     pg_close($con);
>
>     return $edge;
>   }
>
> You can see this function in the tutorial that link I gave at the top of
> this post. Can anybody please help me. I would really appreciate...
>
> Regards
> Paul
>
>   

 This email and any attachments thereof may contain confidential, privileged, proprietary, or otherwise private information. This email is intended solely for the use of the individual to whom it is addressed. If you are not the intended recipient of the email and its attachments please inform the sender immediately and do not disclose the contents to any other person, use it for any purpose or store or copy the information in any way and delete this e-mail and its attachments from your system. Any views or opinions expressed are solely those of the author.