# pgRouting Manual (3.1)

## pgr_createTopology

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

Availability

- Version 2.0.0

  - Renamed from version 1.x

  - Official function

Support

- Supported versions: current(3.1) 3.0 2.6

- Unsupported versions: 2.5 2.4 2.3 2.2 2.1 2.0

## Description

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.

## Signatures

    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)

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

### 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)

## Additional Examples

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.

## See Also

- Topology - Family of Functions 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