[GIS] pgrouting php problem “pg_fetch_result(): Unable to jump to row 0 on PostgreSQL result index 3”

openlayers-2pgroutingPHPpostgispostgresql

I'm trying to build a simple routing web-app using pgrouting, followed some tutorial and the latest workshop. Because I'm using PostgreSQL 9.3(x64), Postgis 2.1.1, and pgrouting 2.0 so I did some modification to the PHP script so it can be used for my version. Here are the php code:

<?php

// Database connection settings
define("PG_DB"  , "db_mapserver");
define("PG_HOST", "localhost"); 
define("PG_USER", "postgres");
define("PG_PSSWD", "postgres");
define("PG_PORT", "5433"); 
define("TABLE",   "jalan1"); 

$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." port=".PG_PORT." user=".PG_USER."    password=".PG_PSSWD);

$sql = "SELECT gid as id, source::int, target::int, geom, 
         st_distance(geom, ST_GeomFromText(
              'POINT(".$lonlat[0]." ".$lonlat[1].")', 4326)) AS st_dist 
        FROM ".TABLE."  
        WHERE geom && st_setsrid(
              'BOX3D(".($lonlat[0]-200)." 
                     ".($lonlat[1]-200).", 
                     ".($lonlat[0]+200)." 
                     ".($lonlat[1]+200).")'::box3d, 4326) 
        ORDER BY st_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['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, st_AsText(rt.geom) AS st_wkt, 
               st_length(rt.geom) AS length, ".TABLE.".id 
            FROM ".TABLE.", 
                (SELECT gid, geom 
                    FROM pgr_dijkstra(
                        '".TABLE."',
                        ".$startEdge['source'].",
                        ".$endEdge['target'].",
                        3000)
                 ) as rt 
            WHERE ".TABLE.".gid=rt.gid;";
  break;

case 'SPA' : // Shortest Path A* 

  $sql = "SELECT rt.gid, st_AsText(rt.geom) AS st_wkt, 
                 st_length(rt.geom) AS length, ".TABLE.".id 
              FROM ".TABLE.", 
                  (SELECT gid as id, geom 
                      FROM pgr_astar(
                          '".TABLE."',
                          ".$startEdge['source'].",
                          ".$endEdge['target'].",
                          3000)
                   ) as rt 
              WHERE ".TABLE.".gid=rt.gid;";  
  break;
  } // close switch

  // Database connection and query
  $dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." port=".PG_PORT." user=".PG_USER." password=".PG_PSSWD);

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

  ?>

I only modified the php code so it matches the postgis 2.x function and my own road network database. When I get into firefox and tried to compute the route, I got this error from Firebug:

pg_fetch_result(): Unable to jump to row 0 on PostgreSQL result index 3

Am I missing something?
The route ran normally in QGIS DB Manager and pgAdmin.

glad to have a suggestion from you all

regards

UPDATE(3:22 pm GMT+7): I rebuilt my whole system, installing PostgreSQL 8.4, PostGIS 1.5, and pgrouting 1.x, using the original tutorial it still gave me the same error. I'm using openlayers as my map renderer.

Best Answer

You get this error when there are no results, so the query returns 0 records, and row[0] does not exist. Using another postgres query method you can avoid this error:

$result = pg_exec($dbconn, $query);
$numrows = pg_numrows($result);

// Loop through rows in the result set
for($i = 0; $i < $numrows; $i++) 
{
    $row = pg_fetch_array($result, $i);
    echo $row["name"];
}
Related Question