Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0

231 views Asked by At

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,

2

There are 2 answers

0
Stephen Woodbridge On

The code you posted is from the pgRouting 1.0 workshop and will not work with pgRouting 2.0 there is a workshop for pgRouting 2.0 and you should be looking at that. pgRouting 2.0 is a major rewrite of the whole pgRouting API and there is no compatibility betwen them.

1
iqnaul On

I've had some problem here, I looked at the postgis 2.1.1 documentation FAQ they stated that all older function was included in legacy.sql. Is that mean that the php code above will work?