[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Emmanuel Adegboye
eaadegboye at googlemail.com
Thu Jan 9 07:06:06 PST 2014
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
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."
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: 
> ^
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
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>
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>> 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
>>
>>
> _______________________________________________
> 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/2505fb29/attachment.html>
More information about the postgis-users
mailing list