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

Calling pgr_nodeNetwork.

SELECT pgr_nodeNetwork('edges', 0.001, the_geom => 'geom', rows_where=>'id in ('||inhere||','||edge_id||')')
FROM deadends;
NOTICE:  PROCESSING:
NOTICE:  id: id
NOTICE:  the_geom: geom
NOTICE:  table_ending: noded
NOTICE:  rows_where: id in (17,14)
NOTICE:  outall: f
NOTICE:  pgr_nodeNetwork('edges', 0.001, 'id', 'geom', 'noded', 'id in (17,14)',  f)
NOTICE:  Performing checks, please wait .....
NOTICE:  Processing, please wait .....
NOTICE:    Split Edges: 1
NOTICE:   Untouched Edges: 1
NOTICE:       Total original Edges: 2
NOTICE:   Edges generated: 2
NOTICE:   Untouched Edges: 1
NOTICE:         Total New segments: 3
NOTICE:   New Table: public.edges_noded
NOTICE:  ----------------------------------
 pgr_nodenetwork
-----------------
 OK
(1 row)

Inspecting the generated table, we can see that edge 14 has been segmented.

SELECT old_id, ST_AsText(geom) FROM edges_noded ORDER BY old_id, sub_id;
 old_id |               st_astext
--------+----------------------------------------
     14 | LINESTRING(2 3,1.999999999999 3.5)
     14 | LINESTRING(1.999999999999 3.5,2 4)
     17 | LINESTRING(0.5 3.5,1.999999999999 3.5)
(3 rows)

Update the topology

Add new segments to the edges table.

INSERT INTO edges (cost, reverse_cost, geom)
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, en.geom
FROM deadends r JOIN edges_noded en ON (old_id = edge_id) JOIN edges e ON (old_id = e.id)
UNION
SELECT 0,0,edge FROM deadends;
INSERT 0 3

Insert the intersection as new vertices.

/* Update the vertices table */
INSERT INTO vertices (id, geom)
select row_number() over() + 200, st_endpoint(edge) FROM deadends;
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 3
UPDATE edges e SET target = v.id FROM
vertices v where target IS NULL AND (st_endPoint(e.geom) = v.geom);
UPDATE 3

Delete original edge.

DELETE FROM edges WHERE id IN (SELECT edge_id FROM deadends);
DELETE 1

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 19

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 (pgr_findCloseEdges(
      'SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*,
  d.id, d.inhere
  FROM data d
)
SELECT * FROM results;
 edge_id | fraction | side |       distance        |                    geom                    |                                        edge                                        | id  | inhere
---------+----------+------+-----------------------+--------------------------------------------+------------------------------------------------------------------------------------+-----+--------
      17 |        1 | l    | 1.000088900582341e-12 | 010100000000000000000000400000000000000C40 | 01020000000200000000000000000000400000000000000C4068EEFFFFFFFFFF3F0000000000000C40 | 201 |     25
(1 row)

另请参阅

Topology - 函数族 用于路由算法的拓扑概述。

索引和表格