[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Emmanuel Adegboye
eaadegboye at googlemail.com
Sat Jan 4 13:20:31 PST 2014
Hello Daniel.
Thanks for your response.
I will take a look at the workshop again but I got stuck at some point
trying to write wrapper functions. I will post questions if I get stuck,
your answers would be invaluable.
Regards,
Emmanuel Adegboye
On 4 January 2014 04:38, Daniel Kastl <daniel at georepublic.de> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140104/11f8fca5/attachment.html>
More information about the postgis-users
mailing list