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

[Paragon Corporation]

Emmanuel,
 
That PHP code looks like it was written for pgRouting 1.0.  Which workshop
did you get that from?  
 
Most of the pgRouting 2.0 functions are prefixed with pgr_. There should be
a file in your install called
 In C:/Program Files/PostgreSQL/9.2/share/extensions/pgrouting_legacy.sql
which installs the 1.0 function names.  You could try using that though I
haven't personally had a need to.
 
One other thing I did notice about this code which I think is a bug is that
although PG_PORT is defined, it doesn't seem to be used for pg_connect, so
will not work without modification if you are runnning postgres on
non-default port (5432).
 
Hope that helps,
Regina
http://www.postgis.us
http://www.bostongis.com
http://www.paragoncorporation.com
 
 


  _____  

From: pgrouting-users-bounces at lists.osgeo.org
[mailto:pgrouting-users-bounces at lists.osgeo.org] On Behalf Of Emmanuel
Adegboye
Sent: Friday, January 03, 2014 4:42 PM
To: pgrouting-users at lists.osgeo.org
Subject: [pgrouting-users] Issues with pgrouting workshop code in
postgis2.11/pgrouting 2.0



I'm having issues getting the pgrouting workshop to work on Postgresql
9.2/PostGIS 2.11 and pgrouting 2.0 on windows.

How can I rewrite the following php/sql code and make it compatible with my
version:



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



    case 'SPA' : // Shortest Path A* 



      $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 astar_sp_delta(

                              '".TABLE."',

                              ".$startEdge['source'].",

                              ".$endEdge['target'].",

                              3000)

                       ) as rt 

                  WHERE ".TABLE.".gid=rt.gid;";  

      break;



    case 'SPS' : // Shortest Path Shooting*



      $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 shootingstar_sp(

                              '".TABLE."',

                              ".$startEdge['gid'].",

                              ".$endEdge['gid'].",

                              3000, 'length', false, false)

                       ) as rt 

                  WHERE ".TABLE.".gid=rt.gid;";

      break;  



  } // close switch



  // Database connection and query

  $dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER."
password=".PG_PASSWD);



  $query = pg_query($dbcon,$sql); 



  // Return route as XML

  $xml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>'."\n";

  $xml .= "<route>\n";



  // Add edges to XML file

  while($edge=pg_fetch_assoc($query)) {  



    $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";

  }



  $xml .= "</route>\n";



  // Close database connection

  pg_close($dbcon);



  // Return routing result

  header('Content-type: text/xml',true);

  echo $xml;



?>

Thanks,

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20140103/2cf80c44/attachment-0001.html>