[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