pgr_extractVertices – Proposed¶
pgr_extractVertices
— Extracts the vertices information
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
Classified 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 as described below |
Optional parameters¶
Parameter |
Type |
Default |
Description |
---|---|---|---|
|
|
|
|
Inner Queries¶
Edges SQL¶
When line geometry is known¶
Column |
Type |
Description |
---|---|---|
|
|
(Optional) identifier of the edge. |
|
|
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 |
---|---|---|
|
|
(Optional) identifier of the edge. |
|
|
POINT geometry of the starting vertex. |
|
|
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 |
---|---|---|
|
|
(Optional) identifier of the edge. |
|
|
Identifier of the first end point vertex of the edge. |
|
|
Identifier of the second end point vertex of the edge. |
Result Columns¶
Column |
Type |
Description |
---|---|---|
|
|
Identifier of the first end point vertex of the edge. |
|
|
Array of identifiers of the edges that have the vertex
|
|
|
Array of identifiers of the edges that have the vertex
|
|
|
X value of the point geometry
|
|
|
X value of the point geometry
|
|
|
Geometry of the point
|
Additional Examples¶
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)
Create a routing topology¶
Make 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
Clean up the columns of the routing topology to be created¶
UPDATE edge_table
SET source = NULL, target = NULL,
x1 = NULL, y1 = NULL,
x2 = NULL, y2 = NULL;
UPDATE 18
Create the vertices table¶
When the
LINESTRING
has a SRID then usegeom::geometry(POINT, <SRID>)
For big edge tables that are been prepared,
Create it as
UNLOGGED
andAfter the table is created
ALTER TABLE .. SET LOGGED
SELECT * INTO vertices_table
FROM pgr_extractVertices('SELECT id, the_geom AS geom FROM edge_table');
SELECT 17
Inspect 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)
Create 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
Inspect 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¶
Indices and tables