pgr_extractVertices -- 拟议

pgr_extractVertices — 提取顶点信息

Warning

下一版本的拟议功能。

  • 它们并未正式出现在当前版本中。

  • 它们可能会正式成为下一个版本的一部分:

    • 这些函数使用 ANY-INTEGER 和 ANY-NUMERICAL

    • 名字可能不会改变。(但仍然有可能改变)

    • 签名可能不会改变。(但仍然有可能改变)

    • 功能可能不会改变。(但仍然有可能改变)

    • pgTap 测试已经完成。 但可能需要更多。

    • 文档可能需要完善。

可用性

  • 版本 3.3.0

    • 列为 拟议 函数

  • 版本3.0.0

    • 实验 函数

描述

这是一个用于提取图的边集的顶点信息的辅助函数。

  • 当给出边标识符时,它也会计算出入边

签名

pgr_extractVertices(Edges SQL, [dryrun])
RETURNS SETOF (id, in_edges, out_edges, x, y, geom)
OR EMTPY SET
示例:

提取顶点信息

SELECT  * FROM pgr_extractVertices(
  'SELECT id, geom FROM edges');
 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)

参数

参数

类型

描述

Edges SQL

TEXT

Edges SQL 如下所述

可选参数

参数

类型

默认

描述

dryrun

BOOLEAN

false

  • 当为真时,不要处理查询,而是获取一个通知(NOTICE)来显示查询的结果。

内部查询

Edges SQL

当线的几何形状已知时

类型

描述

id

BIGINT

(可选)边的标识符。

geom

LINESTRING

边的几何形状。

该内部查询优先于接下来的两个内部查询,因此当 geom 列出现时,其他列将被忽略。

  • 忽略的列:

    • startpoint

    • endpoint

    • source

    • target

当顶点几何形状已知时

要使用此内部查询,列 geom 不应成为列集的一部分。

类型

描述

id

BIGINT

(可选)边的标识符。

startpoint

POINT

起始顶点的 POINT 几何图形。

endpoint

POINT

结束顶点POINT的几何图形。

此内部查询优先于下一个内部查询,因此当出现 startpointendpoint 列时,其他列将被忽略。

  • 忽略的列:

    • source

    • target

当顶点的标识符已知时

要使用此内部查询,列 geomstartpoint 和``endpoint`` 不应成为列集的一部分。

类型

描述

id

BIGINT

(可选)边的标识符。

source

ANY-INTEGER

边的第一个端点顶点的标识符。

target

ANY-INTEGER

边的第二个端点顶点的标识符。

结果列

类型

描述

id

BIGINT

顶点标识符

in_edges

BIGINT[]

以顶点 id 作为*第一个端点*的边的标识符数组。

  • NULLid 不是内部查询的一部分时

out_edges

BIGINT[]

以顶点 id 作为*第二个端点*的边的标识符数组。

  • NULLid 不是内部查询的一部分时

x

FLOAT

点几何的X值

  • NULL 当没有提供几何图形时

y

FLOAT

点几何的X值

  • NULL 当没有提供几何图形时

geom

POINT

点几何

  • NULL 当没有提供几何图形时

其他示例

模拟执行

要获取用于生成顶点信息的查询,请使用 dryrun := true

结果可作为基础代码,根据后台开发需要进行改进。

SELECT  * FROM pgr_extractVertices(
  'SELECT id, geom FROM edges',
  dryrun => true);
NOTICE:
        WITH

        main_sql AS (
          SELECT id, geom FROM edges
        ),

        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)

创建路由拓扑

确保数据库没有 vertices_table

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

清理要创建的路由拓扑的列

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

创建顶点表

  • LINESTRING``具有 SRID 时,请使用 ``geom::geometry(POINT, <SRID>)

  • 对于已经准备好的大型边表,

    • 将其创建为 UNLOGGED

    • 创建表后,执行 ALTER TABLE .. SET LOGGED 操作

SELECT  * INTO vertices_table
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17

检查顶点表

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)

在边表上创建路由拓扑

更新 source 信息

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

更新 target 信息

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

检查路由拓扑

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

_images/Fig1-originalData.png

生成拓扑

交叉边

要获取交叉边:

SELECT a.id, b.id
FROM edges AS a, edges AS b
WHERE a.id < b.id AND st_crosses(a.geom, b.geom);
 id | id
----+----
 13 | 18
(1 row)

_images/crossing_edges.png

这些信息是正确的,例如,就车辆而言,是隧道还是横跨另一条道路的桥梁。

它可能是不正确的,例如:

  1. 当它实际上是道路交叉口时,车辆可以转弯。

  2. 在电力线路方面,电力线能够在隧道或桥梁上甚至切换道路。

当不正确时,需要修复:

  1. 对于车辆和行人

    • 如果数据来自 OSM 并使用 osm2pgrouting 导入到数据库,则需要在 OSM portal 中完成修复并再次导入数据。

    • 一般来说,当数据来自为车辆路线准备数据的供应商时,并且出现问题时,需要从供应商处修复数据

  2. 对于非常具体的应用

    • 从路线车辆或行人的角度来看,数据是正确的。

    • 数据需要针对特定应用程序进行本地修复。

对交叉点进行一一分析后,对于需要局部修复的交叉点,需要 分割 边。

SELECT ST_AsText((ST_Dump(ST_Split(a.geom, b.geom))).geom)
FROM edges AS a, edges AS b
WHERE a.id = 13 AND b.id = 18
UNION
SELECT ST_AsText((ST_Dump(ST_Split(b.geom, a.geom))).geom)
FROM edges AS a, edges AS b
WHERE a.id = 13 AND b.id = 18;
         st_astext
---------------------------
 LINESTRING(3.5 2.3,3.5 3)
 LINESTRING(3 3,3.5 3)
 LINESTRING(3.5 3,4 3)
 LINESTRING(3.5 3,3.5 4)
(4 rows)

需要将新边添加到边表中,需要更新新边中的其余属性,需要删除旧边并需要更新路由拓扑。

添加分割边

对于每一对交叉边,必须执行与此类似的过程。

插入的列和计算方式取决于应用程序。 例如,如果边具有特征 名称 ,则将复制该列。

用于 pgRouting 计算

  • 基于边相交位置的 因子 可用于调整 costreverse_cost 列。

  • 分割边时,在 Flow - 函数族 函数中使用的容量信息不需要改变。

WITH
first_edge AS (
  SELECT (ST_Dump(ST_Split(a.geom, b.geom))).path[1],
    (ST_Dump(ST_Split(a.geom, b.geom))).geom,
    ST_LineLocatePoint(a.geom,ST_Intersection(a.geom,b.geom)) AS factor
  FROM edges AS a, edges AS b
  WHERE a.id = 13 AND b.id = 18),
first_segments AS (
  SELECT path, first_edge.geom,
    capacity, reverse_capacity,
    CASE WHEN path=1 THEN factor * cost
         ELSE (1 - factor) * cost END AS cost,
    CASE WHEN path=1 THEN factor * reverse_cost
         ELSE (1 - factor) * reverse_cost END AS reverse_cost
  FROM first_edge , edges WHERE id = 13),
second_edge AS (
  SELECT (ST_Dump(ST_Split(b.geom, a.geom))).path[1],
    (ST_Dump(ST_Split(b.geom, a.geom))).geom,
    ST_LineLocatePoint(b.geom,ST_Intersection(a.geom,b.geom)) AS factor
  FROM edges AS a, edges AS b
  WHERE a.id = 13 AND b.id = 18),
second_segments AS (
  SELECT path, second_edge.geom,
    capacity, reverse_capacity,
    CASE WHEN path=1 THEN factor * cost
         ELSE (1 - factor) * cost END AS cost,
    CASE WHEN path=1 THEN factor * reverse_cost
         ELSE (1 - factor) * reverse_cost END AS reverse_cost
  FROM second_edge , edges WHERE id = 18),
all_segments AS (
  SELECT * FROM first_segments
  UNION
  SELECT * FROM second_segments)
INSERT INTO edges
  (capacity, reverse_capacity,
    cost, reverse_cost,
    x1, y1, x2, y2,
    geom)
(SELECT capacity, reverse_capacity, cost, reverse_cost,
  ST_X(ST_StartPoint(geom)), ST_Y(ST_StartPoint(geom)),
  ST_X(ST_EndPoint(geom)), ST_Y(ST_EndPoint(geom)),
  geom
  FROM all_segments);
INSERT 0 4

添加新的顶点

添加应用程序所需的所有分割边后,需要将新创建的顶点添加到顶点表中。

INSERT INTO vertices (in_edges, out_edges, x, y, geom)
(SELECT nv.in_edges, nv.out_edges, nv.x, nv.y, nv.geom
FROM pgr_extractVertices('SELECT id, geom FROM edges') AS nv
LEFT JOIN vertices AS v USING(geom) WHERE v.geom IS NULL);
INSERT 0 1

更新边拓扑

/* -- set the source information */
UPDATE edges AS e
SET source = v.id
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
UPDATE 4
/* -- set the target information */
UPDATE edges AS e
SET target = v.id
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
UPDATE 4

去除多余的边

一旦应用程序所需的所有重要信息都已传输到新边,则可以删除交叉边。

DELETE FROM edges WHERE id IN (13, 18);
DELETE 2

还有其他选项可以完成此任务,例如创建视图或物化视图。

更新顶点拓扑

为了保持图的一致性,需要更新顶点拓扑

UPDATE vertices AS v SET
in_edges = nv.in_edges, out_edges = nv.out_edges
FROM (SELECT * FROM pgr_extractVertices('SELECT id, geom FROM edges')) AS nv
WHERE v.geom = nv.geom;
UPDATE 18

检查交叉边

图上没有交叉边。

SELECT a.id, b.id
FROM edges AS a, edges AS b
WHERE a.id < b.id AND st_crosses(a.geom, b.geom);
 id | id
----+----
(0 rows)

没有几何信息的图

本示例使用此表设计:

CREATE TABLE wiki (
  id SERIAL,
  source INTEGER,
  target INTEGER,
  cost INTEGER);
CREATE TABLE

插入数据

INSERT INTO wiki (source, target, cost) VALUES
(1, 2, 7),  (1, 3, 9), (1, 6, 14),
(2, 3, 10), (2, 4, 15),
(3, 6, 2),  (3, 4, 11),
(4, 5, 6),
(5, 6, 9);
INSERT 0 9

寻找最短路径

为了解决这个例子,使用了 pgr_dijkstra`

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost FROM wiki',
  1, 5, false);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         1 |       5 |    1 |    2 |    9 |        0
   2 |        2 |         1 |       5 |    3 |    6 |    2 |        9
   3 |        3 |         1 |       5 |    6 |    9 |    9 |       11
   4 |        4 |         1 |       5 |    5 |   -1 |    0 |       20
(4 rows)

\(1\)\(5\) 的路径要经过以下顶点: \(1 \rightarrow 3 \rightarrow 6 \rightarrow 5\)

graph G {
 rankdir="LR";
 1 [color="red"];
 5 [color="green"];
 1 -- 2 [label="(7)"];
 5 -- 6 [label="(9)", color="blue"];
 1 -- 3 [label="(9)", color="blue"];
 1 -- 6 [label="(14)"];
 2 -- 3 [label="(10)"];
 2 -- 4 [label="(13)"];
 3 -- 4 [label="(11)"];
 3 -- 6 [label="(2)", color="blue"];
 4 -- 5 [label="(6)"];
}

顶点信息

要获取顶点信息,请使用 pgr_extractVertices -- 拟议

SELECT id, in_edges, out_edges
FROM pgr_extractVertices('SELECT id, source, target FROM wiki');
 id | in_edges | out_edges
----+----------+-----------
  3 | {2,4}    | {6,7}
  5 | {8}      | {9}
  4 | {5,7}    | {8}
  2 | {1}      | {4,5}
  1 |          | {1,2,3}
  6 | {3,6,9}  |
(6 rows)

另请参阅

索引和表格