# pgRouting Manual (3.6)

## pgr_createVerticesTable

`pgr_createVerticesTable` — Reconstructs the vertices table based on the source and target information.

Availability

- Version 2.0.0

  - Renamed from version 1.x

  - Official function

## Description

The function returns:

- `OK` after the vertices table has been reconstructed.

- `FAIL` when the vertices table was not reconstructed due to an error.

## Signatures

pgr_createVerticesTable(edge_table, [`the_geom, source, target, rows_where`])
RETURNS `VARCHAR`

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

Example 1:

  The simplest way to use pgr_createVerticesTable

    SELECT  pgr_createVerticesTable('edges', 'geom');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_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.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

## Additional Examples

Example 2:

  When the arguments are given in the order described in the parameters:

    SELECT  pgr_createVerticesTable('edges', 'geom', 'source', 'target');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_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.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

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('edges', 'source', 'geom', 'target');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','source','geom','target','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: geom
    HINT:    ----> Expected type of geom is integer, smallint or bigint but USER-DEFINED was found
    NOTICE:  Unexpected error raise_exception
     pgr_createverticestable
    -------------------------
     FAIL
    (1 row)

When using the named notation

Example 3:

  The order of the parameters do not matter:

    SELECT  pgr_createVerticesTable('edges', the_geom:='geom', source:='source', target:='target');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_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.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 4:

  Using a different ordering

    SELECT  pgr_createVerticesTable('edges', source:='source', target:='target', the_geom:='geom');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_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.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 5:

  Parameters defined with a default value can be omitted, as long as the value matches the default:

    SELECT  pgr_createVerticesTable('edges', 'geom', source:='source');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_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.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Selecting rows using rows_where parameter

Example 6:

  Selecting rows based on the id.

    SELECT  pgr_createVerticesTable('edges', 'geom', rows_where:='id < 10');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','id < 10')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_vertices_pgr, please wait...
    NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
    NOTICE:                                         FOR   10  EDGES
    NOTICE:    Edges with NULL geometry,source or target: 0
    NOTICE:                              Edges processed: 10
    NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 7:

  Selecting the rows where the geometry is near the geometry of row with `id` =5 .

    SELECT  pgr_createVerticesTable('edges', 'geom',
        rows_where:='geom && (select st_buffer(geom,0.5) FROM edges WHERE id=5)');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','geom && (select st_buffer(geom,0.5) FROM edges WHERE id=5)')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_vertices_pgr, please wait...
    NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
    NOTICE:                                         FOR   9  EDGES
    NOTICE:    Edges with NULL geometry,source or target: 0
    NOTICE:                              Edges processed: 9
    NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 8:

  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;
    NOTICE:  table "othertable" does not exist, skipping
    DROP TABLE
    CREATE TABLE otherTable AS  (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
    SELECT 1
    SELECT  pgr_createVerticesTable('edges', 'geom',
        rows_where:='geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('edges','geom','source','target','geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.edges_vertices_pgr, please wait...
    NOTICE:    ----->   VERTICES TABLE CREATED WITH  10 VERTICES
    NOTICE:                                         FOR   12  EDGES
    NOTICE:    Edges with NULL geometry,source or target: 0
    NOTICE:                              Edges processed: 12
    NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

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

Using the following table

    DROP TABLE IF EXISTS mytable;
    NOTICE:  table "mytable" does not exist, skipping
    DROP TABLE
    CREATE TABLE mytable AS (SELECT id AS gid, geom AS mygeom, source AS src ,target AS tgt FROM edges) ;
    SELECT 18

Using positional notation:

Example 9:

  The arguments need to be given in the order described in the parameters:

    SELECT  pgr_createVerticesTable('mytable', 'mygeom', 'src', 'tgt');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.mytable_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.mytable is: public.mytable_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

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');
>     NOTICE:  PROCESSING:
>     NOTICE:  pgr_createVerticesTable('mytable','src','mygeom','tgt','true')
>     NOTICE:  Performing checks, please wait .....
>     NOTICE:  ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: mygeom
>     HINT:    ----> Expected type of mygeom is integer, smallint or bigint but USER-DEFINED was found
>     NOTICE:  Unexpected error raise_exception
>      pgr_createverticestable
>     -------------------------
>      FAIL
>     (1 row)

When using the named notation

Example 10:

  The order of the parameters do not matter:

    SELECT  pgr_createVerticesTable('mytable',the_geom:='mygeom',source:='src',target:='tgt');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.mytable_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.mytable is: public.mytable_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 11:

  Using a different ordering

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.

    SELECT  pgr_createVerticesTable(
        'mytable', source:='src', target:='tgt',
        the_geom:='mygeom');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.mytable_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.mytable is: public.mytable_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Selecting rows using rows_where parameter

Example 12:

  Selecting rows based on the gid. (positional notation)

    SELECT  pgr_createVerticesTable(
        'mytable', 'mygeom', 'src', 'tgt',
        rows_where:='gid < 10');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','gid < 10')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.mytable_vertices_pgr, please wait...
    NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
    NOTICE:                                         FOR   10  EDGES
    NOTICE:    Edges with NULL geometry,source or target: 0
    NOTICE:                              Edges processed: 10
    NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 13:

  Selecting rows based on the gid. (named notation)

    SELECT  pgr_createVerticesTable(
        'mytable', source:='src', target:='tgt', the_geom:='mygeom',
        rows_where:='gid < 10');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','gid < 10')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Populating public.mytable_vertices_pgr, please wait...
    NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
    NOTICE:                                         FOR   10  EDGES
    NOTICE:    Edges with NULL geometry,source or target: 0
    NOTICE:                              Edges processed: 10
    NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
    NOTICE:  ----------------------------------------------
     pgr_createverticestable
    -------------------------
     OK
    (1 row)

Example 14:

  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)');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Got column "the_geom" does not exist
    NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
    NOTICE:  select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)) limit 1
     pgr_createverticestable
    -------------------------
     FAIL
    (1 row)

Example 15:

  TBD

    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)');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Got column "id" does not exist
    NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
    NOTICE:  select * from public.mytable WHERE true AND (mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)) limit 1
     pgr_createverticestable
    -------------------------
     FAIL
    (1 row)

Example 16:

  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;
    DROP TABLE
    CREATE TABLE otherTable AS  (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
    SELECT 1

    SELECT pgr_createVerticesTable(
        'mytable', 'mygeom', 'src', 'tgt',
        rows_where:='the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Got column "the_geom" does not exist
    NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
    NOTICE:  select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)) limit 1
     pgr_createverticestable
    -------------------------
     FAIL
    (1 row)

Example 17:

  TBD

    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)');
    NOTICE:  PROCESSING:
    NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)')
    NOTICE:  Performing checks, please wait .....
    NOTICE:  Got column "the_geom" does not exist
    NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
    NOTICE:  select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)) limit 1
     pgr_createverticestable
    -------------------------
     FAIL
    (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_createTopology <pgr_create_topology>` 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.

Indices and tables

- Index

- Search Page