[postgis-users] Help with Topology Triggers sought

[Regina Obe]

Sorry one more thought came to mind and now I realize the issue is more complicated than I had originally thought.

 

For the moving a primitive point, you’d want to use

https://postgis.net/docs/ ST_MoveIsoNode.html <https://postgis.net/docs/%20ST_MoveIsoNode.html> 

 

So your trigger might look something like:

DECLARE v_result text;

 

-- you want to prevent triggering resulting from change of computed geom column as that could result in an infinite loop.

  IF ( TG_OP = 'UPDATE' AND OLD.topo.layer_id = 1 AND OLD.geom = NEW.geom) THEN
    -- Get old node id
    v_node_id = (GetTopoGeomElementArray(OLD.topo))[1][1];

    v_result = ST_MoveIsoNode('path', v_node_id,  NEW.path_point);
    v_geom := v_topogeom::geometry;
    -- Then update the underlying topology
    UPDATE <table> SET geom = v_geom WHERE id = OLD.id;

-- there has to be an update here to change the affected edges

-- something like:

UPDATE <table> SET geom = topo::geometry WHERE ST_Intersects(topo, NEW.topo);
    RETURN NEW;
  END IF;

 

-- the edge case is a bit trickier as an edge might be composed of multiple primitives, if it is just one then it would be:

https://postgis.net/docs/ ST_ChangeEdgeGeom.html <https://postgis.net/docs/%20ST_ChangeEdgeGeom.html> 

  IF ( TG_OP = 'UPDATE' AND OLD.topo.layer_id = 2 AND OLD.geom = NEW.geom) THEN
    -- Get old node id
    v_edge_id = (GetTopoGeomElementArray(OLD.topo))[1][1];

    v_result = ST_ChangeEdgeGeom('path', v_edge_id,  NEW.path_line);

    -- Then update the underlying topology
    UPDATE <table> SET geom = topo::geometry WHERE id = OLD.id;

    UPDATE <table> SET geom = topo::geometry WHERE ST_Intersects(topo, NEW.topo);
    RETURN NEW;
  END IF;

 

For the case where an edge was split, I have to think that thru some more.

I suspect then you’d want to do the clearTopoGeom route, but delete the old edges from the system if they have no related topo geoms and do not have nodes related to others.

 

I also found a lot of documentation errors.  Sorry about that will fix them in a bit and thanks for highlighting that.

 

Thanks,

Regina

 

 

From: Regina Obe [mailto:lr at pcorp.us] 
Sent: Sunday, July 10, 2022 2:41 PM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Cc: 'strk at kbt.io' <strk at kbt.io>
Subject: RE: [postgis-users] Help with Topology Triggers sought

 

I don’t think pgRouting would serve your needs. I think postgis topology is a better fit.  Pgr_CreateTopology would only create nodes at each line segment and in doing so would create a points table and ascribe the node ids to that point.  It will not inject the points in your line work if they are not already there, which it sounds like you might want.  

 

That said the newer function -- https://docs.pgrouting.org/latest/en/pgr_extractVertices.html might be a better fit to allow you to pick the pieces you want.

 

PgRouting also does it as a one time thing so can’t really be put in a trigger.

 

Regarding your other questions, see my comments below:

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Simon SPDBA Greener
Sent: Friday, July 8, 2022 1:07 AM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: [postgis-users] Help with Topology Triggers sought

 

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.

They would be changed as well.  The topogeom is defined as a relation of other topo elements or primitives.   So whenever you add a new edge, related edges are readjusted.  E.g. if you break an edge into two, the topo geom relations would be adjusted too and have those two new edge ids in the relation and the old one removed.

Take a look at the table path.relation. That is where the topogeom relationships are stored.

[Regina Obe] 

 

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;

This looks almost right, except I think you are using the wrong version of toTopoGeom.  What you want is to maintain the same id but fill with new data, so I would change to this:

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,OLD.topogeom, 0.1);
    v_geom := v_topogeom::geometry;
    -- Then update the underlying topology
    UPDATE <table> SET geom = v_geom WHERE id = OLD.id;
    RETURN NEW;
  END IF;
END;

[Regina Obe] 

 

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

Yah I think that should do it.

One thing I am unclear about with clearTopogeom is if it would anneal edges and remove edges no longer used.  I suspect not.  But I’m thinking you are hoping this will happen. 

Sandro you have any comments on this?

[Regina Obe] 

 

 

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