How to make a pgr_dijkstra wrapper with reverse cost?

Question

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

Answer 1

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