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:

../../_images/routingfrage_1.png
// 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:

../../_images/routing_works.png

The routing works!!!

… but as you see the visualized route doesn`t go till the green markers …