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')