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.