Explanation of the php-code step 1¶
First of all a database-connection is set
<?php
// Database connection settings
define("PG_DB" , "routing");
define("PG_HOST", "localhost");
define("PG_USER", "postgres");
define("PG_PORT", "5432");
define("TABLE", "ways");
Attention: If you use a database-connection with password of course you need to define a password:
define(“PG_PASS”, “whatyouwant”);
- The $con-string then will look like:
- $con = pg_connect(“dbname=”.PG_DB.” host=”.PG_HOST.” password=”.PG_PASS.” user=”.PG_USER);
At the next step, the start- and endpoint are set.
$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]);
At the next step the nearest edge from the_geom ist searched, for each point (start end end) a kind of bounding box (here: 200 meters) is used within that it`s looked for the_geom. Because of ORDER BY dist LIMIT 1 the nearest one will be taken.
That screenshot visualises the bounding-box:
// 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);
$sql = "SELECT gid, source, target, the_geom,
distance(the_geom, GeometryFromText(
'POINT(".$lonlat[0]." ".$lonlat[1].")', 900913)) AS dist
FROM ".TABLE."
WHERE the_geom && setsrid(
'BOX3D(".($lonlat[0]-200)."
".($lonlat[1]-200).",
".($lonlat[0]+200)."
".($lonlat[1]+200).")'::box3d, 900913)
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;
}
After that, the routing-algorithm is taken.
// 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
// echo $sql;
// Database connection and query
$dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER);
$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;
?>
An Example
Imagine the startPoint is: 900323.3927317789 6852401.794747721 and the endpoint is 900912.1967151827 6852354.021605052
The result of the first SQL-Requests for the StartPoint will be the geom with the 7289 and the source-value 486, target-value: 43 For the endPoint: Gid:8298, Source: 4490, Target: 449
Then The SQL-Command tor e.g. Shortest Path Dijkstra starts. The result in this case is:
gid wkt length id
---- ---------------------------------------------------------------------------------------------------------------------- ---------------- -------
738 MULTILINESTRING((900623.048836333 6852579.25744262,900636.485098872 6852566.95487873,900770.11301562 6852342.72756191)) 279.243241240336 738
8298 MULTILINESTRING((900883.369465553 6852397.46895301,900923.778440711 6852337.886752,900926.539164082 685231 etc......) 102.503920838894 8298
8277 MULTILINESTRING((900822.032426126 6852374.6842597,900796.061588924 6852358.70589498,900770.11301562 6852342.72756191)) 60.9660221463712 8277
8281 MULTILINESTRING((900883.369465553 6852397.46895301,900852.68981389 6852386.0765983,900822.032426126 6852374.6842597)) 65.4322146538341 8281
The succesfull routing-screenshot looks like:
The routing works!!!
… but as you see the visualized route doesn`t go till the green markers …