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 <[email protected]
<mailto:[email protected]>> 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" <[email protected]
<mailto:[email protected]>
<mailto:[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
<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]>
<mailto:daniel.kastl@__georepublic.de
<mailto:[email protected]>>
Web: http://georepublic.de <http://georepublic.de/>
_________________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
<mailto:postgis-users@lists.__osgeo.org
<mailto:[email protected]>>
http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users
<http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
_________________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users
<http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
_________________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users
<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
