Dictionary of columns & Custom Query¶

path:	a sequence of vertices/edges from A to B.
route:	a sequence of paths.
ANY-INTEGER:	Any of the following types: SMALLINT, INTEGER, BIGINT
ANY-NUMERICAL:	Any of the following types: SMALLINT, INTEGER, BIGINT, REAL, FLOAT

Custom Queries¶

Edges queries¶

Columns of the edges_sql queries¶

Depending on the function used the following columns are expected

Column	Type	Default	Description
id	`ANY-INTEGER`		Identifier of the edge.
source	`ANY-INTEGER`		Identifier of the first end point vertex of the edge.
target	`ANY-INTEGER`		Identifier of the second end point vertex of the edge.
cost	`ANY-NUMERICAL`		Weight of the edge (source, target) When negative: edge (source, target) does not exist, therefore it’s not part of the graph.
reverse_cost	`ANY-NUMERICAL`	-1	Weight of the edge (target, source), When negative: edge (target, source) does not exist, therefore it’s not part of the graph.

Where:

ANY-INTEGER:	SMALLINT, INTEGER, BIGINT
ANY-NUMERICAL:	SMALLINT, INTEGER, BIGINT, REAL, FLOAT

Description of the edges_sql query¶

edges_sql:

an SQL query, which should return a set of rows with the following columns:

id:	`ANY-INTEGER` identifier of the edge.
source:	`ANY-INTEGER` identifier of the first end point vertex of the edge.
target:	`ANY-INTEGER` identifier of the second end pont vertex of the edge.
cost:	`ANY-NUMERICAL` weight of the edge (source, target), if negative: edge (source, target) does not exist, therefore it’s not part of the graph.
reverse_cost:	`ANY-NUMERICAL` (optional) weight of the edge (target, source), if negative: edge (target, source) does not exist, therefore it’s not part of the graph.

Where:

ANY-INTEGER:	smallint, int, bigint
ANY-NUMERICAL:	smallint, int, bigint, real, float

Description of the parameters of the signatures¶

edges_sql:	`TEXT` SQL query as described above.
start_vid:	`BIGINT` identifier of the starting vertex of the path.
start_vids:	`array[ANY-INTEGER]` array of identifiers of starting vertices.
end_vid:	`BIGINT` identifier of the ending vertex of the path.
end_vids:	`array[ANY-INTEGER]` array of identifiers of ending vertices.
directed:	`boolean` (optional). When `false` the graph is considered as Undirected. Default is `true` which considers the graph as Directed.

Description of the return values¶

Returns set of (seq [, start_vid] [, end_vid] , node, edge, cost, agg_cost)

seq:	`INTEGER` is a sequential value starting from 1.
route_seq:	`INTEGER` relative position in the route. Has value 1 for the beginning of a route.
route_id:	`INTEGER` id of the route.
path_seq:	`INTEGER` relative position in the path. Has value 1 for the beginning of a path.
path_id:	`INTEGER` id of the path.
start_vid:	`BIGINT` id of the starting vertex. Used when multiple starting vetrices are in the query.
end_vid:	`BIGINT` id of the ending vertex. Used when multiple ending vertices are in the query.
node:	`BIGINT` id of the node in the path from start_vid to end_v.
edge:	`BIGINT` id of the edge used to go from `node` to the next node in the path sequence. `-1` for the last node of the path.
cost:	`FLOAT` cost to traverse from `node` using `edge` to the next node in the path sequence.
agg_cost:	`FLOAT` total cost from `start_vid` to `node`.

Descriptions for version 2.0 signatures¶

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

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 required
pgr_func('edge_table',0.001)  -- when tolerance is required
-- s_in_rule, s_out_rule, st_in_rules, t_out_rules are required
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 recommended 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')

pgRouting Manual (2.3)