demo (#22) - Routing path not correct, how to get it right ?? (#252) - Message List

Routing path not correct, how to get it right ??

Hi !

First sorry for my poor english :) My name is Paul. This is my second topic. In first time I got the answer :) (thanks to daniel)

Now I am having some problem with the path that is given on my map. The rout that is drawing is not exacly correct. here is some examples.

 http://img509.imageshack.us/img509/9250/routing.jpg

In the php file the method findingnearles looks like that:

  function 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;
  }

Are there any parameters that I can change , that could help me ? Or maybe it is because of the shp that I have at the begging. I am not sure if for example road looks like this:

- road
* node
= the vector layer, found path
^ start point
*-------*---------------------------* ...end

and some click in the middle of the second road, like this

*-------*------------^--------------* ...end

then the roud on the map would looks like this

*-------*------------^==============* ...end

or maybe

*-------*============^==============* ...end

I hope you understand what I mean... Please help me understand all this stuff. I was hoping that it would be working like on the las example, for example something like that:

 http://demo.postlbs.org/openrouting/web/geobase.html

or

 http://demo.postlbs.org/openrouting/web/suuchi.html

And I am also trying to figure it out how come in the pages that I mentioned before when I click and hold the left mouse button I can move the map, but in may page there is an option 'navigate', and when it is checked I can move my page, I just input start or end point. Any ideas how can I get rid of the 'navigation' option an just have start and end points. Thanks a lot for your help...

Regards Paul

  • Message #902

    Reply from daniel from page http://pgrouting.postlbs.org/discussion/22/251

    In any case you're missing partial geometries, because shortest path algorithms don't split edges for you. You have to do this yourself and extend the PHP script or write a wrapper or whatever. You can use two PostGIS functions for this as far as I remember: line_locate_point(LineString?, Point) and line_substring(linestring, start, end) to get partial geometries.
    This way you can split your closest edge for start and end in two parts each. You actually have a few cases then and need to add the correct line_substring to the start and end of your routing result (or you have to remove it).

    • Message #903

      Thank you for moving the topic here!

      Well, Anton wrote an extra wrapper some time ago for Shooting* algorithm, which actually does most things for you.

      We put that wrapper in the sandbox directory on SVN: http://pgrouting.postlbs.org/browser/sandbox/wrappers/routing_core_smart.sql

      It's not documented, so you need to read a bit in the source file probably. It's nothing official but works well.

      Function name is then shootingstar_sp_smart(parameters...). You can find parameter information in line 380.

      What you need for this is an entry in the "network_info" table, which is automatically created when you load the function.

      SELECT add_network_info('<table name>');
      

      It precalculates some parameters of your network table.

      • Message #906

        Hi!

        Thanks a lot for all the clues, I really appreciate :) I was really reading a lot of stuff on the internet about segmentation of the line. I find some interesting information. I read some info about this function:

        line_locate_point();
        line_substring();
        

        But unfortunately I can't use them because as I found out that all my geometry in the base must be linestring , and I have multilinestring as you can see in the screen

         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
        

        I was also looking into file you gave me the link but I guess this is to difficult to me. I don't know what to do. I am so close to the end... I don't get it, why it wasn't included into tutorial!!! without that it's practically does not work. I also don't know where to put all this function that I found. I thinking that I have to update this function

          function 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;
          }
        

        but I have no clue how to do this. Can anyone please help me...

      • Message #909

        Hi daniel !

        Finally I have done it !!!!!!!!!

        My path is now correct... What a great feeling. I had to chance a little bit the php code from that tutorial, but afters couple hours I managed to do this. Everything is working all right. I was thinking about writing about what I have done, maybe some tutorial or something, but I don't know even if I wrote it, it would be added to the forum, or tutorial pages. Now I have to focus on my master thesis, but in 2,3 month why not :) Thanks a lot for your help. I wouldn't have done it without your help.

        All the best Regards, Paul

        • Message #911

          Hi paweluz,

          Fine :-) It would be great to get that code, I`m working at the same step now :-)

          • Message #912

            Hi Pawulez and Kai,

            Great to see you contributing to the project. It has always been our goal to get more people join the project, so in the future we might be be able to get an OSGeo project.

            I'm thinking how to organize the Wiki better. Tutorials are very popular as far as I can see, but it's quite hard to keep them updated. Also it doesn't help much to have too many of them, because people are just confused which one they should chose. Also it takes quite some time to write a complete one.

            So I wanted to ask you what you think about something we could call "pgRouting recipies": a collection of more or less short howto's, tips and tricks, code snippets, etc., which can be grouped, tagged, organized in several ways. If we can keep them modular, people could easily pick them as they need.

            I think this would help a lot to keep everything up-to-date. It's also much easier to contribute. Anyone has some experience with this?

            • Message #913

              Hi Daniel,

              sounds like a good idea, I don`t have much knowledge about that kind of documentation but I really would like to contribute as far as I can. In the moment I try to write a function in plpgsql that solves the problem pawulez was talking in that discussion about, that will be a small recipe as well.

              In that semester our students are using pgrouting for a project, I will ask them how they handled the so-far-instructions and the help in the documentations, that could give some points what to change.

              Or what do you thing? Should we start directly in the next weaks to build a a new infrastructure?

              • Message #914

                One thing I forgot, I thing it`s very important to have some "extensive" tutorials here, because there is so much stuff people need to know to handle pgRouting (SQL, PHP, Javascript, maybe MapServer, OpenLayers? etc.), that coulds be an argumet against small "snippet-recipes" because many people don`t know how to handle it......

                • Message #916

                  Hi daniel and Kai!

                  I think it is a great idea to create something like "pgRouting recipies", some kinds of tips and stuff like this. But as Kai said I think tutorials are also needed. Without this tutorial

                   http://pgrouting.postlbs.orgwikiWorkshopFOSS4G2007

                  I wouldn't have done a thing! But as daniel said it is important to UPDATE them! I remember I was fighting with this line

                                  OpenLayers.loadURL("./ax_routing.php",
                                                     OpenLayers.Util.getParameterString(result),
                                                     null,
                                                     displayRoute);
                  

                  for about 2,3 days, then I talk with my friends and they told me to do this in that way:

                   OpenLayers.loadURL("rout.php?startpoint="+startPoint.geometry.x+" "+startPoint.geometry.y+"&finalpoint="+stopPoint.geometry.x+" "+stopPoint.geometry.y+"&method="+l_method+"&srid=900913",
                                                     OpenLayers.Util.getParameterString(result),
                                                     null,
                                                     displayRoute);
                  

                  After I handled with it, Kai gave me the link to his new tutorial and I found that now it was changed :) I still have a little error with the routing, but I don't know even how to ask the question because I don't know what is wrong :) I am going to write as soon as I can how I managed with everything... but I want everything to be working perfectly :) Thanks again to both of you for help :)

                  Regards Paul

                  • Message #918

                    Good discussion, but we could move this to the mailing list. TRAC and this forum extension is little bit inconvenient, because most people won't be aware of this thread.

                    Paul, I'm not sure you have signed up to the mailing list, so I post my answer here:

                    Looking at the parts of the current tutorials, the OpenLayers? part for example actually hasn't much to do with the library. But it's very useful, right?
                    But what if people want to use their own data and not OpenLayers??

                    I think we could group documentation into parts like

                    • Installation
                    • Data preparation
                      • Import
                      • Topology creation
                      • Data validation (checks)
                    • Data processing
                      • pgRouting Basic recipies
                      • pgRouting Advanced recipies
                      • PostgreSQL/PostGIS tips and tricks
                    • Data output
                      • Postprocessing
                      • Driving directions
                    • Application integration

                    This way users get a guideline what needs to be done, but they can pick what they need. And it's less work to add additional recipies or correct errors.

                    • Message #920

                      This overview looks good. Will you start to build that new structure? Shall I do it? In the next week I should have a couple of hours time for that.....

                    • Message #921

                      Hi!

                      Daniel this looks really great! It is great that you both trying to make working with pgrouting easier :)

                      I have done everything! All is working perfectly well. I think tomorrow I am going to write here in this thread...

                      I have one more question. Actually not about implementation. I was curies if any of you knows any free jsp server, when I can put my page ? I am working with geoserver witch is written in java, so it's jps file, also as you know I have postgresql database and my main page is in *.php So it needs to support: - jsp (java) - application as war file - php - postgre sql Do you know any. I found eatj.com but it was just for 50 Mb and for a little time, so I could not use it. And it was really unstable. Maybe you know how to get acces to any server like that.... If you do , please do not hesitate to write to me

                      Regards, Paul

                      • Message #924

                        Hi Paul,

                        Not that I'm aware of free jsp hosting. Did you take a look at Google App Engine?  http://code.google.com/intl/en/appengine/

                        It seems they support now Java, too. Never tried it though.

              • Message #923

                Thank you for the offer, Kai!

                I think I will sort the existing documentation a little bit and setup a basic structure, if you don't mind.
                But this is a wiki, so if you feel like you want to change something, go ahead.

              • Message #926

                Hi!

                As I said before I am going to write how I managed with getting the right path. When I done tutorial my path looked like this:

                 http://img509.imageshack.us/img509/9250/routing.jpg

                That of course wasn't enough. I needed to to this correct, and I did. Steps: 1. You have to split two edges in two parts. The start edges and the ending edges. Then you would be having 4 pieces. I did it in this way:

                  $sqlstart1 =  "Select astext(ST_LINE_SUBSTRING(
                                    ST_LineMerge(the_geom),
                                    0,  ST_line_locate_point(st_linemerge(the_geom),GeometryFromText(
                                  'POINT(".$startPoint[0]." ".$startPoint[1].")',900913)))) as begin from ".TABLE." where gid=".$startEdge['gid']."";
                  $sqlstart2 =  "Select astext(ST_LINE_SUBSTRING(
                                    ST_LineMerge(the_geom),
                                    ST_line_locate_point(st_linemerge(the_geom),GeometryFromText(
                                  'POINT(".$startPoint[0]." ".$startPoint[1].")',900913)),
                                    1)) as begin from  ".TABLE." where gid=".$startEdge['gid']."";
                  $sqlend1 =  "Select astext(ST_LINE_SUBSTRING(
                                    ST_LineMerge(the_geom),
                                    ST_line_locate_point(st_linemerge(the_geom),GeometryFromText(
                                  'POINT(".$endPoint[0]." ".$endPoint[1].")',900913)),
                                    1)) as ending from  ".TABLE." where gid=".$endEdge['gid']."";
                  $sqlend2 =  "Select astext(ST_LINE_SUBSTRING(
                                    ST_LineMerge(the_geom),
                                    0,  ST_line_locate_point(st_linemerge(the_geom),GeometryFromText(
                                  'POINT(".$endPoint[0]." ".$endPoint[1].")',900913)))) as ending from ".TABLE." where gid=".$endEdge['gid']."";
                

                As you can see I split the the start edge form 0 to start point to 1, and this same with ending edge. 2. After you get the path from the Dijkstra or else algorithm you need to be sure that this path does not include two edges - the one with starting point and the one with ending point. I managed this with one if

                	if (($edge['wkt']!=$startEdge['the_geom'])and(($edge['wkt']!=$endEdge['the_geom'])))
                

                I don't know why but the starting and ending edge was sometimes added to result of routing algorithnm. Once there were added, and once not. So I used this if instruction 3. Now in the main 'while' instruction under the 'if' I wrote before you need to find the edge thats touches the one from the start edge and the one from the end edge. Because now you have 4 edges (2 start and 2 end - you got them before with split instruction). Now each piece ypu have to check if its touch all the others edges from the routing path. I did it in this way

                 while($edge=pg_fetch_assoc($query_main))
                  {
                	if (($edge['wkt']!=$startEdge['the_geom'])and(($edge['wkt']!=$endEdge['the_geom'])))
                	{
                	    $sql =  "SELECT ST_Touches(GeometryFromText('{$edge['wkt']}'),GeometryFromText('$start1')) AS inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$startinter1=$edge1['inter'];
                			if ($startinter1=="t")
                			{
                				$first_edge=$start1;
                			}
                		}
                		$sql =  "Select ST_Touches(GeometryFromText('{$edge['wkt']}'),GeometryFromText('$start2')) as inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$startinter2=$edge1['inter'];
                			if ($startinter2=="t")
                			{
                				$first_edge=$start2;
                			}
                		}
                	    $sql =  "Select ST_Touches(GeometryFromText('{$edge['wkt']}'),GeometryFromText('$end1')) as inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$endinter1=$edge1['inter'];
                			if ($endinter1=="t")
                			{
                				$last_edge=$end1;
                			}
                		}
                		$sql =  "Select ST_Touches(GeometryFromText('{$edge['wkt']}'),GeometryFromText('$end2')) as inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$endinter2=$edge1['inter'];
                			if ($endinter2=="t")
                			{
                				$last_edge=$end2;
                			}
                		}
                		$pathlength += $edge['length'];
                	    $xml .= "\t<edge id='".++$counter."'>\n";
                		$xml .= "\t\t<id>".$edge['id']."</id>\n";
                		$xml .= "\t\t<wkt>".$edge['wkt']."</wkt>\n";
                		$xml .= "\t\t<length>".round(($pathlength/1000),3)."</length>\n";
                		$xml .= "\t</edge>\n";
                	}
                

                It is pretty easy. You should get 2 geometry - final and start. And that is pretty much everything. But it is not everything !!!!

                When you try to get the shortest path from the road created only with one geometry or with two geometry ten the program would give you an error. It is the situation when you trying to get the shortest path on one street or two streets that are next to each other. You have to : 1. At beggining add

                 $startinter1=FALSE;
                  $startinter2=FALSE;
                  $endinter1=FALSE;
                  $endinter=FALSE;
                  $last_edge=FALSE;
                  $first_edge=FALSE;
                

                Then the situation with two geometries:

                if (($last_edge==false)and($startEdge['the_geom']!=$endEdge['the_geom']))
                	{
                		$sql =  "SELECT ST_Touches(GeometryFromText('$start1'),GeometryFromText('$end1')) AS inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$startinter1=$edge1['inter'];
                			if ($startinter1=="t")
                			{
                				$first_edge=$start1;
                				$last_edge=$end1;
                			}
                		}
                		$sql =  "Select ST_Touches(GeometryFromText('$start1'),GeometryFromText('$end2')) as inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$startinter2=$edge1['inter'];
                			if ($startinter2=="t")
                			{
                				$first_edge=$start1;
                				$last_edge=$end2;
                			}
                		}
                		$sql =  "SELECT ST_Touches(GeometryFromText('$start2'),GeometryFromText('$end1')) AS inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$endinter1=$edge1['inter'];
                			if ($endinter1=="t")
                			{
                				$first_edge=$start2;
                				$last_edge=$end1;
                			}
                		}
                		$sql =  "Select ST_Touches(GeometryFromText('$start2'),GeometryFromText('$end2')) as inter";
                		$query = pg_query($dbcon,$sql);
                		while($edge1=pg_fetch_assoc($query))
                		{
                			$endinter2=$edge1['inter'];
                			if ($endinter2=="t")
                			{
                				$first_edge=$start2;
                				$last_edge=$end2;
                			}
                		}
                	}
                

                And with one geometry

                	if (($last_edge==false)and($startEdge['the_geom']==$endEdge['the_geom']))
                	{
                		  $sqlmin1 =  "Select ST_line_locate_point(st_linemerge(the_geom)," .
                		  		"GeometryFromText('POINT(".$startPoint[0]." ".$startPoint[1].")',900913))as min from ".TABLE." where gid=".$startEdge['gid']."";
                		  $query = pg_query($dbcon,$sqlmin1);
                		  while($edge=pg_fetch_assoc($query))
                		  {
                				$min1=$edge['min'];
                		  }
                		  $sqlmin1 =  "Select ST_line_locate_point(st_linemerge(the_geom)," .
                		  		"GeometryFromText('POINT(".$endPoint[0]." ".$endPoint[1].")',900913))as min from ".TABLE." where gid=".$endEdge['gid']."";
                		  $query = pg_query($dbcon,$sqlmin1);
                		  while($edge=pg_fetch_assoc($query))
                		  {
                				$min2=$edge['min'];
                		  }
                		  if ($min1 <$min2)
                		  {
                		  	  $sqlpoint_to_point =  "Select astext(ST_LINE_SUBSTRING(
                		                    ST_LineMerge(the_geom),
                		                    		$min1,
                		                    		 $min2)) as point from ".TABLE." where gid=".$endEdge['gid']."";
                		  }
                		  else
                		  {
                		      $sqlpoint_to_point =  "Select astext(ST_LINE_SUBSTRING(
                		                    ST_LineMerge(the_geom),
                		                    		$min2,
                		                    		 $min1)) as point from ".TABLE." where gid=".$endEdge['gid']."";
                		  }
                		  $query = pg_query($dbcon,$sqlpoint_to_point);
                		   while($edge=pg_fetch_assoc($query))
                		  {
                				$last_edge=$edge['point'];
                				$first_edge=$edge['point'];
                		  }
                	}
                

                You may of course make some function and the code would be much shorter. I don't think this is the fastest way but it is working !!!!! The screen ... maybe unnecessary but it LOOKS SO GREAT !!!:)  http://img147.imageshack.us/img147/2904/routingoki.jpg

                So I think this is all. If you have any question don't hesitate to write

                Regards, Paul

                • Message #927

                  I have one more questions. Do you know where can I have some documentation about pgRouting...

                  especially for all the algorithms, how was it written, all the input parameters...I have to do some research for my master thesis, and it would help me a lot. e.g. the function I use:

                  - assign_vertex_id
                  - shortest_path_shooting_star
                  - shortest_path_astar
                  - shortest_path
                  

                  or maybe some others. I found only this page

                  http://pgrouting.postlbs.org/wiki/Dijkstra

                  Is the any manual, or pdf file, or maybe there were written any articles? If you aware of any please write to me

                  Regards, Paul

                  • Message #928

                    Did you try to look at http://pgrouting.postlbs.org/wiki/pgRoutingDocs already?

                    • Message #929

                      Hi

                      Yes, I have already found this page... I gave a link in my last post to this page, but thanks anyway :)

                      • Message #933

                        I found also one solution to visualize the "real" geometry (pg_dijkstra)

                        That function gives you the nearest point on a multiline (If you set a start point):


                        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;


                        That value given back (it's the start-geometry of a route) is put into the following function which gives you the "new" geometry of the first Multiline (where the start_point is set). The new geometry goes from the intercept_point of the two database-geometries until the start-point, so it`s a "new" Multilinestring:

                        CREATE OR REPLACE FUNCTION schnittpunkt(gid_a integer,gid_b integer,start geometry)

                        RETURNS geometry AS

                        $BODY$ DECLARE intercept_point geometry; number_of_points integer; first_point_of_geometry geometry; j integer; point_geom geometry; line text; mywkt text; p text; new_factor integer; endgeometry geometry[] := '{}';

                        BEGIN

                        RAISE NOTICE 'Here we go........';

                        --Give me the intercept point of two geometries we use...... intercept_point:= ST_astext(intersection(a.the_geom, b.the_geom)) from (select the_geom from ways where gid=$1)a, (select the_geom from ways where gid = $2) b;

                        RAISE NOTICE 'intercept_point %',astext(intercept_point);

                        -- Give me the number of points of the geometry we touch with the start-point number_of_points:=ST_NumPoints(the_geom) from ways where gid=$1;

                        mywkt:=; line:=;

                        RAISE NOTICE 'Numer of points in that geometry is %',number_of_points;

                        first_point_of_geometry:=PointN(c.the_geom,1) from (select (the_geom) from ways where gid=$1)c,ways where gid=$1;

                        -- If intercept_point and first point of geometry (with start point) is equal IF astext(intercept_point)=astext(first_point_of_geometry) THEN

                        RAISE NOTICE 'Equal!!';

                        FOR j IN 1 .. number_of_points LOOP

                        point_geom:=PointN(c.the_geom,j) from (select (the_geom) from ways where gid=$1)c,ways where gid=$1;

                        RAISE NOTICE 'Call: %',j; endgeometry[j]:=point_geom;

                        RAISE NOTICE 'The point is %',astext(point_geom);

                        line:=;

                        line:= line X(endgeometry[j])' 'Y(endgeometry[j])', 'X(endgeometry[j-1])' 'Y(endgeometry[j-1]);

                        -- A line is build

                        line:='LINESTRING''(' line ')';

                        SELECT INTO p ST_intersects(line,buffer($3,1));

                        IF j=1 THEN

                        mywkt:= mywkt X(endgeometry[j])' 'Y(endgeometry[j])', '; END IF;

                        IF p!= 't' THEN RAISE NOTICE 'The start point does not touch a part of a multiline';

                        mywkt:= mywkt X(endgeometry[j])' 'Y(endgeometry[j])', ';

                        END IF;

                        IF p = 't' THEN RAISE NOTICE 'Interesting: Start point touches part of multiline, we replace it!!';

                        mywkt:= mywkt X($3)' 'Y($3);

                        mywkt:='MULTILINESTRING''((' mywkt '))'; RAISE NOTICE 'We have got the new geometry: %',mywkt; RETURN mywkt; END IF;

                        END LOOP;

                        ELSE

                        -- Ok, intercept_point and first point of geometry (with start point)are not equal -- We have to go "thr other way round"

                        FOR j IN 1 .. number_of_points LOOP

                        new_factor:=number_of_points+1-j;

                        point_geom:=PointN(c.the_geom,new_factor) from (select (the_geom) from ways where gid=$1)c,ways where gid=$1;

                        RAISE NOTICE 'Call number: %',j; endgeometry[j]:=point_geom;

                        RAISE NOTICE 'The point is at %',astext(point_geom);

                        line:=;

                        line:= line X(endgeometry[j])' 'Y(endgeometry[j])', 'X(endgeometry[j-1])' 'Y(endgeometry[j-1]);

                        line:='LINESTRING''(' line ')';

                        SELECT INTO p ST_intersects(line,buffer($3,1));

                        IF j=1 THEN

                        mywkt:= mywkt X(endgeometry[j])' 'Y(endgeometry[j])', '; END IF;

                        IF p!= 't' THEN RAISE NOTICE 'The start point does not touch a part of a multiline';

                        mywkt:= mywkt X(endgeometry[j])' 'Y(endgeometry[j])', ';

                        END IF;

                        IF p = 't' THEN RAISE NOTICE 'The start point touches a part of a multiline';

                        mywkt:= mywkt X($3)' 'Y($3);

                        mywkt:='MULTILINESTRING''((' mywkt '))'; RAISE NOTICE 'The geometry is %',mywkt; RETURN mywkt; END IF;

                        END LOOP;

                        END IF;

                        RETURN mywkt;

                        END; $BODY$

                        LANGUAGE 'plpgsql' IMMUTABLE STRICT;

                        Function is called e.g. SELECT astext(schnittpunkt(2174,2175,'POINT(899129.095395082 6851009.00957939)'))

                        I will clean up the code a bit, write the same for the end-point and put in in the recipies if I`m ready.

                        Best regards, Kai

                • Message #940

                  Hi Paul ,

                  I am stuck with with the same issue of getting the accurate path.

                  I have read your post and got some idea about it. My quetion is when I execute: Select astext(ST_LINE_SUBSTRING(ST_LineMerge(the_geom), 0, ST_line_locate_point(st_linemerge(the_geom),GeometryFromText?( 'POINT(46.66319622618476 24.70309900767372)',4326)))) as

                  begin from myroad where gid=7130;

                  POINT(46.66319622618476 24.70309900767372) -- the point which I gets when I click on to the start point on the map. gid = 7130 -- the gid corresponding to the source when executing : SELECT source as src, target as trgt, distance(the_geom, GeometryFromText?('POINT(46.66319622618476 24.70309900767372)', 4326))" +

                  " AS dist FROM myroad WHERE the_geom && setsrid('BOX3D("+ minBoundLat +" "+minBoundLon+","+ maxBoundLat+" "+maxBoundLon+")'
                  box3d, 4326) ORDER BY dist LIMIT 1;";

                  I get the output as :

                  "LINESTRING(46.6643375667239 24.7034833681905,46.6636878360818 24.70321293342)"

                  but I get te same line string output in my dijikstra query also "LINESTRING(46.6643375667239 24.7034833681905,46.6636878360818 24.70321293342)"

                  Is my gid=7130 a wrong one? If so how to get correct gid.

                • Message #948

                  I have referred to the post by Paul (Message #926 ) and was able to draw the routing path exactly from the point I selected as start and end. But the issue which I found is that for some lines the start and the end point does not get drawn exactly. The issue which I later discovered was I was unable to choose which portion of the line should be considered to draw the route. (i.e) after executing the query below I got two different line strings.

                  $sqlstart1 = "Select astext(ST_LINE_SUBSTRING(

                  ST_LineMerge(the_geom), 0, ST_line_locate_point(st_linemerge(the_geom),GeometryFromText?(

                  'POINT(".$startPoint[0]." ".$startPoint[1].")',900913)))) as begin from ".TABLE." where gid=".$startEdgegid?."";

                  $sqlstart2 = "Select astext(ST_LINE_SUBSTRING(

                  ST_LineMerge(the_geom), ST_line_locate_point(st_linemerge(the_geom),GeometryFromText?(

                  'POINT(".$startPoint[0]." ".$startPoint[1].")',900913)),

                  1)) as begin from ".TABLE." where gid=".$startEdgegid?."";

                  which when given as an input to

                  SELECT ST_Touches(GeometryFromText?('{$edgewkt?}'),GeometryFromText?('$start1' ' '$sqlstart2' )) AS inter";

                  for both $sqlstart1 $sqlstart2 it returns "t" as output. So in this case I get confused with which Linestring to be considered to draw the routing path. Also in some cases even when I choose the linestring which yields "t" as output for

                  SELECT ST_Touches(GeometryFromText?('{$edgewkt?}'),GeometryFromText?('$start1' ' '$sqlstart2' )) AS inter"; my route drawn is incorrect.

                  Please go through the link and find the screen shots of my routing:  http://shafeervc.wordpress.com/2009/05/13/pgrouting/ Compare those two images and suggest me a solution.

                  • Message #950

                    There was a very good post in the PostGIS mailing list explaining this issue, I think:  http://postgis.refractions.net/pipermail/postgis-users/2009-April/023275.html

                    Otherwise as I desrcibed above, there is a "smart" wrapper in a sandbox, that does all this for you without splitting and merging lines later.

                    I see that this topic grew quite a lot and it'S a bit hard to read now. Once time allows I'm thinking to write a Wiki page about the "smart" wrapper. If someone wants to volunteer on this, please go ahead.