pgr_extractVertices – Experimental

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

Warning

Possible server crash

• These functions might create a server crash

Warning

Experimental functions

• They are not officially of the current release.

• They likely will not be officially be part of the next release:

  • The functions might not make use of ANY-INTEGER and ANY-NUMERICAL

  • Name might change.

  • Signature might change.

  • Functionality might change.

  • pgTap tests might be missing.

  • Might need c/c++ coding.

  • May lack documentation.

  • Documentation if any might need to be rewritten.

  • Documentation examples might need to be automatically generated.

  • Might need a lot of feedback from the comunity.

  • Might depend on a proposed function of pgRouting

  • Might depend on a deprecated function of pgRouting

Availability

• 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