pgr_createVerticesTable - pgRouting Manual (3.4)
pgr_createVerticesTable
pgr_createVerticesTable
- Reconstructs the vertices table based on the
source and target information.
Availability
-
Version 2.0.0
-
Renamed from version 1.x
-
Official function
-
Description
The function returns:
-
OK
after the vertices table has been reconstructed. -
FAIL
when the vertices table was not reconstructed due to an error.
Signatures
the_geom,
source,
target,
rows_where
])
VARCHAR
Parameters
The reconstruction of the vertices table function accepts the following parameters:
- edge_table :
-
text
Network table name. (may contain the schema name as well) - the_geom :
-
text
Geometry column name of the network table. Default value isthe_geom
. - source :
-
text
Source column name of the network table. Default value issource
. - target :
-
text
Target column name of the network table. Default value istarget
. - rows_where :
-
text
Condition to SELECT a subset or rows. Default value istrue
to indicate all rows.
Warning
The
edge_table
will be affected
-
An index will be created, if it doesn’t exists, to speed up the process to the following columns:
-
the_geom
-
source
-
target
-
The function returns:
-
OK
after the vertices table has been reconstructed.-
Creates a vertices table:
_vertices_pgr. -
Fills
id
andthe_geom
columns of the vertices table based on the source and target columns of the edge table.
-
-
FAIL
when the vertices table was not reconstructed due to an error.-
A required column of the Network table is not found or is not of the appropriate type.
-
The condition is not well formed.
-
The names of source, target are the same.
-
The SRID of the geometry could not be determined.
-
The Vertices Table
The vertices table is a requierment of the pgr_analyzeGraph and the pgr_analyzeOneWay functions.
The structure of the vertices table is:
- id :
-
bigint
Identifier of the vertex. - cnt :
-
integer
Number of vertices in the edge_table that reference this vertex. See pgr_analyzeGraph . - chk :
-
integer
Indicator that the vertex might have a problem. See pgr_analyzeGraph . - ein :
-
integer
Number of vertices in the edge_table that reference this vertex as incoming. See pgr_analyzeOneWay . - eout :
-
integer
Number of vertices in the edge_table that reference this vertex as outgoing. See pgr_analyzeOneWay . - the_geom :
-
geometry
Point geometry of the vertex. - Example 1 :
-
The simplest way to use pgr_createVerticesTable
SELECT pgr_createVerticesTable('edges', 'geom');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
Additional Examples
- Example 2 :
-
When the arguments are given in the order described in the parameters:
SELECT pgr_createVerticesTable('edges', 'geom', 'source', 'target');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
We get the same result as the simplest way to use the function.
Warning
An error would occur when the arguments are not given in the appropriate
order: In this example, the column source column
source
of the table
mytable
is passed to the function as the geometry column, and the
geometry column
the_geom
is passed to the function as the source column.
SELECT pgr_createVerticesTable('edges', 'source', 'geom', 'target');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','source','geom','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: geom
HINT: ----> Expected type of geom is integer, smallint or bigint but USER-DEFINED was found
NOTICE: Unexpected error raise_exception
pgr_createverticestable
-------------------------
FAIL
(1 row)
When using the named notation
- Example 3 :
-
The order of the parameters do not matter:
SELECT pgr_createVerticesTable('edges', the_geom:='geom', source:='source', target:='target');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 4 :
-
Using a different ordering
SELECT pgr_createVerticesTable('edges', source:='source', target:='target', the_geom:='geom');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 5 :
-
Parameters defined with a default value can be omitted, as long as the value matches the default:
SELECT pgr_createVerticesTable('edges', 'geom', source:='source');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
Selecting rows using rows_where parameter
- Example 6 :
-
Selecting rows based on the id.
SELECT pgr_createVerticesTable('edges', 'geom', rows_where:='id < 10');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','id < 10')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 9 VERTICES
NOTICE: FOR 10 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 10
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 7 :
-
Selecting the rows where the geometry is near the geometry of row with
id
=5 .
SELECT pgr_createVerticesTable('edges', 'geom',
rows_where:='geom && (select st_buffer(geom,0.5) FROM edges WHERE id=5)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','geom && (select st_buffer(geom,0.5) FROM edges WHERE id=5)')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 9 VERTICES
NOTICE: FOR 9 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 9
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 8 :
-
Selecting the rows where the geometry is near the geometry of the row with
gid
=100 of the tableothertable
.
DROP TABLE IF EXISTS otherTable;
NOTICE: table "othertable" does not exist, skipping
DROP TABLE
CREATE TABLE otherTable AS (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
SELECT 1
SELECT pgr_createVerticesTable('edges', 'geom',
rows_where:='geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edges','geom','source','target','geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edges_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 10 VERTICES
NOTICE: FOR 12 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 12
NOTICE: Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
Usage when the edge table’s columns DO NOT MATCH the default values:
Using the following table
DROP TABLE IF EXISTS mytable;
NOTICE: table "mytable" does not exist, skipping
DROP TABLE
CREATE TABLE mytable AS (SELECT id AS gid, geom AS mygeom, source AS src ,target AS tgt FROM edges) ;
SELECT 18
Using positional notation:
- Example 9 :
-
The arguments need to be given in the order described in the parameters:
SELECT pgr_createVerticesTable('mytable', 'mygeom', 'src', 'tgt');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.mytable_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
Warning
An error would occur when the arguments are not given in the appropriate
order: In this example, the column
src
of the table
mytable
is passed
to the function as the geometry column, and the geometry column
mygeom
is
passed to the function as the source column.
SELECT pgr_createVerticesTable('mytable', 'src', 'mygeom', 'tgt'); NOTICE: PROCESSING: NOTICE: pgr_createVerticesTable('mytable','src','mygeom','tgt','true') NOTICE: Performing checks, please wait ..... NOTICE: ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: mygeom HINT: ----> Expected type of mygeom is integer, smallint or bigint but USER-DEFINED was found NOTICE: Unexpected error raise_exception pgr_createverticestable ------------------------- FAIL (1 row)
When using the named notation
- Example 10 :
-
The order of the parameters do not matter:
SELECT pgr_createVerticesTable('mytable',the_geom:='mygeom',source:='src',target:='tgt');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.mytable_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 11 :
-
Using a different ordering
In this scenario omitting a parameter would create an error because the default values for the column names do not match the column names of the table.
SELECT pgr_createVerticesTable(
'mytable', source:='src', target:='tgt',
the_geom:='mygeom');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.mytable_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 17 VERTICES
NOTICE: FOR 18 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 18
NOTICE: Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
Selecting rows using rows_where parameter
- Example 12 :
-
Selecting rows based on the gid. (positional notation)
SELECT pgr_createVerticesTable(
'mytable', 'mygeom', 'src', 'tgt',
rows_where:='gid < 10');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','gid < 10')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.mytable_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 9 VERTICES
NOTICE: FOR 10 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 10
NOTICE: Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 13 :
-
Selecting rows based on the gid. (named notation)
SELECT pgr_createVerticesTable(
'mytable', source:='src', target:='tgt', the_geom:='mygeom',
rows_where:='gid < 10');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','gid < 10')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.mytable_vertices_pgr, please wait...
NOTICE: -----> VERTICES TABLE CREATED WITH 9 VERTICES
NOTICE: FOR 10 EDGES
NOTICE: Edges with NULL geometry,source or target: 0
NOTICE: Edges processed: 10
NOTICE: Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
- Example 14 :
-
Selecting the rows where the geometry is near the geometry of row with
gid
= 5.
SELECT pgr_createVerticesTable(
'mytable', 'mygeom', 'src', 'tgt',
rows_where := 'the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)')
NOTICE: Performing checks, please wait .....
NOTICE: Got column "the_geom" does not exist
NOTICE: ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE: select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)) limit 1
pgr_createverticestable
-------------------------
FAIL
(1 row)
- Example 15 :
-
TBD
SELECT pgr_createVerticesTable(
'mytable', source:='src', target:='tgt', the_geom:='mygeom',
rows_where:='mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)')
NOTICE: Performing checks, please wait .....
NOTICE: Got column "id" does not exist
NOTICE: ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE: select * from public.mytable WHERE true AND (mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)) limit 1
pgr_createverticestable
-------------------------
FAIL
(1 row)
- Example 16 :
-
Selecting the rows where the geometry is near the geometry of the row with
gid
=100 of the tableothertable
.
DROP TABLE IF EXISTS otherTable;
DROP TABLE
CREATE TABLE otherTable AS (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
SELECT 1
SELECT pgr_createVerticesTable(
'mytable', 'mygeom', 'src', 'tgt',
rows_where:='the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)')
NOTICE: Performing checks, please wait .....
NOTICE: Got column "the_geom" does not exist
NOTICE: ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE: select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)) limit 1
pgr_createverticestable
-------------------------
FAIL
(1 row)
- Example 17 :
-
TBD
SELECT pgr_createVerticesTable(
'mytable',source:='src',target:='tgt',the_geom:='mygeom',
rows_where:='the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)')
NOTICE: Performing checks, please wait .....
NOTICE: Got column "the_geom" does not exist
NOTICE: ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE: select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)) limit 1
pgr_createverticestable
-------------------------
FAIL
(1 row)
The example uses the Sample Data network.
See Also
-
Topology - Family of Functions for an overview of a topology for routing algorithms.
-
pgr_createTopology
` to create a topology based on the geometry. -
pgr_analyzeGraph to analyze the edges and vertices of the edge table.
-
pgr_analyzeOneWay to analyze directionality of the edges.
Indices and tables