[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Emmanuel Adegboye
eaadegboye at googlemail.com
Fri Jan 10 23:52:23 PST 2014
Hello Stephen.
I really appreciate your response.
Yes, I am able to enter a sql query and get pgrouting to return a route.
Thanks for the modified code. I initially got a syntax error on Lines 29
and 35:
FROM quote_ident(tbl || '_vertices_pgr' )
> ^
But I figured that the quotation marks on the '_vertices_pgr' was the
issue, so I removed them and it ran fine.
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:
java.lang.RuntimeException: java.io.IOException: Error occured calculating
bounds
Caused by: java.io.IOException: Error occured calculating bounds
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
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?
In the mean time, I will also start studying the suggested documentation.
Regards,
Emmanuel
On 10 January 2014 06:10, Stephen Woodbridge <woodbri at swoodbridge.com>wrote:
> Hello Emmanuel,
>
> 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.
>
>
> On 1/9/2014 10:29 PM, Emmanuel Adegboye wrote:
>
>> Hello Stephen, Daniel and all.
>>
>> Thank you very much for your candid comments and taking out time to
>> respond to the issues I raised.
>>
>> Even though it probably appears so, I am not just copying random pieces
>> of code. The workshop builds on previous versions and I have tried to
>> follow it as closely as possible and only make modifications based on
>> the data I am working with.
>>
>> I am not working with OSM data, instead I started with shapefiles loaded
>> as a table (eastlegon) into a postgis database (which I named routing).
>> My data is also entirely in ESPG 4326 and I am working from a windows
>> machine but was able to install pgrouting easily using the installer at
>> http://winnie.postgis.net/download/pg2/buildbot/postgis-
>> bundle-pg92x32-setup-2.1.1-1.exe
>> to my already existing PostgreSQL database. I loaded the data, added the
>> additional columns, ran the topology function, added indices, ran
>> queries using the algorithms (bearing in mind my table name) and I got
>> results similar to the workshop.
>>
>
> 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?
>
>
> All of this I fully understand and I've done it more than once just to
>> be sure I am not making any mistakes so much that I can almost do it in
>> my sleep. However, if you advise that I start again from scratch, create
>> a database 'pgrouting-workshop', and table 'ways' instead of my existing
>> setup, I will!
>>
>
> 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.
>
> 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.
>
>
> Everything works fine till I get to the wrappers part of the workshop.
>> The one with link Daniel shared. I must admit, I've tried to understand
>> the code, and I'm still working on it, and that's why it appears I
>> simply copy it.
>>
>
> Yeah, I understand, it is hard to
>
>
> Two simple questions I now have are:
>> 1) Is there any existing resource that explains (as straightforward as
>> possible) the basics of how to write a wrapper from scratch for a
>> beginner like me?
>>
>
> 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.
>
> There are a few pieces to writing a wrapper:
>
> 1. knowing plpgsql and sql
> see: http://www.postgresql.org/docs/9.2/static/plpgsql.html
> 2. knowing postgis functions and what they do
> 3. knowing pgrouting and the various functions and utilites
> 4. identifying your inputs and output and then using the tools above to
> create your wrapper.
>
>
> 2) Do I actually need to write custom wrappers (based on my dataset) to
>> get my application to work? All I'm simply trying to do is create a
>> simple application that allows users to set a start and end point,
>> calculate route using at least dijkstra algorithm and display the
>> results as a new layer, all in a web browser. If I do, how best do you
>> advise I go about it. If not, how best can I build on the workshop to
>> achieve my goal.
>>
>
> 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.
>
> 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.
>
> -Steve
>
> --DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision,
> -- double precision, double precision);
>
> CREATE OR REPLACE FUNCTION pgr_fromAtoB(
> IN tbl varchar,
> IN x1 double precision,
> IN y1 double precision,
> IN x2 double precision,
> IN y2 double precision,
> OUT seq integer,
> OUT gid integer,
> OUT name text,
> OUT heading double precision,
> OUT cost double precision,
> OUT geom geometry
> )
> RETURNS SETOF record AS
> $BODY$
> DECLARE
> sql text;
> rec record;
> source integer;
> target integer;
> point integer;
>
> BEGIN
> -- Find nearest node
> EXECUTE 'SELECT id::integer
> FROM quote_ident(tbl || '_vertices_pgr')
> ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
> || x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
> source := rec.id;
>
> EXECUTE 'SELECT id::integer
> FROM quote_ident(tbl || '_vertices_pgr')
> ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
> || x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;
> target := rec.id;
>
> -- Shortest path query (TODO: limit extent by BBOX)
> seq := 0;
> sql := 'SELECT gid, the_geom, name, cost, source, target,
> ST_Reverse(the_geom) AS flip_geom FROM ' ||
> 'pgr_dijkstra(''SELECT gid as id, source::int,
> target::int, '
> || 'length::float AS cost FROM '
> || quote_ident(tbl) || ''', '
> || source || ', ' || target
> || ' , false, false), '
> || quote_ident(tbl) || ' WHERE id2 = gid
> ORDER BY seq';
>
> -- Remember start point
> point := source;
>
> FOR rec IN EXECUTE sql
> LOOP
> -- Flip geometry (if required)
> IF ( point != rec.source ) THEN
> rec.the_geom := rec.flip_geom;
> point := rec.source;
> ELSE
> point := rec.target;
> END IF;
>
> -- Calculate heading (simplified)
> EXECUTE 'SELECT degrees( ST_Azimuth(
> ST_StartPoint(''' || rec.the_geom::text ||
> '''),
> ST_EndPoint(''' || rec.the_geom::text ||
> ''') ) )'
> INTO heading;
>
> -- Return record
> seq := seq + 1;
> gid := rec.gid;
> name := rec.name;
> cost := rec.cost;
> geom := rec.the_geom;
> RETURN NEXT;
> END LOOP;
> RETURN;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE STRICT;
>
>
> Thanks once again for your time. Without you guys and the work you've
>> done I would not even know where to get started. I really appreciate.
>>
>> Regards,
>>
>> Emmanuel Adegboye
>>
>> On Jan 10, 2014 1:18 AM, "Daniel Kastl" <daniel at georepublic.de
>> <mailto:daniel at georepublic.de>> wrote:
>>
>>
>> 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.
>>
>>
>> This is the function:
>> http://workshop.pgrouting.org/chapters/wrapper.html#route-
>> between-lat-lon-points-and-return-ordered-geometry-with-heading
>>
>> As Steve mentioned, you will not be successful to copy pieces of
>> code (randomly) without understanding what actually is going on there.
>>
>> The workshop really tries to explain everything, and I don't think
>> it makes sense to just copy the text from the link above. It should
>> be all explained. If you're missing some information, or something
>> is unclear, then feel free to ask.
>>
>> Daniel
>>
>>
>>
>>
>>
>>
>> --
>> Georepublic UG & Georepublic Japan
>> eMail: daniel.kastl at georepublic.de <mailto:daniel.kastl@
>> 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/20140111/3409ccf6/attachment.html>
More information about the postgis-users
mailing list