[postgis-users] Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0
Stephen Woodbridge
woodbri at swoodbridge.com
Thu Jan 9 21:10:04 PST 2014
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 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>
> 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