pgr_createVerticesTable¶
Name¶
pgr_createVerticesTable
— Reconstructs the vertices table based on the source and target information.
Synopsis¶
The function returns:
OK
after the vertices table has been reconstructed.FAIL
when the vertices table was not reconstructed due to an error.
varchar pgr_createVerticesTable(text edge_table, text the_geom:='the_geom'
text source:='source',text target:='target',text rows_where:='true')
Description¶
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. |
History
- Renamed in version 2.0.0
Usage when the edge table’s columns MATCH the default values:¶
The simplest way to use pgr_createVerticesTable is:
SELECT pgr_createVerticesTable('edge_table');
When the arguments are given in the order described in the parameters:
SELECT pgr_createVerticesTable('edge_table','the_geom','source','target');
We get the same result as the simplest way to use the function.
Warning
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');
When using the named notation
The order of the parameters do not matter:
SELECT pgr_createVerticesTable('edge_table',the_geom:='the_geom',source:='source',target:='target');
SELECT pgr_createVerticesTable('edge_table',source:='source',target:='target',the_geom:='the_geom');
Parameters defined with a default value can be omited, as long as the value matches the default:
SELECT pgr_createVerticesTable('edge_table',source:='source');
Selecting rows using rows_where parameter
Selecting rows based on the id.
SELECT pgr_createVerticesTable('edge_table',rows_where:='id < 10');
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)');
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;
CREATE TABLE otherTable AS (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
SELECT pgr_createVerticesTable('edge_table',rows_where:='the_geom && (select st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
Usage when the edge table’s columns DO NOT MATCH the default values:¶
For the following table
DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable AS (SELECT id AS gid, the_geom AS mygeom,source AS src ,target AS tgt FROM edge_table) ;
Using positional notation:
The arguments need to be given in the order described in the parameters:
SELECT pgr_createVerticesTable('mytable','mygeom','src','tgt');
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');
When using the named notation
The order of the parameters do not matter:
SELECT pgr_createVerticesTable('mytable',the_geom:='mygeom',source:='src',target:='tgt');
SELECT pgr_createVerticesTable('mytable',source:='src',target:='tgt',the_geom:='mygeom');
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.
Selecting rows using rows_where parameter
Selecting rows based on the gid.
SELECT pgr_createVerticesTable('mytable','mygeom','src','tgt',rows_where:='gid < 10');
SELECT pgr_createVerticesTable('mytable',source:='src',target:='tgt',the_geom:='mygeom',rows_where:='gid < 10');
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)');
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)');
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;
CREATE TABLE otherTable AS (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
SELECT pgr_createVerticesTable('mytable','mygeom','src','tgt',
rows_where:='the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
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)');
Examples¶
SELECT pgr_createVerticesTable('edge_table');
NOTICE: PROCESSING:
NOTICE: pgr_createVerticesTable('edge_table','the_geom','source','target','true')
NOTICE: Performing checks, pelase 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)
The example uses the Sample Data network.
See Also¶
- Routing Topology 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.