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: