Pgrouting PHP don't work with Openlayers and GeoExt

manpreet Best Answer 2 years ago

I'm working with this source code. Html file (Openlayers + GeoExt) seems to work fine - everything shows up correctly but when I click on map nothing happend. I use my OSM data loaded to postregsql by osm2po. I think the problem is PHP file. I have changed it by myself but I'm almost sure I have made some mistake. Maybe somebody can help me to figured out what's wrong? Maybe the sql queries did errors? Any sugestions? I use PostgreSQL 9.5.3, PostGIS 2.2 and pgRouting 2.3.2. I'm not sure if my software version is comaptibile with used tutorial

PHP file:

 php   

// Database connection settings
   define("PG_DB", "ble");
  define("PG_HOST", "localhost"); 
  define("PG_USER", "postgres");
  define("PG_PSSWD", "db_pass");
  define("PG_PORT", "5432"); 
  define("TABLE",   "aa_2po_4pgr");

// Retrieve start point $start = split(' ',$_REQUEST['startpoint']); $startPoint = array($start[0], $start1);

// Retrieve end point $end = split(' ',$_REQUEST['finalpoint']); $endPoint = array($end[0], $end1);

?>

// 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." port=".PG_PORT." user=".PG_USER."    password=".PG_PSSWD)
    or die("nie udalo sie polaczyc z baza danych");

      $sql = "SELECT id, source, target, geom_way, 
                  ST_distance(geom_way, ST_GeometryFromText(
                       'POINT(".$lonlat[0]." ".$lonlat[1].")', 4326)) AS dist 
                 FROM ".TABLE."  
                 WHERE geom_way(
                       '(".($lonlat[0]-0.1)." 
                              ".($lonlat[1]-0.1).", 
                              ".($lonlat[0]+0.1)." 
                              ".($lonlat[1]+0.1).")') 
                 ORDER BY dist LIMIT 1";

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

      $edge['id']      = pg_fetch_result($query, 0, 0);  
      $edge['source']   = pg_fetch_result($query, 0, 1);  
      $edge['target']   = pg_fetch_result($query, 0, 2);  
      $edge['geom_way'] = 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.id, ST_AsGeoJSON(rt.geom_way) AS geojson, 
                 length(rt.geom_way) AS length, ".TABLE.".id 
              FROM ".TABLE.", 
                  (SELECT id, geom_way 
                      FROM pgr_dijkstra(
                          '".TABLE."',
                          ".$startEdge['source'].",
                          ".$endEdge['target'].",
                          0.1)
                   ) as rt 
              WHERE ".TABLE.".id=rt.gid;";
    break;

} // close switch

// Connect to database $dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." port=".PG_PORT." user=".PG_USER." password=".PG_PSSWD);

// Perform database query $query = pg_query($dbcon,$sql);

?>

'FeatureCollection', 'features' => array() ); // Add edges to GeoJSON array while($edge=pg_fetch_assoc($query)) { $feature = array( 'type' => 'Feature', 'geometry' => json_decode($edge['geojson'], true), 'crs' => array( 'type' => 'EPSG', 'properties' => array('code' => '4326') ), 'properties' => array( 'id' => $edge['id'], 'length' => $edge['length'] ) ); // Add feature array to feature collection array array_push($geojson['features'], $feature); } // Close database connection pg_close($dbcon); // Return routing result header('Content-type: application/json',true); echo json_encode($geojson); ?>

After I lunch it in localhost (xampp) in browser i got this:

Notice: Undefined index: startpoint in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 12

Notice: Undefined offset: 1 in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 13

Notice: Undefined index: finalpoint in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 16

Notice: Undefined offset: 1 in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 17

Warning: pg_query(): Query failed: ERROR: function geom_way(unknown) doesn't exist LINE 5: WHERE geom_way( ^ HINT: No function more matching to the given name and types of arguments. Perhaps you should add explicit projection types. in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 45

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 47

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 48

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 49

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 50

Warning: pg_query(): Query failed: ERROR: function geom_way(unknown) doesn't exist LINE 5: WHERE geom_way( ^ HINT: No function more matching to the given name and types of arguments. Perhaps you should add explicit projection types. in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 45

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 47

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 48

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 49

Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 50

Notice: Undefined index: method in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 63

Notice: Undefined variable: sql in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 86

Warning: pg_query(): Query failed: in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 86

Warning: pg_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp2\htdocs\pgrout\php\pgrouting.php on line 99
{"type":"FeatureCollection","features":[]}