<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div>
<div id="appendonsend"></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>From: <span style="text-decoration: underline">
postgis</span>-users <postgis-users-bounces<span style="text-decoration: underline">@lists.osgeo.org</span>> on behalf of
<span style="text-decoration: underline">Sandro</span> <span style="text-decoration: underline">
Santilli</span> <strk<span style="text-decoration: underline">@kbt.io</span>></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>Sent: Wednesday, February 26, 2020 11:18 AM</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>To: PostGIS Users Discussion <postgis-users<span style="text-decoration: underline">@lists.osgeo.org</span>></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>Subject: Re: [<span style="text-decoration: underline">postgis</span>-users]
<span style="text-decoration: underline">Postgis</span> Topology and slow topology.TopoGeo_addLinestring on big
<span style="text-decoration: underline">datasets</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>On Wed, <span style="text-decoration: underline">
Feb</span> 26, 2020 at 09:59:52AM +0000, <span style="text-decoration: underline">
Lars</span> <span style="text-decoration: underline">Aksel</span> <span style="text-decoration: underline">
Opsahl</span> wrote:</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> <span> </span>
* <span> </span>The output from pg_stat_statements is below, The only strange thing I see here is that the query “SELECT edge_id,<span style="text-decoration: underline">geom</span> FROM "test_topo_ar5".edge_data WHERE edge_id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,”
has length of 599090</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>It sounds like a big "edge ring", that is a face bound by many edges.</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>You should be able to speed things up by cutting that big face into</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>smaller faces. Since you already made a grid you may start by adding</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>edges of that grid in the topology.</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Yes I can do that but then I have to remove this extra lines when done.</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="color: rgb(0, 0, 0); font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; background: var(--white);">This should by able to do by checking if two faces
that share edge belongs the same original polygon.</span><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 0); background: var(--white);">>Note that every time you add an edge with TopoGeo_addLinestring,
the</span><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>code tries to determine if a new face is created by walking on that</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>"edge ring" (which is done using that query you mention). The
<span style="text-decoration: underline">Tuscany</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>Region had funded, for
<span style="text-decoration: underline">spatialite</span>, code to prevent that step while</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>adding <span style="text-decoration: underline">
linestrings</span>, for doing it at the very end, when all edges</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>are in place. Doing so somewhat speed things up, because at the time</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>in which faces are computed, all edges are in place and thus there</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>each "edge ring" is computed exactly once instead of per-every-edge</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>added.</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>The work was never ported back to PostGIS because it would require</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>defining a policy to leave a topology in invalid state (with faces</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>being formed but not marked as such) and I personally didn't find</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>the time to work on that. An initial work to implement this in</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>ST_CreateTopoGeo (which would not have the problem of leaving the</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>topology in an invalid state, given it all happens in a single</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>transaction) is here: https://git.osgeo.org/gitea/postgis/postgis/pulls/28</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">If checkout this code "<span style="background: var(--white);">git fetch origin pull/28/head:batch-topo2" is any problem for me to compile this code and test it ?</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Or do I have to do a lot changes in my own code to test it ?</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"> </p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Thanks a lot.</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Lars</p>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt"></span></font></div>
</div>
</body>
</html>