Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generate a network geometry edge table in PostGIS according to a edge table and topology relations (to/from nodes) - with triggers
/*
Vincent Picavet <vincent.picavet@oslandia.com>
Work distributed under MIT Licence.
Automated generation of edges geometries according to topology and nodes geometry
A nodes table and a edges table design a network.
Edges are straight lines between id_from and id_to nodes
The edge geometries are automatically recomputed when nodes geometries are updated (or deleted)
and also when the id_to / id_from attributes of the edges are updated.
This script is self sufficient and uses PostGIS 2.x
You can test with QGIS :
* Open nodes and edges tables in QGIS
* Edit nodes layer
* Move a node geometry and save modifications -> the edges linked will move too
* Edit edges layer
* Change a id_to or id_from attribute for an edge and save -> the edge's geometry will be updated
*/
create extension if not exists postgis;
drop table if exists nodes cascade;
create table nodes (
gid serial primary key
, nameid varchar
, geom geometry(Point, 2154)
);
drop table if exists edges cascade;
create table edges (
gid serial primary key
, id_from integer references nodes(gid)
, id_to integer references nodes(gid)
, edge_name varchar
, diameter double precision default 10.0
, edge_type varchar default 'grey_water'
, geom geometry(LineString, 2154)
);
/* Insert some test data with QGIS in the node table */
INSERT INTO nodes VALUES (1, 'Point 1', '01010000206A08000062F0D06B1D112941E4FD0AF088CD5841');
INSERT INTO nodes VALUES (2, 'Point 2', '01010000206A08000088C088BF4918294187E187FB9ECC5841');
INSERT INTO nodes VALUES (3, 'Point 3', '01010000206A0800007C9EFDC4A10E2941B8DA3896B0CA5841');
INSERT INTO nodes VALUES (4, 'Point 4', '01010000206A080000C54C490575F728418DC16BD808CA5841');
INSERT INTO nodes VALUES (5, 'Point 5', '01010000206A08000008BD5119AB2129414497C9A9E5CE5841');
INSERT INTO nodes VALUES (6, 'Point 6', '01010000206A080000CF04BE9BE81629417C072D944FCE5841');
INSERT INTO nodes VALUES (7, 'Point 7', '01010000206A080000270332BC40112941CBC214C046CE5841');
/*
insert edges links infos :
1-2, 2-3, 1-4, 1-5, 6-7, 7-1
*/
insert into edges (id_from, id_to) values (1, 2), (2, 3), (1, 4), (1, 5), (6, 7), (7, 1);
/* Generate geometry lines : straight lines between nodes */
update
edges as e
set
geom = st_makeline(n1.geom, n2.geom)
from
nodes as n1
, nodes as n2
where
e.id_from = n1.gid
and e.id_to = n2.gid;
-- add trigger to the node table to update the edge table
create or replace function update_node_edge() returns trigger as
$$
begin
-- delete : delete any edge connected to the deleted node
IF (TG_OP = 'DELETE') THEN
delete from edge as e where id_from = OLD.gid or id_to = OLD.gid;
return OLD;
-- update : if a node is updated, then regenerate any edge referencing it
elsif (TG_OP = 'UPDATE') THEN
-- update edges starting from modified node
update
edges as e
set
geom = st_makeline(NEW.geom, n2.geom)
from
nodes as n2
where
-- join condition
e.id_to = n2.gid
-- all edges starting from modified node
and e.id_from = NEW.gid;
-- update edges ending at modified node
update
edges as e
set
geom = st_makeline(n1.geom, NEW.geom)
from
nodes as n1
where
-- join condition
e.id_from = n1.gid
-- all edges ending at modified node
and e.id_to = NEW.gid;
return NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_node_edges ON nodes;
CREATE TRIGGER trg_node_edges AFTER DELETE OR UPDATE ON nodes
FOR EACH ROW EXECUTE PROCEDURE update_node_edge();
-- add trigger to the edges table to update the geometry when to/from changes
create or replace function update_edge_attr() returns trigger as
$$
begin
-- update : if id_to or id_from is modified, update the geometry
if (TG_OP = 'UPDATE') THEN
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;
return NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_edges_attr ON edges;
CREATE TRIGGER trg_edges_attr BEFORE UPDATE ON edges
FOR EACH ROW EXECUTE PROCEDURE update_edge_attr();
@hchristol

This comment has been minimized.

Copy link

@hchristol hchristol commented May 23, 2014

Thank you, very useful for network update !

Your trigger works well only on segments, so I have add this :

geom = ST_AddPoint( ST_RemovePoint(geom, ST_NPoints(geom) - 1), NEW.the_geom, ST_NPoints(geom) - 1 )

For linestring with more than 2 points (instead of st_makeline).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment