<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div><br>
</div>
<div>
<div id="appendonsend"></div>
<span style="font-family: Calibri, Arial, Helvetica, sans-serif;"></span>
<div><br>
<span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"></span></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"></span>
<div id="divRplyFwdMsg" dir="ltr"></div>
<div>
<div>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>On Wed, Mar 04, 2020 at 07:04:57AM +0000,
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Lars</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Aksel</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Opsahl</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
wrote:</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> When testing adding millions of
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">linestrings</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
by splitting in around 4000 content based cells, one cell was using hours for adding a few thousands lines , but by changing the order and adding the non-closed line strings first I reduced the time to around two minutes from many</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">hours for this cell. When running I started from an empty Topology for each cell.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Reading your numbers I see:</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">- Non-closed first, big first:
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">8h40m</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">- Big first (no matter closed/non-closed):
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">0h02m</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">- Closed first, big first:
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">0h02m</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Correct me if I'm wrong. I'll include your tests below.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Note that "false" comes before "true" with order by on</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>boolean (ascending, by default).</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_02',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_05</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> order by is_closed, num_points
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">desc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 31234035.504
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(08:40:34.036)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>The above adds non-closed first, and each set (non-closed, then</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>closed) it will add the lines with the most points first.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_01',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_05</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> order by num_points
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">desc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 164403.033
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(02:44.403)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>The above adds with most dense lines first, no matter closed/unclosed.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_03',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_05</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> order by is_closed
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">desc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,
num_points </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">desc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 131125.236
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(02:11.125)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>The above adds with closed first, and each set (closed, then</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>non-closed) it will add the lines with most points first.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>So, I'd derive from the above that "big first" makes a difference, no</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>matter closed/non-closed (although I'd expect the contrary, to be</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>honest).</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> But another strange thing was that time could vary a lot for the same</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>>
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">dataset</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
and order. In this test I did not care about closed or not but</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> only line length.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Yes, I'd expect this.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_01',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_02 order by num_points </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">desc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 57915.860
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(00:57.916)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_02',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_02 order by num_points </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">asc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 42424.087
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(00:42.424)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_03',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_02 order by id </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">asc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 738242.464
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(12:18.242)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 1197808.563
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(19:57.809)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 1003501.683
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(16:43.502)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> The file is here https://github.com/larsop/resolve-overlap-and-gap/blob/add_postgis_topology_TopoGeo_addLinestring_thred_grid/src/test/sql/regress/test_topology_02.dump.gz
.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>The cost of each addition is the cost of:</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">1. Finding all edges and nodes intersecting the new line</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">2.
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Noding</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
the new line with existing nodes/edges, split accordingly</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">3. For each resulting split-segment:</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">3.1. Finding all edges and nodes intersecting the segment</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">(to bail out if found an intersecting one)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">3.2. Finding edge rings on both sides of the edge</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">(to determine if a new face is created)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Roughly, the above is the algorithm. Can probably be improved in</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>some steps, but you can see how the order in which things are</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>added matters. In general, reducing (by splitting) the length</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>of lines and (by adding connecting edges) the area of</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>faces should speed things up. Assuming indexes are correctly used</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>(is
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">autovacuum</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
on?).</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Hi</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
again</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">- Yes we have
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">autovacum</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
on.</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">- I made some very simple code where I tried group edges together but that did not seem to help to much on performance,</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 0); font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background: var(--white);">but
that may be related to that I am not making clean directed graph.</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<div style="margin: 0px; min-height: 14px;">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height: 14.0px">
<br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">DO $$</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f0055"><b>DECLARE</b></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>tables_non_closed
<span style="color: #7f0055"><b>CURSOR</b></span> <span style="color: #000080">FOR</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span><span style="color: #7f0055"><b>select</b></span> id,geom,num_points
<span style="color: #7f0055"><b>from</b></span> test_topology_02 <span style="color: #7f0055">
<b>where</b></span> ST_Isclosed(geom) = <span style="color: #4000c8"><b>false</b></span>
<span style="color: #7f0055"><b>order</b></span> <span style="color: #7f0055"><b>by</b></span> num_points
<span style="color: #7f0055"><b>desc</b></span>;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height: 14.0px">
<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>working_geo geometry;<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f0055"><b>BEGIN</b></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span><span style="color: #7f0055"><b>drop</b></span>
<span style="color: #7f0055"><b>table</b></span> if <span style="color: #7f0055">
<b>exists</b></span> test_tt;<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span><span style="color: #7f0055"><b>create</b></span>
<span style="color: #7f0055"><b>table</b></span> test_tt <span style="color: #7f0055">
<b>as</b></span> (<span style="color: #7f0055"><b>select</b></span> geom,num_points
<span style="color: #7f0055"><b>from</b></span> test_topology_02 <span style="color: #7f0055">
<b>where</b></span> ST_Isclosed(geom) = <span style="color: #4000c8"><b>false</b></span>
<span style="color: #7f0055"><b>order</b></span> <span style="color: #7f0055"><b>by</b></span> num_points
<span style="color: #7f0055"><b>desc</b></span>);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height: 14.0px">
<span></span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span><span style="color: #7f0055"><b>drop</b></span>
<span style="color: #7f0055"><b>table</b></span> if <span style="color: #7f0055">
<b>exists</b></span> test_tt4;<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span><span style="color: #7f0055"><b>create</b></span>
<span style="color: #7f0055"><b>table</b></span> test_tt4 <span style="color: #7f0055">
<b>as</b></span> <span style="color: #7f0055"><b>select</b></span> * <span style="color: #7f0055">
<b>from</b></span> test_tt limit 0;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height: 14.0px">
<span></span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span><span style="color: #7f0055"><b>alter</b></span>
<span style="color: #7f0055"><b>table</b></span><span> </span>test_tt4 <span style="color: #7f0055">
<b>add</b></span> <span style="color: #7f0055"><b>column</b></span> id serial;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span><span style="color: #7f0055"><b>INSERT</b></span>
<span style="color: #7f0055"><b>INTO</b></span> test_tt4(geom)<span> </span><span style="color: #7f0055"><b>select</b></span> geom
<span style="color: #7f0055"><b>from</b></span> test_topology_02 <span style="color: #7f0055">
<b>where</b></span> ST_Isclosed(geom) = <span style="color: #4000c8"><b>true</b></span>
<span style="color: #7f0055"><b>order</b></span> <span style="color: #7f0055"><b>by</b></span> num_points
<span style="color: #7f0055"><b>desc</b></span>;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height: 14.0px">
<span></span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span>working_geo =
<span style="color: #4000c8"><b>null</b></span>;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span><span style="color: #000080">FOR</span> table_record
<span style="color: #7f0055"><b>IN</b></span> tables_non_closed LOOP</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>
working_geo = table_record.geom;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>
LOOP<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>
Select ST_Union(geom) <span style="color: #7f0055"><b>from</b></span> test_tt <span style="color: #7f0055">
<b>where</b></span> ST_Intersects(geom,working_geo) <span style="color: #7f0055">
<b>into</b></span> working_geo;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>
EXIT WHEN working_geo <span style="color: #7f0055"><b>is</b></span> <span style="color: #4000c8">
<b>null</b></span>;<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>
<span style="color: #7f0055"><b>INSERT</b></span> <span style="color: #7f0055"><b>into</b></span> test_tt4
<span style="color: #7f0055"><b>select</b></span> * <span style="color: #7f0055">
<b>from</b></span> test_tt <span style="color: #7f0055"><b>where</b></span> ST_Intersects(geom,working_geo)
<span style="color: #7f0055"><b>order</b></span> <span style="color: #7f0055"><b>by</b></span> num_points;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span>
<span style="color: #7f0055"><b>DELETE</b></span> <span style="color: #7f0055"><b>from</b></span> test_tt
<span style="color: #7f0055"><b>where</b></span> ST_Intersects(geom,working_geo);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></span><span>
<span></span></span><span style="color: #7f0055"><b>END</b></span> LOOP ;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span> </span><span style="color: #7f0055"><b>END</b></span> LOOP;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">END$$;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height: 14.0px">
<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(127, 0, 85);"><b>select</b></span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; background: var(--white);">
topology.CreateTopology (</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 255);">'test_topo_04'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; background: var(--white);">,
4258,0.000001);</span><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="color: #7f0055"><b>select</b></span> topology.TopoGeo_AddLineString(<span style="color: #0000ff">'test_topo_04'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span><span> </span>test_tt4 <span style="color: #7f0055">
<b>order</b></span> <span style="color: #7f0055"><b>by</b></span> id;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Time: 138741.013 ms (02:18.741)</p>
<br>
<br>
</div>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">- The randomness for the three same calls with same order I did not understand. The time vary from almost 20
minutes to 12 minutes.</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> select topology.TopoGeo_AddLineString('test_topo_03',</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geom</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,0.000001)
from test_topology_02 order by id </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">asc</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 738242.464
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(12:18.242)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 1197808.563
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(19:57.809)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>> Time: 1003501.683
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(16:43.502)</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Thanks a lot.</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Lars</span></p>
<p style="margin: 0px; min-height: 14px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>--</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">strk</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>_______________________________________________</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">postgis</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-users
mailing list</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">postgis</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-users</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">@lists.osgeo.org</span></p>
<p style="margin: 0px; font-stretch: normal; line-height: normal;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>https://lists.osgeo.org/mailman/listinfo/postgis-users</span></p>
</div>
<font size="2"></font></div>
</div>
</body>
</html>