· 9 years ago · Oct 28, 2016, 02:12 PM
1/*
2Vincent Picavet <vincent.picavet@oslandia.com>
3
4Work distributed under MIT Licence.
5
6Automated generation of edges geometries according to topology and nodes geometry
7
8A nodes table and a edges table design a network.
9Edges are straight lines between id_from and id_to nodes
10
11The edge geometries are automatically recomputed when nodes geometries are updated (or deleted)
12and also when the id_to / id_from attributes of the edges are updated.
13
14This script is self sufficient and uses PostGIS 2.x
15You can test with QGIS :
16* Open nodes and edges tables in QGIS
17* Edit nodes layer
18* Move a node geometry and save modifications -> the edges linked will move too
19* Edit edges layer
20* Change a id_to or id_from attribute for an edge and save -> the edge's geometry will be updated
21
22
23*/
24
25create extension if not exists postgis;
26
27drop table if exists nodes cascade;
28create table nodes (
29 gid serial primary key
30 , nameid varchar
31 , geom geometry(Point, 2154)
32);
33
34drop table if exists edges cascade;
35create table edges (
36 gid serial primary key
37 , id_from integer references nodes(gid)
38 , id_to integer references nodes(gid)
39 , edge_name varchar
40 , diameter double precision default 10.0
41 , edge_type varchar default 'grey_water'
42 , geom geometry(LineString, 2154)
43);
44
45/* Insert some test data with QGIS in the node table */
46INSERT INTO nodes VALUES (1, 'Point 1', '01010000206A08000062F0D06B1D112941E4FD0AF088CD5841');
47INSERT INTO nodes VALUES (2, 'Point 2', '01010000206A08000088C088BF4918294187E187FB9ECC5841');
48INSERT INTO nodes VALUES (3, 'Point 3', '01010000206A0800007C9EFDC4A10E2941B8DA3896B0CA5841');
49INSERT INTO nodes VALUES (4, 'Point 4', '01010000206A080000C54C490575F728418DC16BD808CA5841');
50INSERT INTO nodes VALUES (5, 'Point 5', '01010000206A08000008BD5119AB2129414497C9A9E5CE5841');
51INSERT INTO nodes VALUES (6, 'Point 6', '01010000206A080000CF04BE9BE81629417C072D944FCE5841');
52INSERT INTO nodes VALUES (7, 'Point 7', '01010000206A080000270332BC40112941CBC214C046CE5841');
53
54/*
55insert edges links infos :
561-2, 2-3, 1-4, 1-5, 6-7, 7-1
57*/
58insert into edges (id_from, id_to) values (1, 2), (2, 3), (1, 4), (1, 5), (6, 7), (7, 1);
59
60/* Generate geometry lines : straight lines between nodes */
61update
62 edges as e
63set
64 geom = st_makeline(n1.geom, n2.geom)
65from
66 nodes as n1
67 , nodes as n2
68where
69 e.id_from = n1.gid
70 and e.id_to = n2.gid;
71
72
73-- add trigger to the node table to update the edge table
74create or replace function update_node_edge() returns trigger as
75$$
76begin
77 -- delete : delete any edge connected to the deleted node
78 IF (TG_OP = 'DELETE') THEN
79 delete from edge as e where id_from = OLD.gid or id_to = OLD.gid;
80 return OLD;
81 -- update : if a node is updated, then regenerate any edge referencing it
82 elsif (TG_OP = 'UPDATE') THEN
83 -- update edges starting from modified node
84 update
85 edges as e
86 set
87 geom = st_makeline(NEW.geom, n2.geom)
88 from
89 nodes as n2
90 where
91 -- join condition
92 e.id_to = n2.gid
93 -- all edges starting from modified node
94 and e.id_from = NEW.gid;
95 -- update edges ending at modified node
96 update
97 edges as e
98 set
99 geom = st_makeline(n1.geom, NEW.geom)
100 from
101 nodes as n1
102 where
103 -- join condition
104 e.id_from = n1.gid
105 -- all edges ending at modified node
106 and e.id_to = NEW.gid;
107 return NEW;
108 END IF;
109END;
110$$ LANGUAGE plpgsql;
111
112DROP TRIGGER IF EXISTS trg_node_edges ON nodes;
113CREATE TRIGGER trg_node_edges AFTER DELETE OR UPDATE ON nodes
114 FOR EACH ROW EXECUTE PROCEDURE update_node_edge();
115
116-- add trigger to the edges table to update the geometry when to/from changes
117create or replace function update_edge_attr() returns trigger as
118$$
119begin
120 -- update : if id_to or id_from is modified, update the geometry
121 if (TG_OP = 'UPDATE') THEN
122 NEW.geom := st_makeline(n1.geom, n2.geom) from nodes as n1, nodes as n2 where n1.gid = NEW.id_from and n2.gid = NEW.id_to;
123 return NEW;
124 END IF;
125END;
126$$ LANGUAGE plpgsql;
127
128DROP TRIGGER IF EXISTS trg_edges_attr ON edges;
129CREATE TRIGGER trg_edges_attr BEFORE UPDATE ON edges
130 FOR EACH ROW EXECUTE PROCEDURE update_edge_attr();