[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Emmanuel Adegboye
eaadegboye at googlemail.com
Thu Jan 9 01:49:11 PST 2014
Hello.
I'm now working with the 2013 workshop now and I have my tables structured
as follows:
routing=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+----------+----------
public | eastlegon | table | postgres
public | eastlegon_gid_seq | sequence | postgres
public | eastlegon_vertices_pgr | table | postgres
public | eastlegon_vertices_pgr_id_seq | sequence | postgres
public | eastlegonboundary | table | postgres
public | eastlegonboundary_gid_seq | sequence | postgres
public | geography_columns | view | postgres
public | geometry_columns | view | postgres
public | raster_columns | view | postgres
public | raster_overviews | view | postgres
public | route | table | postgres
public | spatial_ref_sys | table | postgres
(12 rows)
routing=# \d eastlegon
Table "public.eastlegon"
Column | Type | Modifiers
--------------+---------------------------+-------------------------------------
--------------------
gid | integer | not null default
nextval('eastlegon_
gid_seq'::regclass)
length | double precision |
class_id | integer | not null
postcode | character varying(254) |
name | character varying(100) |
the_geom | geometry(LineString,4326) |
source | integer |
target | integer |
cost_len | double precision |
cost_time | double precision |
rcost_len | double precision |
rcost_time | double precision |
x1 | double precision |
y1 | double precision |
x2 | double precision |
y2 | double precision |
to_cost | double precision |
rule | text |
isolated | integer |
reverse_cost | double precision |
Indexes:
"eastlegon_pkey" PRIMARY KEY, btree (gid)
"eastlegon_source_idx" btree (source)
"eastlegon_target_idx" btree (target)
"eastlegon_the_geom_gidx" gist (the_geom)
"source_idx" btree (source)
"target_idx" btree (target)
My aim is simply to follow the workshop and display my routes using at
least dijkstra algorithm in openlayers 2 (or 3 if it's not too
complicated). I try installing the wrappers in the workshop but I get
syntax errors.
I'm sure my routing is properly set up since I can display routes using the
pgRouting layer plugin for QGIS. I also have the layer stored properly in
Geoserver. I can already display the layers as a WMS layer using
openlayers. My only challenge is to display the route dynamically in a web
browser.
I need pointers in the right direction. My skills are still basic though,
but I'm willing to learn.
Thanks a lot for your help.
Emmanuel
On 4 January 2014 22:20, Emmanuel Adegboye <eaadegboye at googlemail.com>wrote:
> 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/20140109/b08ec986/attachment.html>
More information about the postgis-users
mailing list