Supported versions: latest (3.8) 3.7 3.6 3.5 3.4 3.3 3.2 3.1 3.0 main dev
Unsupported versions:2.6 2.5 2.4 2.3 2.2 2.1 2.0

`pgr_nodeNetwork`¶

pgr_nodeNetwork - 网络边表的节点。

作者:: Nicolas Ribot
版权:: Nicolas Ribot，源代码根据 MIT-X 许可证发布。

该函数从非"noded"网络表中读取边，并将"noded"边写入新表中。

| pgr_nodenetwork(edge_table, tolerance, [options])
| options: [id, text the_geom, table_ending, rows_where, outall]

| RETURNS TEXT

可用性

Version 3.8.0
- Not checking and not creating indexes.
- Using pgr_separateTouching and pgr_separateCrossing.
- Created table with BIGINT.
版本2.0.0
- 官方函数

描述¶

主要特点是：

将 GIS 数据引入 pgRouting 的一个常见问题是数据通常没有正确“节点化”。这将创建无效的拓扑，从而导致路由不正确。

我们所说的 "noded"是指在道路网络的每个交叉口，所有边缘都将被分成单独的路段。在某些情况下，例如天桥和地下通道交叉口，您无法从天桥穿越到地下通道，但此功能无法检测和适应这些情况。

该函数读取 edge_table``表，该表具有主键列 ``id 和名为``the_geom`` 的几何列，并将其中的所有段与所有其他段相交，然后创建表 edge_table_noded。它使用``tolerance`` 来决定容差内的多个节点被视为同一节点。

参数¶

edge_table:: text 网络表的名称（可能包含模式名称）
tolerance:: float8 重合点公差（以投影单位表示）dd
id:: text 网络表的主键列名称。默认值为``id``。
the_geom:: text``网络表的几何列名称。默认值为``the_geom。
table_ending:: text``新表的后缀。默认值是``noded。

输出表中将包含 edge_table_noded 内容

id:: bigint 表的唯一标识符
old_id:: bigint 是原始表中边的标识符
sub_id:: integer 原边的段号
source:: bigint Empty source column
target:: bigint Empty target column
the geom:: geometry 节点网络的几何列

示例¶

Create the topology for the data in 示例数据

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

/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE ST_StartPoint(e.geom) = v.geom;
UPDATE 18
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE ST_EndPoint(e.geom) = v.geom;
UPDATE 18

Analyze the network for intersections.

SELECT e1.id id1, e2.id id2, ST_ASTEXT(ST_Intersection(e1.geom, e2.geom)) AS point
FROM edges e1, edges e2
WHERE e1.id < e2.id AND ST_Crosses(e1.geom, e2.geom);
 id1 | id2 |    point
-----+-----+--------------
  13 |  18 | POINT(3.5 3)
(1 row)

Analyze the network for gaps.

WITH
data AS (
  SELECT id, geom, (in_edges || out_edges)[1] as inhere
  FROM vertices where array_length(in_edges || out_edges, 1) = 1
),
results AS (
  SELECT d.id, d.inhere,
  (pgr_findCloseEdges('SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*
  FROM data d
)
SELECT
  id, inhere, edge_id, fraction,
  ST_AsText(geom) AS geom, ST_AsText(edge) AS edge
FROM results;
 id | inhere | edge_id | fraction |           geom            |                 edge
----+--------+---------+----------+---------------------------+--------------------------------------
  4 |     17 |      14 |      0.5 | POINT(1.999999999999 3.5) | LINESTRING(1.999999999999 3.5,2 3.5)
(1 row)

The analysis tell us that the network has a gap and an intersection.

Fixing an intersection¶

Storing the intersections.

SELECT e1.id id1, e2.id id2, st_intersection(e1.geom, e2.geom) AS point
INTO intersections
FROM edges e1, edges e2
WHERE e1.id < e2.id AND st_crosses(e1.geom, e2.geom);
SELECT 1

Calling pgr_nodeNetwork.

SELECT pgr_nodeNetwork('edges', 0.001, the_geom => 'geom', rows_where=>'id in ('||id1||','||id2||')')
FROM intersections;
NOTICE:  PROCESSING:
NOTICE:  id: id
NOTICE:  the_geom: geom
NOTICE:  table_ending: noded
NOTICE:  rows_where: id in (13,18)
NOTICE:  outall: f
NOTICE:  pgr_nodeNetwork('edges', 0.001, 'id', 'geom', 'noded', 'id in (13,18)',  f)
NOTICE:  Performing checks, please wait .....
NOTICE:  Processing, please wait .....
NOTICE:    Split Edges: 2
NOTICE:   Untouched Edges: 0
NOTICE:       Total original Edges: 2
NOTICE:   Edges generated: 4
NOTICE:   Untouched Edges: 0
NOTICE:         Total New segments: 4
NOTICE:   New Table: public.edges_noded
NOTICE:  ----------------------------------
 pgr_nodenetwork
-----------------
 OK
(1 row)

Inspecting the generated table, we can see that edges 13 and 18 have been segmented.

SELECT old_id, ST_AsText(geom) FROM edges_noded ORDER BY old_id, sub_id;
 old_id |         st_astext
--------+---------------------------
     13 | LINESTRING(3 3,3.5 3)
     13 | LINESTRING(3.5 3,4 3)
     18 | LINESTRING(3.5 2.3,3.5 3)
     18 | LINESTRING(3.5 3,3.5 4)
(4 rows)

Update the topology

Add new segments to the edges table.

INSERT INTO edges (cost, reverse_cost, geom)
WITH
the_fractions AS (
  SELECT e1.id id, st_lineLocatePoint(e1.geom, point) AS fraction
  FROM intersections, edges e1, edges e2 WHERE e1.id = id1 AND e2.id = id2
  UNION
  SELECT e2.id, st_lineLocatePoint(e2.geom, point)
  FROM intersections, edges e1, edges e2 WHERE e1.id = id1 AND e2.id = id2
)
SELECT
  CASE WHEN sub_id = 1
    THEN cost*fraction ELSE cost*(1-fraction) END as cost,
  CASE WHEN sub_id = 1
    THEN reverse_cost*(1-fraction) ELSE reverse_cost*(fraction) END AS reverse_cost,
  segments.geom
FROM edges as edges
JOIN edges_noded as segments ON (edges.id = segments.old_id)
JOIN the_fractions f ON (segments.old_id = f.id);
INSERT 0 4

Insert the intersection as new vertices.

INSERT INTO vertices (id, geom)
SELECT row_number() over() + 100, point
FROM intersections;
INSERT 0 1

Update source and target information on the edges table.

UPDATE edges e SET source = v.id FROM
vertices v where source IS NULL AND (st_startPoint(e.geom) = v.geom);
UPDATE 4
UPDATE edges e SET target = v.id FROM
vertices v where target IS NULL AND (st_endPoint(e.geom) = v.geom);
UPDATE 4

Delete original edge.

DELETE FROM edges
WHERE id IN (
  SELECT id1 FROM intersections
  UNION
  SELECT id2 FROM intersections);
DELETE 2

Update the vertex topology

WITH data AS (
  select p.id, p.in_edges, p.out_edges
  FROM pgr_extractVertices('select id, source, target from edges') p)
UPDATE vertices v
SET (in_edges,out_edges) = (d.in_edges,d.out_edges)
FROM data d where d.id = v.id;
UPDATE 18

Analyze the network for intersections.

SELECT e1.id, e2.id
FROM edges_noded e1, edges e2
WHERE e1.id < e2.id AND st_crosses(e1.geom, e2.geom);
 id | id
----+----
(0 rows)

Fixing a gap¶

Store the deadends

WITH
data AS (
  SELECT id, geom, (in_edges || out_edges)[1] as inhere
  FROM vertices where array_length(in_edges || out_edges, 1) = 1)
SELECT
  d.id, d.inhere,
  (pgr_findCloseEdges('SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*
INTO deadends
FROM data d;
SELECT 1