[pgrouting-users] Issues with pgrouting workshop code in postgis2.11/pgrouting 2.0

[sittichai choosumrong]

Hi Emmanuel

If you want to make the old workshop works on pgRouting 2.0




>>>     |<?php
>>>
>>>        // Database connection settings
>>>        define("PG_DB"   ,  "routing");
>>>        define("PG_HOST",  "localhost");
>>>        define("PG_USER",  "postgres");
>>>        define("PG_PORT",  "5432");
>>>         define("PG_PASSWD",    "*******");
>>>        define("TABLE",    "eastlegon");
>>>
>>>        $counter=  $pathlength=  0;
>>>
>>>
>>>        // Retrieve start point
>>>        $start=  split('',$_REQUEST['startpoint']);
>>>        $startPoint=  array($start[0],  $start[1]);
>>>
>>>
>>>        // Retrieve end point
>>>        $end=  split('',$_REQUEST['finalpoint']);
>>>        $endPoint=  array($end[0],  $end[1]);
>>>
>>>
>>>        // Find the nearest edge
>>>        $startEdge=  findNearestEdge($startPoint);
>>>        $endEdge=  findNearestEdge($endPoint);
>>>
>>>
>>>        // FUNCTION findNearestEdge
>>>        function  findNearestEdge($lonlat)  {
>>>
>>>          // Connect to database
>>>          $con=  pg_connect("dbname=".PG_DB." host=".PG_HOST."
>>> user=".PG_USER." password=".PG_PASSWD);
>>>
>>>          $sql=  "SELECT gid, source, target, the_geom,
>>>
>>>                   distance(the_geom, GeometryFromText(
>>>                        'POINT(".$lonlat[0]."".$lonlat[1].")', 4326)) AS
>>> dist
>>>                  FROM ".TABLE."
>>>                  WHERE the_geom && setsrid(
>>>                        'BOX3D(".($lonlat[0]-200)."
>>>                               ".($lonlat[1]-200).",
>>>                               ".($lonlat[0]+200)."
>>>                               ".($lonlat[1]+200).")'::box3d, 4326)
>>>                  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;
>>>        }
>>>
>>>        // Select the routing algorithm
>>>        switch($_REQUEST['method'])  {
>>>
>>>          case  'SPD'  :  // Shortest Path Dijkstra
>>>
>>>            $sql=  "SELECT rt.gid, AsText(rt.the_geom) AS wkt,
>>>
>>>                         length(rt.the_geom) AS length, ".TABLE.".id
>>>                      FROM ".TABLE.",
>>>                          (SELECT gid, the_geom
>>>                              FROM dijkstra_sp_delta(
>>>                                  '".TABLE."',
>>>                                  ".$startEdge['source'].",
>>>                                  ".$endEdge['target'].",
>>>                                  3000)
>>>                           ) as rt
>>>                      WHERE ".TABLE.".gid=rt.gid;";
>>>            break;
>>>
>>
   Here you have to change the function to pgRouting 2.0 using pgr_dijkstra
function as show below;

         case 'SPD' : // Shortest Path Dijkstra
           $sql = "SELECT w.gid, AsText(w.the_geom) AS wkt,w.name,
ST_length(w.the_geom) AS length, w.gid FROM ".TABLE." w,
            (SELECT seq, id1 AS node, id2 AS edge,cost FROM pgr_dijkstra('
                SELECT gid AS id,source::integer,
                          target::integer,
                          length::double precision AS cost
                  FROM ".TABLE."',
                                    ".$startEdge['source'].",
                                     ".$endEdge['target'].", false, false)
)as rt where w.gid=rt.edge;";

         break;



It is works on my Computer using this code.

Best,
Bomp

-- 
■□■□■□■□■□■□■□■□■
Graduate School for Creative Cities
Urban Informatics
GIS Laboratory
Osaka City University 大阪市立大学
Sittichai Choosumrong
TEL +81-80-3812-2102
■□■□■□■□■□■□■□■□■
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20140108/a4bb4794/attachment.html>