1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
/* :file: This file is part of the pgRouting project.
:copyright: Copyright (c) 2025-2026 pgRouting developers
:license: Creative Commons Attribution-Share Alike 3.0 https://creativecommons.org/licenses/by-sa/3.0 */
-- pgr_nodeNetwork
--------------------------------------------------------------------------------
/* --q1 */
SELECT *
FROM pgr_separateTouching('SELECT id, geom FROM edges');
/* --q2 */
SELECT *
FROM pgr_separateTouching('SELECT id, geom FROM edges', dryrun => true);
/* --q3 */
SELECT *
FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM edges', 1, 2);
/* --q4 */
WITH
deadends AS (
SELECT id AS vid, (in_edges || out_edges)[1] AS edge, geom AS vgeom
FROM vertices
WHERE array_length(in_edges || out_edges, 1) = 1
)
SELECT id, ST_AsText(geom), vid, ST_AsText(vgeom), ST_Distance(geom, vgeom)
FROM edges, deadends
WHERE id != edge AND ST_Distance(geom, vgeom) < 0.1;
/* --q5 */
ALTER TABLE edges ADD old_id BIGINT;
/* --q6 */
INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateTouching('SELECT id, geom FROM edges');
/* --q7 */
WITH
costs AS (
SELECT e2.id,
sign(e1.cost) * ST_Length(e2.geom) AS cost,
sign(e1.reverse_cost) * ST_Length(e2.geom) AS reverse_cost
FROM edges e1
JOIN edges e2 ON (e1.id = e2.old_id)
)
UPDATE edges e SET (cost, reverse_cost) = (c.cost, c.reverse_cost)
FROM costs AS c
WHERE e.id = c.id;
/* --q8 */
WITH new_vertex AS (
SELECT ev.*
FROM pgr_extractVertices('SELECT id, geom FROM edges WHERE old_id IS NOT NULL') ev
LEFT JOIN vertices v using(geom)
WHERE v IS NULL
)
INSERT INTO vertices (in_edges, out_edges,x,y,geom)
SELECT in_edges, out_edges,x,y,geom
FROM new_vertex;
/* --q9 */
/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
/* --q10 */
SELECT *
FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM edges', 1, 2);
/* --q11 */
/* --qend */
|
