[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0

Stephen Woodbridge woodbri at swoodbridge.com
Sat Jan 11 06:29:32 PST 2014


On 1/11/2014 2:52 AM, Emmanuel Adegboye wrote:
> 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.

Actually removing them is the wrong thing to do. you need to put two 
single quotes in each place to represent a quote in a quoted string. So 
that line should be:

        FROM quote_ident(tbl || ''_vertices_pgr'' )

-Steve

> 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
> <mailto: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
>         <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
>     <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 <http://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 <http://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 <http://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>
>         <mailto: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
>         <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 at georepublic.de>
>         <mailto: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>
>         <mailto:postgis-users at lists.__osgeo.org
>         <mailto:postgis-users at lists.osgeo.org>>
>         http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
>
>
>         _________________________________________________
>         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 <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
>     _________________________________________________
>     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
>     <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
>



More information about the postgis-users mailing list