<div dir="ltr">Hello Stephen.<div><br></div><div>I really appreciate your response.<br><div class="gmail_extra"><br></div><div class="gmail_extra">Yes, I am able to enter a sql query and get pgrouting to return a route.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">Thanks for the modified code. I initially got a syntax error on Lines 29 and 35:</div><div class="gmail_extra"><br></div><div class="gmail_extra"><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">
FROM quote_ident(tbl || '_vertices_pgr' )<br> ^</blockquote><div class="gmail_extra">But I figured that the quotation marks on the '_vertices_pgr' was the issue, so I removed them and it ran fine.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">So now, I'm able to create a new layer using the SQL view in Geoserver except that I now get an error when I try to compute the bounding box from data. I think the following lines from the error report might be a pointer:</div>
<div class="gmail_extra"><br></div><div class="gmail_extra"><span style="color:rgb(38,37,35);font-family:monospace;font-size:10px;line-height:14.625px;white-space:pre">java.lang.RuntimeException: java.io.IOException: Error occured calculating bounds</span><br>
</div><div class="gmail_extra"><span style="color:rgb(38,37,35);font-family:monospace;font-size:10px;line-height:14.625px;white-space:pre">Caused by: java.io.IOException: Error occured calculating bounds</span><span style="color:rgb(38,37,35);font-family:monospace;font-size:10px;line-height:14.625px;white-space:pre"><br>
</span></div><div class="gmail_extra"><span style="color:rgb(38,37,35);font-family:monospace;font-size:10px;line-height:14.625px;white-space:pre">Caused by: org.postgresql.util.PSQLException: ERROR: column "tbl" does not exist
Where: PL/pgSQL function pgr_fromatob(character varying,double precision,double precision,double precision,double precision) line 11 at EXECUTE statement</span><span style="color:rgb(38,37,35);font-family:monospace;font-size:10px;line-height:14.625px;white-space:pre"><br>
</span></div><div class="gmail_extra"><br></div><div class="gmail_extra">Isn't "quote_ident(tbl || _vertices_pgr)" supposed to refer to the table that the SELECT statement queries? How come the error refers to "tbl" as a column?</div>
<div class="gmail_extra"><br></div><div class="gmail_extra"> In the mean time, I will also start studying the suggested documentation.</div><div class="gmail_extra"><br></div><div class="gmail_extra">Regards,</div><div class="gmail_extra">
<br></div><div class="gmail_extra">Emmanuel</div><br><div class="gmail_quote">On 10 January 2014 06:10, Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>></span> 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 Emmanuel,<br>
<br>
Please do not take offense at our comments. We when people have problems, we only see what is posted and have to infer a lot based on that to try and help. We don't always get it right, so apologies if we didn't.<div class="im">
<br>
<br>
On 1/9/2014 10:29 PM, 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 Stephen, Daniel and all.<br>
<br>
Thank you very much for your candid comments and taking out time to<br>
respond to the issues I raised.<br>
<br>
Even though it probably appears so, I am not just copying random pieces<br>
of code. The workshop builds on previous versions and I have tried to<br>
follow it as closely as possible and only make modifications based on<br>
the data I am working with.<br>
<br>
I am not working with OSM data, instead I started with shapefiles loaded<br>
as a table (eastlegon) into a postgis database (which I named routing).<br>
My data is also entirely in ESPG 4326 and I am working from a windows<br>
machine but was able to install pgrouting easily using the installer at<br>
<a href="http://winnie.postgis.net/download/pg2/buildbot/postgis-bundle-pg92x32-setup-2.1.1-1.exe" target="_blank">http://winnie.postgis.net/<u></u>download/pg2/buildbot/postgis-<u></u>bundle-pg92x32-setup-2.1.1-1.<u></u>exe</a><br>
to my already existing PostgreSQL database. I loaded the data, added the<br>
additional columns, ran the topology function, added indices, ran<br>
queries using the algorithms (bearing in mind my table name) and I got<br>
results similar to the workshop.<br>
</blockquote>
<br></div>
Ok, this sounds great and is sounds like you have the pgrouting part all working. So you can enter a sql query and get pgrouting to return a route. Is that correct?<div class="im"><br>
<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">
All of this I fully understand and I've done it more than once just to<br>
be sure I am not making any mistakes so much that I can almost do it in<br>
my sleep. However, if you advise that I start again from scratch, create<br>
a database 'pgrouting-workshop', and table 'ways' instead of my existing<br>
setup, I will!<br>
</blockquote>
<br></div>
No not necessarily, but if you are having a problem with the next step and have not run through the tutorial, I only thought that it might be helpful.<br>
<br>
The tutorial works fine using OSM data but as you can see that does not make the same table structure that building it from shapefiles does because osm2pgrouting creates the topology directly and for the shapefile you have to use the sql command to create the topology which is why the table names are different.<div class="im">
<br>
<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">
Everything works fine till I get to the wrappers part of the workshop.<br>
The one with link Daniel shared. I must admit, I've tried to understand<br>
the code, and I'm still working on it, and that's why it appears I<br>
simply copy it.<br>
</blockquote>
<br></div>
Yeah, I understand, it is hard to<div class="im"><br>
<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">
Two simple questions I now have are:<br>
1) Is there any existing resource that explains (as straightforward as<br>
possible) the basics of how to write a wrapper from scratch for a<br>
beginner like me?<br>
</blockquote>
<br></div>
No not really because the wrapper purpose to to solve some problem like take you input, manipulate it, solve the graph, and manipulate the results, etc.<br>
<br>
There are a few pieces to writing a wrapper:<br>
<br>
1. knowing plpgsql and sql<br>
see: <a href="http://www.postgresql.org/docs/9.2/static/plpgsql.html" target="_blank">http://www.postgresql.org/<u></u>docs/9.2/static/plpgsql.html</a><br>
2. knowing postgis functions and what they do<br>
3. knowing pgrouting and the various functions and utilites<br>
4. identifying your inputs and output and then using the tools above to create your wrapper.<div class="im"><br>
<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">
2) Do I actually need to write custom wrappers (based on my dataset) to<br>
get my application to work? All I'm simply trying to do is create a<br>
simple application that allows users to set a start and end point,<br>
calculate route using at least dijkstra algorithm and display the<br>
results as a new layer, all in a web browser. If I do, how best do you<br>
advise I go about it. If not, how best can I build on the workshop to<br>
achieve my goal.<br>
</blockquote>
<br></div>
For the most part, you should be able to create a wrapper that deals with your work flow and reuse it for most of your applications with little on no tweaks. And once you have a handle on what the tutorial wrapper does you should be able to work with that and modify it for your needs.<br>
<br>
I think that you can adapt the pgr_fromatob() wrapper to your needs. Start with the following, Copy and paste into a pgadmin SQL window and run it on you database to load it, I made a change to it that might work for you.<br>
<br>
-Steve<br>
<br>
--DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision,<br>
-- double precision, double precision);<br>
<br>
CREATE OR REPLACE FUNCTION pgr_fromAtoB(<br>
IN tbl varchar,<br>
IN x1 double precision,<br>
IN y1 double precision,<br>
IN x2 double precision,<br>
IN y2 double precision,<br>
OUT seq integer,<br>
OUT gid integer,<br>
OUT name text,<br>
OUT heading double precision,<br>
OUT cost double precision,<br>
OUT geom geometry<br>
)<br>
RETURNS SETOF record AS<br>
$BODY$<br>
DECLARE<br>
sql text;<br>
rec record;<br>
source integer;<br>
target integer;<br>
point integer;<br>
<br>
BEGIN<br>
-- Find nearest node<br>
EXECUTE 'SELECT id::integer<br>
FROM quote_ident(tbl || '_vertices_pgr')<br>
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('<br>
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;<br>
source := <a href="http://rec.id" target="_blank">rec.id</a>;<br>
<br>
EXECUTE 'SELECT id::integer<br>
FROM quote_ident(tbl || '_vertices_pgr')<br>
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('<br>
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;<br>
target := <a href="http://rec.id" target="_blank">rec.id</a>;<br>
<br>
-- Shortest path query (TODO: limit extent by BBOX)<br>
seq := 0;<br>
sql := 'SELECT gid, the_geom, name, cost, source, target,<br>
ST_Reverse(the_geom) AS flip_geom FROM ' ||<br>
'pgr_dijkstra(''SELECT gid as id, source::int, target::int, '<br>
|| 'length::float AS cost FROM '<br>
|| quote_ident(tbl) || ''', '<br>
|| source || ', ' || target<br>
|| ' , false, false), '<br>
|| quote_ident(tbl) || ' WHERE id2 = gid ORDER BY seq';<br>
<br>
-- Remember start point<br>
point := source;<br>
<br>
FOR rec IN EXECUTE sql<br>
LOOP<br>
-- Flip geometry (if required)<br>
IF ( point != rec.source ) THEN<br>
rec.the_geom := rec.flip_geom;<br>
point := rec.source;<br>
ELSE<br>
point := rec.target;<br>
END IF;<br>
<br>
-- Calculate heading (simplified)<br>
EXECUTE 'SELECT degrees( ST_Azimuth(<br>
ST_StartPoint(''' || rec.the_geom::text || '''),<br>
ST_EndPoint(''' || rec.the_geom::text || ''') ) )'<br>
INTO heading;<br>
<br>
-- Return record<br>
seq := seq + 1;<br>
gid := rec.gid;<br>
name := <a href="http://rec.name" target="_blank">rec.name</a>;<br>
cost := rec.cost;<br>
geom := rec.the_geom;<br>
RETURN NEXT;<br>
END LOOP;<br>
RETURN;<br>
END;<br>
$BODY$<br>
LANGUAGE 'plpgsql' VOLATILE STRICT;<br>
<br>
<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"><div class="im">
Thanks once again for your time. Without you guys and the work you've<br>
done I would not even know where to get started. I really appreciate.<br>
<br>
Regards,<br>
<br>
Emmanuel Adegboye<br>
<br>
On Jan 10, 2014 1:18 AM, "Daniel Kastl" <<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.de</a><br></div><div><div class="h5">
<mailto:<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.de</a>><u></u>> wrote:<br>
<br>
<br>
I tried running thr pgr_fromAtoB wrapper function in pgAdmin<br>
and I<br>
initially get an error:<br>
<br>
ERROR: relation "vertices_tmp" does not exist<br>
SQL state: 42P01<br>
Context: PL/pgSQL function pgr_fromatob(character<br>
varying,double<br>
precision,double precision,double precision,double<br>
precision) line<br>
11 at EXECUTE statement<br>
<br>
<br>
I think Daniel will have to answer this. pgr_fromatob() must be<br>
a wrapper as that is part of the tutorial because it is not part<br>
of the pgrouting release.<br>
<br>
<br>
This is the function:<br>
<a href="http://workshop.pgrouting.org/chapters/wrapper.html#route-between-lat-lon-points-and-return-ordered-geometry-with-heading" target="_blank">http://workshop.pgrouting.org/<u></u>chapters/wrapper.html#route-<u></u>between-lat-lon-points-and-<u></u>return-ordered-geometry-with-<u></u>heading</a><br>
<br>
As Steve mentioned, you will not be successful to copy pieces of<br>
code (randomly) without understanding what actually is going on there.<br>
<br>
The workshop really tries to explain everything, and I don't think<br>
it makes sense to just copy the text from the link above. It should<br>
be all explained. If you're missing some information, or something<br>
is unclear, then feel free to ask.<br>
<br>
Daniel<br>
<br>
<br>
<br>
<br>
<br>
<br>
--<br>
Georepublic UG & Georepublic Japan<br></div></div>
eMail: <a href="mailto:daniel.kastl@georepublic.de" target="_blank">daniel.kastl@georepublic.de</a> <mailto:<a href="mailto:daniel.kastl@georepublic.de" target="_blank">daniel.kastl@<u></u>georepublic.de</a>><div class="im">
<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></div>
______________________________<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><div class="im"><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>
</div></blockquote><div class=""><div class="h5">
<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>
</div></div></blockquote></div><br></div></div></div>