<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Plain Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.PlainTextChar
{mso-style-name:"Plain Text Char";
mso-style-priority:99;
mso-style-link:"Plain Text";
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoPlainText>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. <o:p></o:p></p><p class=MsoPlainText><o:p> </o:p></p><p class=MsoPlainText>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.<o:p></o:p></p><p class=MsoPlainText><o:p> </o:p></p><p class=MsoPlainText>PgRouting also does it as a one time thing so can’t really be put in a trigger.<o:p></o:p></p><p class=MsoPlainText><o:p> </o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regarding your other questions, see my comments below:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Simon SPDBA Greener<br><b>Sent:</b> Friday, July 8, 2022 1:07 AM<br><b>To:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> [postgis-users] Help with Topology Triggers sought<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Folks, <o:p></o:p></p><div><p>I have a topology built from two business tables containing linestrings (pipes) and a table containing points (pits).<o:p></o:p></p><p>The business requirement means we need the topology to clean and form common topological primitives for these lines and their nodes.<o:p></o:p></p><p>The topology will never contain polygons (faces) - should I use pgRouting instead?<o:p></o:p></p><p>While fairly static, both tables are subject to occasional DML. <o:p></o:p></p><p><b>Insert Trigger:</b><o:p></o:p></p><p>A before insert trigger (trigger function - tf) handles any additions (uses toTopoGeom).<br><br><span style='font-family:"Courier New"'>DECLARE -- pseudo code<br> v_layer_id integer := 1;<br> v_geom geometry;<br> v_topogeom topology.TopoGeometry;<br>BEGIN<br> IF ( TG_OP = 'INSERT') THEN<br> -- Create topology primitives for new linestring<br><b> v_topogeom := topology.toTopoGeom(NEW.path_line,'path', v_layer_id, 0.1);</b><br> -- The action of adding the linestring to the topology may see it become slightly different from the original NEW.path_line<br><b> v_geom := v_topogeom::geometry;<br></b> -- Then conduct the insert to the business table<br> INSERT <table> (geom,topogeom) VALUES (v_geom,v_topoGeom);<br> RETURN NEW;<br> END IF;<br>END;</span><o:p></o:p></p><p>QUESTION: If the new linestring that is added to the existing topology causes an existing underlying edge to change (eg split),<br>what happens to any other topogeom objects in the table that reference the edge before it is changed? <br>Should the affected topogeoms be found and updated to reflect any splits to edges?<o:p></o:p></p><p>I am not sure what are the best topology methods to handle updates and/or deletes.<span style='color:#1F497D'><o:p></o:p></span></p><p class=MsoPlainText><i><span style='color:#44546A'>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.<o:p></o:p></span></i></p><p class=MsoPlainText><i><span style='color:#44546A'>Take a look at the table path.relation. That is where the topogeom relationships are stored.<o:p></o:p></span></i></p><p><b><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>[Regina Obe] </span></i></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p><b>Update Trigger.</b><o:p></o:p></p><p>Here I want to construct an UPDATE trigger (tf) to handle situations where points are moved or linestrings are reshaped.<o:p></o:p></p><p>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?<br><br><span style='font-family:"Courier New"'>BEGIN -- pseudo code<br> IF ( TG_OP = 'UPDATE' ) THEN<br> -- clear any topology primitives the old topogeom uniquely referenced<br> v_topogeom := topology.clearTopoGeom(OLD.topogeom);<br> -- Construct new topology primitives for new linestring.<br> v_topogeom := topology.toTopoGeom(NEW.path_line,'path', v_layer_id, 0.1);<br><b> v_geom := v_topogeom::geometry;<br></b> -- Then update the underlying topology<br> UPDATE <table> SET topogeom = v_topogeom, geom = v_geom WHERE id = OLD.id;<br> RETURN NEW;<br> END IF;<br>END;<o:p></o:p></span></p><p><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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:<o:p></o:p></span></p><p><span style='font-family:"Courier New"'>BEGIN -- pseudo code<br> IF ( TG_OP = 'UPDATE' ) THEN<br> -- clear any topology primitives the old topogeom uniquely referenced<br> v_topogeom := topology.clearTopoGeom(OLD.topogeom);<br> -- Construct new topology primitives for new linestring.<br> v_topogeom := topology.toTopoGeom(NEW.path_line,OLD.topogeom, 0.1);<br><b> v_geom := v_topogeom::geometry;<br></b> -- Then update the underlying topology<br> UPDATE <table> SET geom = v_geom WHERE id = OLD.id;<br> RETURN NEW;<br> END IF;<br>END;</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p><b><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>[Regina Obe] <o:p></o:p></span></i></b></p><p><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p><b>Delete Trigger</b><o:p></o:p></p><p>Here I want to construct an DELETE trigger (tf). Is this all I need to do?<o:p></o:p></p><p><span style='font-family:"Courier New"'>BEGIN -- pseudo code<br> IF ( TG_OP = 'DELETE' ) THEN<br> -- Synchronize delete with underlying topology<br> v_topogeom := topology.clearTopoGeom(NEW.topogeom);<br> -- Then delete the record<br> DELETE FROM <table> WHERE id = OLD.id; <br> RETURN OLD;<br> END IF;<br>END;</span><o:p></o:p></p><p>Any help/pointers greatly appreciated.<o:p></o:p></p><p>Simon<o:p></o:p></p><p><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Yah I think that should do it.<o:p></o:p></span></i></p><p><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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. <o:p></o:p></span></i></p><p><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Sandro you have any comments on this?<o:p></o:p></span></i></p><p><b><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>[Regina Obe] <o:p></o:p></span></i></b></p><p><b><i><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></i></b></p><p><o:p> </o:p></p></div></div></div></body></html>