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" <[email protected]
<mailto:[email protected]>> 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: [email protected] <mailto:[email protected]>
Web: http://georepublic.de <http://georepublic.de/>
_______________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
