# pgRouting Manual (2.1.0)

## 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` and `the_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

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');`

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

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