[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0

[Daniel Kastl]

Hi Emmanuel,

The workshop code you're posting is not the current one anymore and won't
work anymore with pgRouting 2.0.

If you want to use the "old" PHP function, then you need to modify it:

* Write your own plpgsql wrapper function. You can take a look at the
examples in the new workshop:
http://workshop.pgrouting.org/chapters/wrapper.html
* Change the function name, arguments, etc. according to your custom
function in the PHP script.

Daniel



On Sat, Jan 4, 2014 at 6:43 AM, Emmanuel Adegboye <eaadegboye at googlemail.com
> wrote:

> I'm having issues getting the pgrouting workshop to work on Postgresql
> 9.2/PostGIS 2.11 and pgrouting 2.0 on windows.
>
> How can I rewrite the following php/sql code and make it compatible with
> my version:
>
>
> <?php
>
>   // Database connection settings
>   define("PG_DB"  , "routing");
>   define("PG_HOST", "localhost");
>   define("PG_USER", "postgres");
>   define("PG_PORT", "5432");
>    define("PG_PASSWD",   "*******");
>   define("TABLE",   "eastlegon");
>
>   $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." user=".PG_USER." password=".PG_PASSWD);
>
>     $sql = "SELECT gid, source, target, the_geom,
>              distance(the_geom, GeometryFromText(
>                   'POINT(".$lonlat[0]." ".$lonlat[1].")', 4326)) AS dist
>             FROM ".TABLE."
>             WHERE the_geom && setsrid(
>                   'BOX3D(".($lonlat[0]-200)."
>                          ".($lonlat[1]-200).",
>                          ".($lonlat[0]+200)."
>                          ".($lonlat[1]+200).")'::box3d, 4326)
>             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;
>   }
>
>   // 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
>
>   // Database connection and query
>   $dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWD);
>
>   $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;
> ?>
>
> Thanks,
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



-- 
Georepublic UG & Georepublic Japan
eMail: daniel.kastl at georepublic.de
Web: http://georepublic.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140104/35f838f8/attachment.html>