0

I'm trying to prepare a dijkstra wrapper for Geoserver. I got a code from my professor and have to modify it so that it uses the dijkstra algorithm with the reverse cost argument. Can anyone please tell me what I'm doing wrong?

I get this error: Support for id,source,target columns only of type: integer. Support for Cost: double precision. Here's the code:

CREATE OR REPLACE FUNCTION pgr_fromatob( IN sche character varying, IN tbl character varying, 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, --nie wiem jak podac cost i reverse_cost do record OUT cost_reverse 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 osm_rowerowe_500_points_y ORDER BY geom <-> ST_GeometryFromText(''POINT(' || x1 || ' ' || y1 || ')'',2180) LIMIT 1' INTO rec; source := rec.id; EXECUTE 'SELECT id::integer FROM osm_rowerowe_500_points_y ORDER BY geom <-> ST_GeometryFromText(''POINT(' || x2 || ' ' || y2 || ')'',2180) LIMIT 1' INTO rec; target := rec.id; -- Shortest path query (TODO: limit extent by BBOX) seq := 0; sql := 'SELECT id, geom, czas, czas_reverse, source, target, ST_Reverse(geom) AS flip_geom FROM ' || 'pgr_dijkstra(''SELECT id as id, source::int, target::int, ' || 'czas::float AS cost, czas_reverse::float as reverse_cost FROM ' || quote_ident(sche) ||'.' || quote_ident(tbl) || ''', ' || source || ', ' || target || ' , false, true), '|| quote_ident(sche) ||'.'|| quote_ident(tbl) || ' WHERE id2 = id ORDER BY seq'; -- Remember start point point := source; FOR rec IN EXECUTE sql LOOP -- Flip geometry (if required) IF ( point != rec.source ) THEN rec.geom := rec.flip_geom; point := rec.source; ELSE point := rec.target; END IF; -- Calculate heading (simplified) EXECUTE 'SELECT degrees( ST_Azimuth( ST_StartPoint(''' || rec.geom::text || '''), ST_EndPoint(''' || rec.geom::text || ''') ) )' INTO heading; -- Return record seq := seq + 1; gid := rec.id; --name := rec.typ_d; cost := rec.czas; cost_reverse := rec.czas_reverse; geom := rec.geom; RETURN NEXT; END LOOP; RETURN; END; $BODY$ LANGUAGE plpgsql VOLATILE
Improve this question

1 Answer 1

Reset to default
0

Which version of pgrouting are you using. Dijkstra changed a lot between 2.0 and 2.1. The issue might be your id is possibly a bigint and you are using the old deprecated pgr_dijkstra function. Try doing id::integer.

Also in 2.1+, the reverse_cost true/false is no longer necessary as it's assumed if you provide reverse_cost column you want to use reverse_cost. Also in 2.1, if you are using the newer signature, the id, source, and target are allowed to be integer or big integer so the casting to int would also probably not be necessary.

BTW, have this covered in our book, currently available for purchase in pre-release form - http://locatepress.com/pgrouting

Improve this answer
2
  • I'm using pgrouting v. 2.1, but casting the id field to int still happened to be the reason why it wasn't working. Thanks a lot! :) Commented Dec 22, 2016 at 16:54
  • 2.1 still supports the old api. To trigger use of the new api, you'd have to leave out the reverse_cost boolean. The new api outputs more meaningful field names too, which may be not what you want if you need to use it in something that requires the old field names. Commented Dec 22, 2016 at 19:41

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.