[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Stephen Woodbridge
woodbri at swoodbridge.com
Thu Jan 9 07:16:45 PST 2014
On 1/9/2014 10:06 AM, Emmanuel Adegboye wrote:
> Hi Stephen.
>
> Thanks for your response.
>
> I tried running thr pgr_fromAtoB wrapper function in pgAdmin and I
> initially get an error:
>
> ERROR: relation "vertices_tmp" does not exist
> SQL state: 42P01
> Context: PL/pgSQL function pgr_fromatob(character varying,double
> precision,double precision,double precision,double precision) line
> 11 at EXECUTE statement
>
I think Daniel will have to answer this. pgr_fromatob() must be a
wrapper as that is part of the tutorial because it is not part of the
pgrouting release.
> Then I changed the table name in the sql code to eastlegon_vertices_pgr.
> This is the table that stores the vertices and I assume that's the one
> been referred to. This returns a message "Query returned successfully
> with no result in 111 ms."
This sounds reasonable. But, it seems like you are copying pasting
random bits of the tutorial into your application trying to get things
to work. I think you would be more successful if you followed the
tutorial from start to end so you understand how all the pieces work and
look at the tables at each step along the way. Then you will have a
better idea how to adapt that process to your needs.
> I saved this query as sql and tried installing in psql but I get the error:
>
> C:\Users\GWU>psql -U postgres -d routing -f
> C:\Users\GWU\Desktop\HGT\wrapper3.sq
> l
> Password for user postgres:
> psql:C:/Users/GWU/Desktop/HGT/wrapper3.sql:79: ERROR: syntax error
> at or near "
> "
> LINE 1: 
> ^
This is a copy and paste issues. You have non-ascii characters in the
file that psql can not understand.
> Why does the query run but fail to install?
>
> Following also to the SQL view parameters (Geoserver) part of the
> workshop, I got to the part of changing the validation regular
> expression but once I hit refresh on the Attributes list, I don't get
> any attribute. Instead, I get an error in geoserver:
>
> * ERROR: column "’eastlegon’" does not exist Position: 84
Looks like you have too many quote marks.
-Steve
> This is the part where i get stuck. I really appreciate your help so
> far. I'm sure there's something I'm probably missing but I need your advise.
>
> Regards,
>
> Emmanuel
>
>
>
> On Jan 9, 2014 2:48 PM, "Stephen Woodbridge" <woodbri at swoodbridge.com
> <mailto:woodbri at swoodbridge.com>> wrote:
>
> 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>
> <mailto: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>
> <mailto: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
> <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>
> <mailto: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>
> <mailto:postgis-users at lists.__osgeo.org
> <mailto:postgis-users at lists.osgeo.org>>
> http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users <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>
> <mailto: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>
> <mailto:postgis-users at lists.__osgeo.org
> <mailto:postgis-users at lists.osgeo.org>>
> http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
>
>
>
> _________________________________________________
> 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 <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
> _________________________________________________
> 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
> <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