# pgRouting Manual (2.5)

## pgr_createTopology

## Name

`pgr_createTopology` — Builds a network topology based on the geometry information.

## Synopsis

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)

## Description

Parameters

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

- The `source` column values will change.
- The `target` column values will change.
  - An index will be created, if it doesn’t exists, to speed up the process to the following columns:
    - `id`
    - `the_geom`
    - `source`
    - `target`

The function returns:

- `OK` after the network topology has been built.
  - Creates a vertices table: <edge_table>_vertices_pgr.
  - Fills `id` and `the_geom` columns of the vertices table.
  - Fills the source and target columns of the edge table referencing the `id` of the vertices table.
- `FAIL` when the network topology was not built 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 or id are the same.
  - The SRID of the geometry could not be determined.

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.

History

- Renamed in version 2.0.0

## Usage when the edge table’s columns MATCH the default values:

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

An error would occur when the arguments are not given in the appropriate order:
In this example, the column `id` of the table `ege_table` is passed to the function as the geometry column,
and 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)

## Usage when the edge table’s columns DO NOT MATCH the default values:

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

An error would occur when the arguments are not given in the appropiriate order:
In this example, the column `gid` 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 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)

## Examples 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.

## See Also

- Routing Topology for an overview of a topology for routing algorithms.
- pgr_createVerticesTable to reconstruct the vertices table based on the source and target information.
- pgr_analyzeGraph to analyze the edges and vertices of the edge table.

Indices and tables

- Index
- Search Page