pgRouting Manual (2.0.0)

Custom Query

«  Graph Analytics   ::   Contents   ::   Performance Tips  »

Custom Query

In general, the routing algorithms need an SQL query that contain one or more of the following required columns with the preferred type:

id:int4
source:int4
target:int4
cost:float8
reverse_cost:float8
x:float8
y:float8
x1:float8
y1:float8
x2:float8
y2:float8

When the edge table has the mentioned columns, the following SQL queries can be used.

SELECT source, target, cost FROM edge_table;
SELECT id, source, target, cost FROM edge_table;
SELECT id, source, target, cost, x1, y1, x2, y2 ,reverse_cost FROM edge_table

When the edge table has a different name to represent the required columns:

SELECT src as source,  target, cost FROM othertable;
SELECT gid as id, src as source, target, cost FROM othertable;
SELECT gid as id, src as source, target, cost, fromX as x1, fromY as y1, toX as x2, toY as y2 ,Rcost as reverse_cost
       FROM othertable;

The topology functions use the same names for id, source and target columns of the edge table, The fowllowing parameters have as default value:

id:int4 Default id
source:int4 Default source
target:int4 Default target
the_geom:text Default the_geom
oneway:text Default oneway
rows_where:text Default true to indicate all rows (this is not a column)

The following parameters do not have a default value and when used they have to be inserted in strict order:

edge_table:text
tolerance:float8
s_in_rules:text[]
s_out_rules:text[]
t_in_rules:text[]
t_out_rules:text[]

When the columns required have the default names this can be used (pgr_func is to represent a topology function)

pgr_func('edge_table')        -- when tolerance is not requiered
pgr_func('edge_table',0.001)  -- when tolerance is requiered
-- s_in_rule, s_out_rule, st_in_rules, t_out_rules are requiered
SELECT pgr_analyzeOneway('edge_table', ARRAY['', 'B', 'TF'], ARRAY['', 'B', 'FT'],
                                       ARRAY['', 'B', 'FT'], ARRAY['', 'B', 'TF'])

When the columns required do not have the default names its strongly recomended to use the named notation.

pgr_func('othertable', id:='gid',source:='src',the_geom:='mygeom')
pgr_func('othertable',0.001,the_geom:='mygeom',id:='gid',source:='src')
SELECT pgr_analyzeOneway('othertable', ARRAY['', 'B', 'TF'], ARRAY['', 'B', 'FT'],
                                       ARRAY['', 'B', 'FT'], ARRAY['', 'B', 'TF']
                         source:='src',oneway:='dir')

«  Graph Analytics   ::   Contents   ::   Performance Tips  »