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 - Nodes an network edge table.

Author:: Nicolas Ribot
Copyright:: Nicolas Ribot, The source code is released under the MIT-X license.

The function reads edges from a not “noded” network table and writes the “noded” edges into a new table.

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

| RETURNS TEXT

Availability

Version 3.8.0
- Not checking and not creating indexes.
- Using pgr_separateTouching and pgr_separateCrossing.
- Created table with BIGINT.
Version 2.0.0
- Official function.

Description¶

The main characteristics are:

A common problem associated with bringing GIS data into pgRouting is the fact that the data is often not “noded” correctly. This will create invalid topologies, which will result in routes that are incorrect.

What we mean by “noded” is that at every intersection in the road network all the edges will be broken into separate road segments. There are cases like an over-pass and under-pass intersection where you can not traverse from the over-pass to the under-pass, but this function does not have the ability to detect and accommodate those situations.

This function reads the edge_table table, that has a primary key column id and geometry column named the_geom and intersect all the segments in it against all the other segments and then creates a table edge_table_noded. It uses the tolerance for deciding that multiple nodes within the tolerance are considered the same node.

Parameters¶

edge_table:: text Network table name. (may contain the schema name as well)
tolerance:: float8 tolerance for coincident points (in projection unit)dd
id:: text Primary key column name of the network table. Default value is id.
the_geom:: text Geometry column name of the network table. Default value is the_geom.
table_ending:: text Suffix for the new table’s. Default value is noded.

The output table will have for edge_table_noded

id:: bigint Unique identifier for the table
old_id:: bigint Identifier of the edge in original table
sub_id:: integer Segment number of the original edge
source:: bigint Empty source column
target:: bigint Empty target column
the geom:: geometry Geometry column of the noded network

Examples¶

Create the topology for the data in Sample Data

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