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
Support
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¶
pgr_createVerticesTable(edge_table, the_geom, source, target, rows_where)
RETURNS 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 is the_geom . |
source: | text Source column name of the network table. Default value is source . |
target: | text Target column name of the network table. Default value is target . |
rows_where: | text Condition to SELECT a subset or rows. Default value is true 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: <edge_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('edge_table');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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('edge_table', 'the_geom', 'source', 'target');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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('edge_table', 'source', 'the_geom', 'target');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','source','the_geom','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: the_geom
HINT: ----> Expected type of the_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('edge_table', the_geom:='the_geom', source:='source', target:='target');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createverticestable
-------------------------
OK
(1 row)
Example 4: | Using a different ordering |
---|
SELECT pgr_createVerticesTable('edge_table', source:='source', target:='target', the_geom:='the_geom');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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('edge_table',source:='source');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','true')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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('edge_table',rows_where:='id < 10');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','id < 10')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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('edge_table',
rows_where:='the_geom && (select st_buffer(the_geom,0.5) FROM edge_table WHERE id=5)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','the_geom && (select st_buffer(the_geom,0.5) FROM edge_table WHERE id=5)')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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 table othertable . |
---|
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('edge_table',
rows_where:='the_geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','the_geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)')
NOTICE: Performing checks, please wait .....
NOTICE: Populating public.edge_table_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.edge_table is: public.edge_table_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, the_geom AS mygeom, source AS src ,target AS tgt FROM edge_table) ;
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
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 table othertable . |
---|
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 <pgr_create_topology>` 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