pgr_extractVertices - Experimental - pgRouting Manual (3.0)
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
-
Support
-
Supported versions: current( 3.0 )
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 |
|
The set of edges of the graph. It is an Inner Query as described below. |
dryrun |
|
Don’t process and get in a NOTICE the resulting query. |
Inner Query
When line geometry is known
Column |
Type |
Description |
---|---|---|
id |
|
(Optional) identifier of the edge. |
geom |
|
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 |
|
(Optional) identifier of the edge. |
startpoint |
|
POINT geometry of the starting vertex. |
endpoint |
|
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 |
|
(Optional) identifier of the edge. |
source |
|
Identifier of the first end point vertex of the edge. |
target |
|
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 |
|
Identifier of the first end point vertex of the edge. |
in_edges |
|
|
out_edges |
|
|
x |
|
|
y |
|
|
geom |
|
|
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
-
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
-
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
-
Creating the vertices table
SELECT * INTO vertices_table FROM pgr_extractVertices('SELECT id, the_geom AS geom FROM edge_table'); SELECT 17
-
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)
-
Creating the routing topology on the edge table
Updating the
source
informationWITH 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
informationWITH 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
-
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