How to use pgRouting's dijkstra_sp_delta_directed with WHERE clause?

Question

Right now I am using PgRouting for vehicle routing and I have problem with Dijkstra shortest path. My query is:

SELECT * FROM shortest_path('SELECT gid  AS id,
      source::int4 AS source,
      target::int4 AS target,
      cost::float8 AS cost,
      reverse_cost::float8 AS reverse_cost
from network ',                           
257027,
276521,
true,
true)

But, my table network is very large, so I should use "dijkstra_sp_delta_directed" wrapper function with bounding box to make query faster.

Select gid from dijkstra_sp_delta_directed('network', 5700, 6733, 0.1,true,true);

But I don't know how to write some where clause like "from network where road_class=1"

Also, I don't know where and how to write (road_class * cost)::float8 AS cost

Thank you in advance.

Answer 1

I resolved this, here is my wrapper function :

CREATE TYPE route_list AS (gid int4, name varchar(70));

CREATE OR REPLACE FUNCTION dijkstra_sp_delta_directed_my(character varying, integer,   integer, double precision, boolean, boolean,character varying, boolean)
RETURNS SETOF route_list AS
$BODY$
DECLARE 
    geom_table ALIAS FOR $1;
sourceid ALIAS FOR $2;
targetid ALIAS FOR $3;
delta ALIAS FOR $4;
dir ALIAS FOR $5;
rc ALIAS FOR $6;
where_clause ALIAS FOR $7;
fastest ALIAS FOR $8;


rec record;
r record;
    path_result record;
    v_id integer;
    e_id integer;
    geom geoms;
    foo route_list;

srid integer;

source_x float8;
source_y float8;
target_x float8;
target_y float8;

ll_x float8;
ll_y float8;
ur_x float8;
ur_y float8;

query text;
query_f text;
query_s text;
query1 text;
id integer;
   BEGIN

id :=0;
FOR rec IN EXECUTE
    'select srid(geom) from ' ||
    quote_ident(geom_table) || ' limit 1'
LOOP
END LOOP;
srid := rec.srid;

FOR rec IN EXECUTE 
        'select x(startpoint(geom)) as source_x from ' || 
        quote_ident(geom_table) || ' where source = ' || 
        sourceid ||  ' or target='||sourceid||' limit 1'
    LOOP
END LOOP;
source_x := rec.source_x;

FOR rec IN EXECUTE 
        'select y(startpoint(geom)) as source_y from ' || 
        quote_ident(geom_table) || ' where source = ' || 
        sourceid ||  ' or target='||sourceid||' limit 1'
    LOOP
END LOOP;

source_y := rec.source_y;

FOR rec IN EXECUTE 
        'select x(startpoint(geom)) as target_x from ' ||
        quote_ident(geom_table) || ' where source = ' || 
        targetid ||  ' or target='||targetid||' limit 1'
    LOOP
END LOOP;

target_x := rec.target_x;

FOR rec IN EXECUTE 
        'select y(startpoint(geom)) as target_y from ' || 
        quote_ident(geom_table) || ' where source = ' || 
        targetid ||  ' or target='||targetid||' limit 1'
    LOOP
END LOOP;
target_y := rec.target_y;


FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
       ' THEN '||source_x||' ELSE '||target_x||
       ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
       ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
    LOOP
END LOOP;

ll_x := rec.ll_x;
ur_x := rec.ur_x;

FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
        target_y||' THEN '||source_y||' ELSE '||
        target_y||' END as ll_y, CASE WHEN '||
        source_y||'>'||target_y||' THEN '||
        source_y||' ELSE '||target_y||' END as ur_y'
    LOOP
END LOOP;

ll_y := rec.ll_y;
ur_y := rec.ur_y;

query_f :=' SELECT gid, name FROM ' || quote_ident(geom_table) ||  ' JOIN (SELECT * FROM ' || 
      'shortest_path(''SELECT gid as id, source::integer, target::integer, ' || 
      'time::double precision as cost, ' || 
      'time::double precision as reverse_cost ';

query_s :=' SELECT gid, name FROM ' || quote_ident(geom_table) ||  ' JOIN (SELECT * FROM ' || 
      'shortest_path(''SELECT gid as id, source::integer, target::integer, ' || 
      'length ::double precision as cost, ' || 
      'length ::double precision as reverse_cost ';

IF fastest THEN 
    query1 = query_f;
ELSE 
    query1=query_s;
END IF;


query := query1 || ' FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
      ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
      ur_y+delta||')''''::BOX3D, ' || srid || ') && geom ' || where_clause || ''', ' || 
      quote_literal(sourceid) || ' , ' || 
      quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' )) AS route ON ' || quote_ident(geom_table) || '.gid = route.edge_id;';

FOR path_result IN EXECUTE query
    LOOP
            foo.gid      := path_result.gid;
            foo.name:= path_result.name;

             RETURN NEXT foo;

    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100
ROWS 1000;

Answer 2

how to write some where clause like "from network where road_class=1"

Judging from the source code at https://github.com/pgRouting/pgrouting/blob/master/core/sql/routing_core_wrappers.sql this doesn't seem possible without rewriting the wrapper function.

You could try using views instead but I'm not sure how this will perform.