pgr_createTopology
— Builds a network topology based on the geometry information.
Availability
Support
The function returns:
OK
after the network topology has been built and the vertices table created.FAIL
when the network topology was not built due to an error.varchar pgr_createTopology(text edge_table, double precision tolerance,
text the_geom:='the_geom', text id:='id',
text source:='source',text target:='target',
text rows_where:='true', boolean clean:=false)
The topology creation function accepts the following parameters:
edge_table: | text Network table name. (may contain the schema name AS well) |
---|---|
tolerance: | float8 Snapping tolerance of disconnected edges. (in projection unit) |
the_geom: | text Geometry column name of the network table. Default value is the_geom . |
id: | text Primary key column name of the network table. Default value is id . |
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 that where source or target have a null value, otherwise the condition is used. |
clean: | boolean Clean any previous topology. Default value is false . |
Warning
The edge_table
will be affected
source
column values will change.target
column values will change.id
the_geom
source
target
The function returns:
OK
after the network topology has been built.id
and the_geom
columns of the vertices table.id
of the vertices table.FAIL
when the network topology was not built due to an error:The Vertices Table
The vertices table is a requirement 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. |
The simplest way to use pgr_createTopology is:
SELECT pgr_createTopology('edge_table', 0.001);
NOTICE: PROCESSING:
NOTICE: pgr_createTopology('edge_table', 0.001, 'the_geom', 'id', 'source', 'target', rows_where := 'true', clean := f)
NOTICE: Performing checks, please wait .....
NOTICE: Creating Topology, Please wait...
NOTICE: -------------> TOPOLOGY CREATED FOR 18 edges
NOTICE: Rows with NULL geometry or NULL id: 0
NOTICE: Vertices table for table public.edge_table is: public.edge_table_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createtopology
--------------------
OK
(1 row)
When the arguments are given in the order described in the parameters:
We get the same result AS the simplest way to use the function.
SELECT pgr_createTopology('edge_table', 0.001,
'the_geom', 'id', 'source', 'target');
NOTICE: PROCESSING:
NOTICE: pgr_createTopology('edge_table', 0.001, 'the_geom', 'id', 'source', 'target', rows_where := 'true', clean := f)
NOTICE: Performing checks, please wait .....
NOTICE: Creating Topology, Please wait...
NOTICE: -------------> TOPOLOGY CREATED FOR 18 edges
NOTICE: Rows with NULL geometry or NULL id: 0
NOTICE: Vertices table for table public.edge_table is: public.edge_table_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createtopology
--------------------
OK
(1 row)
Warning
id
of the table ege_table
is passed to the function as the geometry column,the_geom
is passed to the function as the id column.SELECT pgr_createTopology('edge_table', 0.001,
'id', 'the_geom');
NOTICE: PROCESSING:
NOTICE: pgr_createTopology('edge_table', 0.001, 'id', 'the_geom', 'source', 'target', rows_where := 'true', clean := f)
NOTICE: Performing checks, please wait .....
NOTICE: ----> PGR ERROR in pgr_createTopology: Wrong type of Column id:the_geom
NOTICE: Unexpected error raise_exception
pgr_createtopology
--------------------
FAIL
(1 row)
When using the named notation
Parameters defined with a default value can be omitted, as long as the value matches the default And The order of the parameters would not matter.
SELECT pgr_createTopology('edge_table', 0.001,
the_geom:='the_geom', id:='id', source:='source', target:='target');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('edge_table', 0.001,
source:='source', id:='id', target:='target', the_geom:='the_geom');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('edge_table', 0.001, source:='source');
pgr_createtopology
--------------------
OK
(1 row)
Selecting rows using rows_where parameter
Selecting rows based on the id.
SELECT pgr_createTopology('edge_table', 0.001, rows_where:='id < 10');
pgr_createtopology
--------------------
OK
(1 row)
Selecting the rows where the geometry is near the geometry of row with id = 5
.
SELECT pgr_createTopology('edge_table', 0.001,
rows_where:='the_geom && (SELECT st_buffer(the_geom, 0.05) FROM edge_table WHERE id=5)');
pgr_createtopology
--------------------
OK
(1 row)
Selecting the rows where the geometry is near the geometry of the row with gid
=100 of the table othertable
.
CREATE TABLE otherTable AS (SELECT 100 AS gid, st_point(2.5, 2.5) AS other_geom);
SELECT 1
SELECT pgr_createTopology('edge_table', 0.001,
rows_where:='the_geom && (SELECT st_buffer(other_geom, 1) FROM otherTable WHERE gid=100)');
pgr_createtopology
--------------------
OK
(1 row)
For the following 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:
The arguments need to be given in the order described in the parameters.
Note that this example uses clean flag. So it recreates the whole vertices table.
SELECT pgr_createTopology('mytable', 0.001, 'mygeom', 'gid', 'src', 'tgt', clean := TRUE);
pgr_createtopology
--------------------
OK
(1 row)
Warning
gid
of the table mytable
is passed to the function AS the geometry column,mygeom
is passed to the function AS the id column.SELECT pgr_createTopology('mytable', 0.001, 'gid', 'mygeom', 'src', 'tgt');
NOTICE: PROCESSING:
NOTICE: pgr_createTopology('mytable', 0.001, 'gid', 'mygeom', 'src', 'tgt', rows_where := 'true', clean := f)
NOTICE: Performing checks, please wait .....
NOTICE: ----> PGR ERROR in pgr_createTopology: Wrong type of Column id:mygeom
NOTICE: Unexpected error raise_exception
pgr_createtopology
--------------------
FAIL
(1 row)
When using the named notation
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. The order of the parameters do not matter:
SELECT pgr_createTopology('mytable', 0.001, the_geom:='mygeom', id:='gid', source:='src', target:='tgt');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('mytable', 0.001, source:='src', id:='gid', target:='tgt', the_geom:='mygeom');
pgr_createtopology
--------------------
OK
(1 row)
Selecting rows using rows_where parameter
Based on id:
SELECT pgr_createTopology('mytable', 0.001, 'mygeom', 'gid', 'src', 'tgt', rows_where:='gid < 10');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('mytable', 0.001, source:='src', id:='gid', target:='tgt', the_geom:='mygeom', rows_where:='gid < 10');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('mytable', 0.001, 'mygeom', 'gid', 'src', 'tgt',
rows_where:='mygeom && (SELECT st_buffer(mygeom, 1) FROM mytable WHERE gid=5)');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('mytable', 0.001, source:='src', id:='gid', target:='tgt', the_geom:='mygeom',
rows_where:='mygeom && (SELECT st_buffer(mygeom, 1) FROM mytable WHERE gid=5)');
pgr_createtopology
--------------------
OK
(1 row)
Selecting the rows where the geometry is near the geometry of the row with gid
=100 of the table othertable
.
SELECT pgr_createTopology('mytable', 0.001, 'mygeom', 'gid', 'src', 'tgt',
rows_where:='mygeom && (SELECT st_buffer(other_geom, 1) FROM otherTable WHERE gid=100)');
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('mytable', 0.001, source:='src', id:='gid', target:='tgt', the_geom:='mygeom',
rows_where:='mygeom && (SELECT st_buffer(other_geom, 1) FROM otherTable WHERE gid=100)');
pgr_createtopology
--------------------
OK
(1 row)
Example: | With full output |
---|
This example start a clean topology, with 5 edges, and then its incremented to the rest of the edges.
SELECT pgr_createTopology('edge_table', 0.001, rows_where:='id < 6', clean := true);
NOTICE: PROCESSING:
NOTICE: pgr_createTopology('edge_table', 0.001, 'the_geom', 'id', 'source', 'target', rows_where := 'id < 6', clean := t)
NOTICE: Performing checks, please wait .....
NOTICE: Creating Topology, Please wait...
NOTICE: -------------> TOPOLOGY CREATED FOR 5 edges
NOTICE: Rows with NULL geometry or NULL id: 0
NOTICE: Vertices table for table public.edge_table is: public.edge_table_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createtopology
--------------------
OK
(1 row)
SELECT pgr_createTopology('edge_table', 0.001);
NOTICE: PROCESSING:
NOTICE: pgr_createTopology('edge_table', 0.001, 'the_geom', 'id', 'source', 'target', rows_where := 'true', clean := f)
NOTICE: Performing checks, please wait .....
NOTICE: Creating Topology, Please wait...
NOTICE: -------------> TOPOLOGY CREATED FOR 13 edges
NOTICE: Rows with NULL geometry or NULL id: 0
NOTICE: Vertices table for table public.edge_table is: public.edge_table_vertices_pgr
NOTICE: ----------------------------------------------
pgr_createtopology
--------------------
OK
(1 row)
The example uses the Sample Data network.
Indices and tables