Generate a network geometry edge table in PostGIS according to a edge table and topology relations (to/from nodes) - with triggers

node_edges.sql

/* 
Vincent Picavet <[email protected]>

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();

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).