<div dir="ltr"><p>Hi Stephen.</p><p>Thanks for your response.</p>
<p>I tried running thr pgr_fromAtoB wrapper function in pgAdmin and I initially get an error:</p><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
ERROR: relation "vertices_tmp" does not exist<br>SQL state: 42P01<br>Context: PL/pgSQL function pgr_fromatob(character varying,double precision,double precision,double precision,double precision) line 11 at EXECUTE statement</blockquote>
<div><br></div><p>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."</p>
<p>I saved this query as sql and tried installing in psql but I get the error:</p><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
C:\Users\GWU>psql -U postgres -d routing -f C:\Users\GWU\Desktop\HGT\wrapper3.sq<br>l<br>Password for user postgres:<br>psql:C:/Users/GWU/Desktop/HGT/wrapper3.sql:79: ERROR: syntax error at or near "<br>"<br>
LINE 1: <br> ^</blockquote><p>Why does the query run but fail to install?</p><p>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:</p>
<ul class="" style="margin:0.2em;padding:0px;border:0px;font-size:12px;font-family:Tahoma,'Lucida Sans Unicode','Lucida Grande',Verdana,sans-serif;vertical-align:baseline;list-style-type:none;color:rgb(38,37,35);line-height:18px">
<li class="" style="margin:0px;padding:0px;border:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;color:rgb(255,255,255);background-color:rgb(242,78,80)"><span class="" style="margin:0px;padding:0px;border:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline">ERROR: column "’eastlegon’" does not exist Position: 84</span></li>
</ul><div><br></div><div><font color="#000000" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px">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.</span></font></div>
<div><font color="#000000" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px"><br></span></font></div><div><font color="#000000" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px">Regards,</span></font></div>
<div><font color="#000000" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px"><br></span></font></div><div><font color="#000000" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px">Emmanuel</span></font></div>
<font face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif" color="#000000"><span style="font-size:12px;line-height:18px"><br></span></font><div><font color="#ffffff" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px"><br>
</span></font></div><div><font color="#ffffff" face="Tahoma, Lucida Sans Unicode, Lucida Grande, Verdana, sans-serif"><span style="font-size:12px;line-height:18px"><br></span></font></div><div class="gmail_quote">On Jan 9, 2014 2:48 PM, "Stephen Woodbridge" <<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>> wrote:<br type="attribution">
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
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.<br>
<br>
Are you using FireFox and Firebug? With that you can see your exact ajax requests and responses.<br>
<br>
-Steve<br>
<br>
On 1/9/2014 4:49 AM, Emmanuel Adegboye wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
Hello.<br>
<br>
I'm now working with the 2013 workshop now and I have my tables<br>
structured as follows:<br>
<br>
routing=# \d<br>
List of relations<br>
Schema | Name | Type | Owner<br>
--------+---------------------<u></u>----------+----------+--------<u></u>--<br>
public | eastlegon | table | postgres<br>
public | eastlegon_gid_seq | sequence | postgres<br>
public | eastlegon_vertices_pgr | table | postgres<br>
public | eastlegon_vertices_pgr_id_seq | sequence | postgres<br>
public | eastlegonboundary | table | postgres<br>
public | eastlegonboundary_gid_seq | sequence | postgres<br>
public | geography_columns | view | postgres<br>
public | geometry_columns | view | postgres<br>
public | raster_columns | view | postgres<br>
public | raster_overviews | view | postgres<br>
public | route | table | postgres<br>
public | spatial_ref_sys | table | postgres<br>
(12 rows)<br>
<br>
<br>
<br>
routing=# \d eastlegon<br>
Table "public.eastlegon"<br>
Column | Type |<br>
Modifiers<br>
<br>
--------------+---------------<u></u>------------+-----------------<u></u>--------------------<br>
--------------------<br>
gid | integer | not null default<br>
nextval('eastlegon_<br>
gid_seq'::regclass)<br>
length | double precision |<br>
class_id | integer | not null<br>
postcode | character varying(254) |<br>
name | character varying(100) |<br>
the_geom | geometry(LineString,4326) |<br>
source | integer |<br>
target | integer |<br>
cost_len | double precision |<br>
cost_time | double precision |<br>
rcost_len | double precision |<br>
rcost_time | double precision |<br>
x1 | double precision |<br>
y1 | double precision |<br>
x2 | double precision |<br>
y2 | double precision |<br>
to_cost | double precision |<br>
rule | text |<br>
isolated | integer |<br>
reverse_cost | double precision |<br>
Indexes:<br>
"eastlegon_pkey" PRIMARY KEY, btree (gid)<br>
"eastlegon_source_idx" btree (source)<br>
"eastlegon_target_idx" btree (target)<br>
"eastlegon_the_geom_gidx" gist (the_geom)<br>
"source_idx" btree (source)<br>
"target_idx" btree (target)<br>
<br>
<br>
My aim is simply to follow the workshop and display my routes using at<br>
least dijkstra algorithm in openlayers 2 (or 3 if it's not too<br>
complicated). I try installing the wrappers in the workshop but I get<br>
syntax errors.<br>
<br>
I'm sure my routing is properly set up since I can display routes using<br>
the pgRouting layer plugin for QGIS. I also have the layer stored<br>
properly in Geoserver. I can already display the layers as a WMS layer<br>
using openlayers. My only challenge is to display the route dynamically<br>
in a web browser.<br>
<br>
I need pointers in the right direction. My skills are still basic<br>
though, but I'm willing to learn.<br>
<br>
Thanks a lot for your help.<br>
<br>
Emmanuel<br>
<br>
<br>
On 4 January 2014 22:20, Emmanuel Adegboye <<a href="mailto:eaadegboye@googlemail.com" target="_blank">eaadegboye@googlemail.com</a><br>
<mailto:<a href="mailto:eaadegboye@googlemail.com" target="_blank">eaadegboye@googlemail.<u></u>com</a>>> wrote:<br>
<br>
Hello Daniel.<br>
<br>
Thanks for your response.<br>
<br>
I will take a look at the workshop again but I got stuck at some<br>
point trying to write wrapper functions. I will post questions if I<br>
get stuck, your answers would be invaluable.<br>
<br>
Regards,<br>
<br>
Emmanuel Adegboye<br>
<br>
<br>
On 4 January 2014 04:38, Daniel Kastl <<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.de</a><br>
<mailto:<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.de</a>><u></u>> wrote:<br>
<br>
Hi Emmanuel,<br>
<br>
The workshop code you're posting is not the current one anymore<br>
and won't work anymore with pgRouting 2.0.<br>
<br>
If you want to use the "old" PHP function, then you need to<br>
modify it:<br>
<br>
* Write your own plpgsql wrapper function. You can take a look<br>
at the examples in the new workshop:<br>
<a href="http://workshop.pgrouting.org/chapters/wrapper.html" target="_blank">http://workshop.pgrouting.org/<u></u>chapters/wrapper.html</a><br>
* Change the function name, arguments, etc. according to your<br>
custom function in the PHP script.<br>
<br>
Daniel<br>
<br>
<br>
<br>
On Sat, Jan 4, 2014 at 6:43 AM, Emmanuel Adegboye<br>
<<a href="mailto:eaadegboye@googlemail.com" target="_blank">eaadegboye@googlemail.com</a> <mailto:<a href="mailto:eaadegboye@googlemail.com" target="_blank">eaadegboye@googlemail.<u></u>com</a>>><br>
wrote:<br>
<br>
I'm having issues getting the pgrouting workshop to work on<br>
Postgresql 9.2/PostGIS 2.11 and pgrouting 2.0 on windows.<br>
<br>
How can I rewrite the following php/sql code and make it<br>
compatible with my version:<br>
<br>
<br>
<br>
|<?php<br>
<br>
// Database connection settings<br>
define("PG_DB" , "routing");<br>
define("PG_HOST", "localhost");<br>
define("PG_USER", "postgres");<br>
define("PG_PORT", "5432");<br>
define("PG_PASSWD", "*******");<br>
define("TABLE", "eastlegon");<br>
<br>
$counter= $pathlength= 0;<br>
<br>
// Retrieve start point<br>
$start= split('',$_REQUEST['<u></u>startpoint']);<br>
$startPoint= array($start[0], $start[1]);<br>
<br>
// Retrieve end point<br>
$end= split('',$_REQUEST['<u></u>finalpoint']);<br>
$endPoint= array($end[0], $end[1]);<br>
<br>
// Find the nearest edge<br>
$startEdge= findNearestEdge($startPoint);<br>
$endEdge= findNearestEdge($endPoint);<br>
<br>
// FUNCTION findNearestEdge<br>
function findNearestEdge($lonlat) {<br>
<br>
// Connect to database<br>
$con= pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWD);<br>
<br>
$sql= "SELECT gid, source, target, the_geom,<br>
distance(the_geom, GeometryFromText(<br>
'POINT(".$lonlat[0]."".$<u></u>lonlat[1].")', 4326)) AS dist<br>
FROM ".TABLE."<br>
WHERE the_geom && setsrid(<br>
'BOX3D(".($lonlat[0]-200)."<br>
".($lonlat[1]-200).",<br>
".($lonlat[0]+200)."<br>
".($lonlat[1]+200).")'::box3d, 4326)<br>
ORDER BY dist LIMIT 1";<br>
<br>
$query= pg_query($con,$sql);<br>
<br>
$edge['gid'] = pg_fetch_result($query, 0, 0);<br>
$edge['source'] = pg_fetch_result($query, 0, 1);<br>
$edge['target'] = pg_fetch_result($query, 0, 2);<br>
$edge['the_geom'] = pg_fetch_result($query, 0, 3);<br>
<br>
// Close database connection<br>
pg_close($con);<br>
<br>
return $edge;<br>
}<br>
<br>
// Select the routing algorithm<br>
switch($_REQUEST['method']) {<br>
<br>
case 'SPD' : // Shortest Path Dijkstra<br>
<br>
$sql= "SELECT rt.gid, AsText(rt.the_geom) AS wkt,<br>
length(rt.the_geom) AS length, ".TABLE.".id<br>
FROM ".TABLE.",<br>
(SELECT gid, the_geom<br>
FROM dijkstra_sp_delta(<br>
'".TABLE."',<br>
".$startEdge['source'].",<br>
".$endEdge['target'].",<br>
3000)<br>
) as rt<br>
WHERE ".TABLE.".gid=rt.gid;";<br>
break;<br>
<br>
case 'SPA' : // Shortest Path A*<br>
<br>
$sql= "SELECT rt.gid, AsText(rt.the_geom) AS wkt,<br>
length(rt.the_geom) AS length, ".TABLE.".id<br>
FROM ".TABLE.",<br>
(SELECT gid, the_geom<br>
FROM astar_sp_delta(<br>
'".TABLE."',<br>
".$startEdge['source'].",<br>
".$endEdge['target'].",<br>
3000)<br>
) as rt<br>
WHERE ".TABLE.".gid=rt.gid;";<br>
break;<br>
<br>
case 'SPS' : // Shortest Path Shooting*<br>
<br>
$sql= "SELECT rt.gid, AsText(rt.the_geom) AS wkt,<br>
length(rt.the_geom) AS length, ".TABLE.".id<br>
FROM ".TABLE.",<br>
(SELECT gid, the_geom<br>
FROM shootingstar_sp(<br>
'".TABLE."',<br>
".$startEdge['gid'].",<br>
".$endEdge['gid'].",<br>
3000, 'length', false, false)<br>
) as rt<br>
WHERE ".TABLE.".gid=rt.gid;";<br>
break;<br>
<br>
} // close switch<br>
<br>
// Database connection and query<br>
$dbcon= pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWD);<br>
<br>
$query= pg_query($dbcon,$sql);<br>
<br>
// Return route as XML<br>
$xml= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n";<br>
$xml .= "<route>\n";<br>
<br>
// Add edges to XML file<br>
while($edge=pg_fetch_assoc($<u></u>query)) {<br>
<br>
$pathlength += $edge['length'];<br>
<br>
$xml .= "\t<edge id='".++$counter."'>\n";<br>
$xml .= "\t\t<id>".$edge['id']."</id>\<u></u>n";<br>
$xml .= "\t\t<wkt>".$edge['wkt']."</<u></u>wkt>\n";<br>
$xml .= "\t\t<length>".round(($<u></u>pathlength/1000),3)."</length><u></u>\n";<br>
$xml .= "\t</edge>\n";<br>
}<br>
<br>
$xml .= "</route>\n";<br>
<br>
// Close database connection<br>
pg_close($dbcon);<br>
<br>
// Return routing result<br>
header('Content-type: text/xml',true);<br>
echo $xml;<br>
<br>
?>|<br>
<br>
Thanks,<br>
<br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.<u></u>osgeo.org</a>><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
<br>
<br>
<br>
<br>
--<br>
Georepublic UG & Georepublic Japan<br>
eMail: <a href="mailto:daniel.kastl@georepublic.de" target="_blank">daniel.kastl@georepublic.de</a><br>
<mailto:<a href="mailto:daniel.kastl@georepublic.de" target="_blank">daniel.kastl@<u></u>georepublic.de</a>><br>
Web: <a href="http://georepublic.de" target="_blank">http://georepublic.de</a> <<a href="http://georepublic.de/" target="_blank">http://georepublic.de/</a>><br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.<u></u>osgeo.org</a>><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
<br>
<br>
<br>
<br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
<br>
</blockquote>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
</blockquote></div>
</div>