[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Stephen Woodbridge
woodbri at swoodbridge.com
Thu Jan 9 05:45:41 PST 2014
So looks like progress. Now you need to post what failed to run and what
the errors were. Also it sounds like pgRouting is working ok since you
can display things in qgis so the problem at thispoint sounds more like
JavaScript issues or issues with OpenLayers configuration.
Are you using FireFox and Firebug? With that you can see your exact ajax
requests and responses.
-Steve
On 1/9/2014 4:49 AM, Emmanuel Adegboye wrote:
> 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
> <mailto: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
> <mailto: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 <mailto: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
> <mailto: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
> <mailto:daniel.kastl at georepublic.de>
> Web: http://georepublic.de <http://georepublic.de/>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list