[postgis-users] Help with Topology Triggers sought

[Simon SPDBA Greener]

Folks,

I have a topology built from two business tables containing linestrings 
(pipes) and a table containing points (pits).

The business requirement means we need the topology to clean and form 
common topological primitives for these lines and their nodes.

The topology will never contain polygons (faces) - should I use 
pgRouting instead?

While fairly static, both tables are subject to occasional DML.

*Insert Trigger:*

A before insert trigger (trigger function - tf) handles any additions 
(uses toTopoGeom).

DECLARE -- pseudo code
v_layer_id integer := 1;
   v_geom     geometry;
   v_topogeom topology.TopoGeometry;
BEGIN
   IF ( TG_OP = 'INSERT') THEN
     -- Create topology primitives for new linestring
*    v_topogeom := topology.toTopoGeom(NEW.path_line,'path', v_layer_id, 
0.1);*
     -- The action of adding the linestring to the topology may see it 
become slightly different from the original NEW.path_line
*    v_geom := v_topogeom::geometry;**
***    -- Then conduct the insert to the business table
     INSERT <table> (geom,topogeom) VALUES (v_geom,v_topoGeom);
     RETURN NEW;
   END IF;
END;

QUESTION: If the new linestring that is added to the existing topology 
causes an existing underlying edge to change (eg split),
what happens to any other topogeom objects in the table that reference 
the edge before it is changed?
Should the affected topogeoms be found and updated to reflect any splits 
to edges?

I am not sure what are the best topology methods to handle updates 
and/or deletes.

*Update Trigger.*

Here I want to construct an UPDATE trigger (tf) to handle situations 
where points are moved or linestrings are reshaped.

In both situations the existing topogeoms point to existing topological 
primitives. To handle a change should I first delete the existing 
topogeom using clearTopoGeom() which modifies the underlying topology, 
and then use toTopoGeom to construct the new topological primitives and 
the topogeom value?

BEGIN -- pseudo code
   IF ( TG_OP = 'UPDATE' ) THEN
     -- clear any topology primitives the old topogeom uniquely referenced
     v_topogeom := topology.clearTopoGeom(OLD.topogeom);
     -- Construct new topology primitives for new linestring.
     v_topogeom := topology.toTopoGeom(NEW.path_line,'path', v_layer_id, 
0.1);
*v_geom := v_topogeom::geometry;**
***    -- Then update the underlying topology
     UPDATE <table> SET topogeom = v_topogeom, geom = v_geom WHERE id = 
OLD.id;
     RETURN NEW;
   END IF;
END;

*Delete Trigger*

Here I want to construct an DELETE trigger (tf). Is this all I need to do?

BEGIN -- pseudo code
   IF ( TG_OP = 'DELETE' ) THEN
     -- Synchronize delete with underlying topology
     v_topogeom := topology.clearTopoGeom(NEW.topogeom);
     -- Then delete the record
     DELETE FROM <table> WHERE id = OLD.id;
     RETURN OLD;
   END IF;
END;

Any help/pointers greatly appreciated.

Simon

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220708/1d8d05c8/attachment.htm>