Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
Please put your second question in a separate thread so it can be answered independently. –  underdark Feb 8 '12 at 14:46
add comment

2 Answers

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;
share|improve this answer
    
This function works fine but … there is always some problem. My SRID is 4326. Where my two points are with different lat/lon everything is fine and buffer value 0.01 is ok. But if I have two points with similar latitude and different longitude my bounding box is very thin, so I must increase buffer value to 0.25-0.5. When I do this routing is slow again. Also, with buffer value 0.25 I got one route, but with buffer value 0.4 I got different route because there are more roads in this case. –  Route Maker Feb 13 '12 at 13:21
    
So, my question is how to define buffer value to be as small as it can, but to provide enough records for routing. I am also thinking about ST_Expand function, if I found middle point and use ST_Expand I will have much more data, but I also have to define buffer value. And in that case speed will be lost? –  Route Maker Feb 13 '12 at 13:21
add comment

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.

share|improve this answer
    
Anita, thank you ... I was thinking so ...PgRouting is so poorly documented , I am pretty frustrated :( –  Route Maker Feb 9 '12 at 9:18
    
I tried with views, it is slow ....I tried also with dijkstra_sp_delta_directed (without where clause) and it is slow :( –  Route Maker Feb 9 '12 at 20:35
    
If you always want to use only roads of class 1, then you should create a table that only contains class 1. –  underdark Feb 10 '12 at 7:41
    
It is not simple as that. When creating route I have 2 types: fastest and shothest, also I have 2 checkboxes: avoid tolls, and avoid highways. So filtering network table depends on user demand. Also, my cost field is time in first case and length in second case, so I have to change that field also. –  Route Maker Feb 10 '12 at 8:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.