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" <[email protected]> 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 <[email protected] >> <mailto:[email protected]>> 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 <[email protected] >> <mailto:[email protected]>> 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 >> <[email protected] <mailto:[email protected]>> >> 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 >> [email protected] >> <mailto:[email protected]> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> >> >> -- >> Georepublic UG & Georepublic Japan >> eMail: [email protected] >> <mailto:[email protected]> >> Web: http://georepublic.de <http://georepublic.de/> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] <mailto:postgis-users@lists. >> osgeo.org> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
