# pgRouting Manual (3.3)

## pgr_extractVertices – Proposed

- Supported versions: Latest (3.3) 3.2 3.1 3.0

# pgr_extractVertices – Proposed

`pgr_extractVertices` — Extracts the vertices information based on the source and target.

Warning

Proposed functions for next mayor release.

- They are not officially in the current release.

- They will likely officially be part of the next mayor release:

  - The functions make use of ANY-INTEGER and ANY-NUMERICAL

  - Name might not change. (But still can)

  - Signature might not change. (But still can)

  - Functionality might not change. (But still can)

  - pgTap tests have being done. But might need more.

  - Documentation might need refinement.

Availability

- Version 3.3.0

  - Clasiffied as proposed function

- Version 3.0.0

  - New experimental function

## Description

This is an auxiliary function for extracting the vertex information of the set of edges of a graph.

- When the edge identifier is given, then it will also calculate the in and out edges

## Signatures

    pgr_extractVertices(Edges SQL [, dryrun])
    RETURNS SETOF (id, in_edges, out_edges, x, y, geom)

Example

  Extracting the vertex information

    SELECT  * FROM pgr_extractVertices(
        'SELECT id, the_geom AS geom
        FROM edge_table');
     id | in_edges | out_edges |       x        |  y  |                    geom
    ----+----------+-----------+----------------+-----+--------------------------------------------
      1 |          | {6}       |              0 |   2 | 010100000000000000000000000000000000000040
      2 |          | {17}      |            0.5 | 3.5 | 0101000000000000000000E03F0000000000000C40
      3 | {6}      | {7}       |              1 |   2 | 0101000000000000000000F03F0000000000000040
      4 | {17}     |           | 1.999999999999 | 3.5 | 010100000068EEFFFFFFFFFF3F0000000000000C40
      5 |          | {1}       |              2 |   0 | 010100000000000000000000400000000000000000
      6 | {1}      | {2,4}     |              2 |   1 | 01010000000000000000000040000000000000F03F
      7 | {4,7}    | {8,10}    |              2 |   2 | 010100000000000000000000400000000000000040
      8 | {10}     | {12,14}   |              2 |   3 | 010100000000000000000000400000000000000840
      9 | {14}     |           |              2 |   4 | 010100000000000000000000400000000000001040
     10 | {2}      | {3,5}     |              3 |   1 | 01010000000000000000000840000000000000F03F
     11 | {5,8}    | {9,11}    |              3 |   2 | 010100000000000000000008400000000000000040
     12 | {11,12}  | {13}      |              3 |   3 | 010100000000000000000008400000000000000840
     13 |          | {18}      |            3.5 | 2.3 | 01010000000000000000000C406666666666660240
     14 | {18}     |           |            3.5 |   4 | 01010000000000000000000C400000000000001040
     15 | {3}      | {16}      |              4 |   1 | 01010000000000000000001040000000000000F03F
     16 | {9,16}   | {15}      |              4 |   2 | 010100000000000000000010400000000000000040
     17 | {13,15}  |           |              4 |   3 | 010100000000000000000010400000000000000840
    (17 rows)

## Parameters

Parameter | Type | Description
--- | --- | ---
Edges SQL | `TEXT` | The set of edges of the graph. It is an Inner Query as described below.
dryrun | `TEXT` | Don’t process and get in a NOTICE the resulting query.

## Inner Query

When line geometry is known

Column | Type | Description
--- | --- | ---
id | `BIGINT` | (Optional) identifier of the edge.
geom | `LINESTRING` | LINESTRING geometry of the edge.

This inner query takes precedence over the next two inner query, therefore other columns are ignored when `geom` column appears.

- Ignored columns:

  - `startpoint`

  - `endpoint`

  - `source`

  - `target`

When vertex geometry is known

To use this inner query the column `geom` should not be part of the set of columns.

Column | Type | Description
--- | --- | ---
id | `BIGINT` | (Optional) identifier of the edge.
startpoint | `POINT` | POINT geometry of the starting vertex.
endpoint | `POINT` | POINT geometry of the ending vertex.

This inner query takes precedence over the next inner query, therefore other columns are ignored when `startpoint` and `endpoint` columns appears.

- Ignored columns:

  - `source`

  - `target`

When identifiers of vertices are known

To use this inner query the columns `geom`, `startpoint` and `endpoint` should not be part of the set of columns.

Column | Type | Description
--- | --- | ---
id | `BIGINT` | (Optional) identifier of the edge.
source | `ANY-INTEGER` | Identifier of the first end point vertex of the edge.
target | `ANY-INTEGER` | Identifier of the second end point vertex of the edge.

## Result Columns

Rreturns set of (id, in_edges, out_edges, x, y, geom)

Column | Type | Description
--- | --- | ---
id | `BIGINT` | Identifier of the first end point vertex of the edge.

in_edges

`BIGINT[]`

Array of identifiers of the edges that have the vertex `id` as first end point.

  - `NULL` When the `id` is not part of the inner query

out_edges

`BIGINT[]`

Array of identifiers of the edges that have the vertex `id` as second end point.

  - `NULL` When the `id` is not part of the inner query

x

`FLOAT`

X value of the POINT geometry

  - `NULL` When no geometry is provided

y

`FLOAT`

Y value of the POINT geometry

  - `NULL` When no geometry is provided

geom

`POINT`

Geometry of the POINT

  - `NULL` When no geometry is provided

## Additional Examples

Example 1

  Dryrun execution

To get the query generated used to get the vertex information, use `dryrun := true`.

The results can be used as base code to make a refinement based on the backend development needs.

    SELECT  * FROM pgr_extractVertices(
        'SELECT id, the_geom AS geom FROM edge_table',
        dryrun := true);
    NOTICE:
            WITH

            main_sql AS (
              SELECT id, the_geom AS geom FROM edge_table
            ),

            the_out AS (
              SELECT id::BIGINT AS out_edge, ST_StartPoint(geom) AS geom
              FROM main_sql
            ),

            agg_out AS (
              SELECT array_agg(out_edge ORDER BY out_edge) AS out_edges, ST_x(geom) AS x, ST_Y(geom) AS y, geom
              FROM the_out
              GROUP BY geom
            ),

            the_in AS (
              SELECT id::BIGINT AS in_edge, ST_EndPoint(geom) AS geom
              FROM main_sql
            ),

            agg_in AS (
              SELECT array_agg(in_edge ORDER BY in_edge) AS in_edges, ST_x(geom) AS x, ST_Y(geom) AS y, geom
              FROM the_in
              GROUP BY geom
            ),

            the_points AS (
              SELECT in_edges, out_edges, coalesce(agg_out.geom, agg_in.geom) AS geom
              FROM agg_out
              FULL OUTER JOIN agg_in USING (x, y)
            )

            SELECT row_number() over(ORDER BY ST_X(geom), ST_Y(geom)) AS id, in_edges, out_edges, ST_X(geom), ST_Y(geom), geom
            FROM the_points;
     id | in_edges | out_edges | x | y | geom
    ----+----------+-----------+---+---+------
    (0 rows)

Example 2

  Creating a routing topology

1. Making sure the database does not have the `vertices_table`

       DROP TABLE IF EXISTS vertices_table;
       NOTICE:  table "vertices_table" does not exist, skipping
       DROP TABLE

2. Cleaning up the columns of the rotuing topology to be created

       UPDATE edge_table
       SET source = NULL, target = NULL,
          x1 = NULL, y1 = NULL,
          x2 = NULL, y2 = NULL;
       UPDATE 18

3. Creating the vertices table

       SELECT  * INTO vertices_table
       FROM pgr_extractVertices('SELECT id, the_geom AS geom FROM edge_table');
       SELECT 17

4. Inspection of the vertices table

       SELECT *
       FROM vertices_table;
        id | in_edges | out_edges |       x        |  y  |                    geom
       ----+----------+-----------+----------------+-----+--------------------------------------------
         1 |          | {6}       |              0 |   2 | 010100000000000000000000000000000000000040
         2 |          | {17}      |            0.5 | 3.5 | 0101000000000000000000E03F0000000000000C40
         3 | {6}      | {7}       |              1 |   2 | 0101000000000000000000F03F0000000000000040
         4 | {17}     |           | 1.999999999999 | 3.5 | 010100000068EEFFFFFFFFFF3F0000000000000C40
         5 |          | {1}       |              2 |   0 | 010100000000000000000000400000000000000000
         6 | {1}      | {2,4}     |              2 |   1 | 01010000000000000000000040000000000000F03F
         7 | {4,7}    | {8,10}    |              2 |   2 | 010100000000000000000000400000000000000040
         8 | {10}     | {12,14}   |              2 |   3 | 010100000000000000000000400000000000000840
         9 | {14}     |           |              2 |   4 | 010100000000000000000000400000000000001040
        10 | {2}      | {3,5}     |              3 |   1 | 01010000000000000000000840000000000000F03F
        11 | {5,8}    | {9,11}    |              3 |   2 | 010100000000000000000008400000000000000040
        12 | {11,12}  | {13}      |              3 |   3 | 010100000000000000000008400000000000000840
        13 |          | {18}      |            3.5 | 2.3 | 01010000000000000000000C406666666666660240
        14 | {18}     |           |            3.5 |   4 | 01010000000000000000000C400000000000001040
        15 | {3}      | {16}      |              4 |   1 | 01010000000000000000001040000000000000F03F
        16 | {9,16}   | {15}      |              4 |   2 | 010100000000000000000010400000000000000040
        17 | {13,15}  |           |              4 |   3 | 010100000000000000000010400000000000000840
       (17 rows)

5. Creating the routing topology on the edge table

   Updating the `source` information

       WITH
           out_going AS (
               SELECT id AS vid, unnest(out_edges) AS eid, x, y
               FROM vertices_table
           )
       UPDATE edge_table
       SET source = vid, x1 = x, y1 = y
       FROM out_going WHERE id = eid;
       UPDATE 18

   Updating the `target` information

       WITH
           in_coming AS (
               SELECT id AS vid, unnest(in_edges) AS eid, x, y
               FROM vertices_table
           )
       UPDATE edge_table
       SET target = vid, x2 = x, y2 = y
       FROM in_coming WHERE id = eid;
       UPDATE 18

6. Inspection of the routing topology

       SELECT id, source, target, x1, y1, x2, y2
       FROM edge_table;
        id | source | target | x1  | y1  |       x2       | y2
       ----+--------+--------+-----+-----+----------------+-----
         6 |      1 |      3 |   0 |   2 |              1 |   2
        17 |      2 |      4 | 0.5 | 3.5 | 1.999999999999 | 3.5
         1 |      5 |      6 |   2 |   0 |              2 |   1
         4 |      6 |      7 |   2 |   1 |              2 |   2
         7 |      3 |      7 |   1 |   2 |              2 |   2
        10 |      7 |      8 |   2 |   2 |              2 |   3
        14 |      8 |      9 |   2 |   3 |              2 |   4
         2 |      6 |     10 |   2 |   1 |              3 |   1
         5 |     10 |     11 |   3 |   1 |              3 |   2
         8 |      7 |     11 |   2 |   2 |              3 |   2
        11 |     11 |     12 |   3 |   2 |              3 |   3
        12 |      8 |     12 |   2 |   3 |              3 |   3
        18 |     13 |     14 | 3.5 | 2.3 |            3.5 |   4
         3 |     10 |     15 |   3 |   1 |              4 |   1
         9 |     11 |     16 |   3 |   2 |              4 |   2
        16 |     15 |     16 |   4 |   1 |              4 |   2
        13 |     12 |     17 |   3 |   3 |              4 |   3
        15 |     16 |     17 |   4 |   2 |              4 |   3
       (18 rows)

## See Also

- Topology - Family of Functions for an overview of a topology for routing algorithms.

- pgr_createVerticesTable to create a topology based on the geometry.

Indices and tables

- Index

- Search Page