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