<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 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 style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
When testing adding millions of linestrings 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 hours for this cell. When running I started from an empty Topology for each cell.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</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"><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; color: rgb(0, 0, 0); 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_02'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 0); 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_02'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span> test_topology_05<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="color: #7f0055"><b>order</b></span>
<span style="color: #7f0055"><b>by</b></span> is_closed, 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">Time: 31234035.504 ms (08:40:34.036)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">(The server was doing other work while part of this test was running, but it was enough CPU left for test)</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; color: rgb(0, 0, 0); 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_01'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 0); 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_01'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span> test_topology_05<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><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">Time: 164403.033 ms (02:44.403)</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; color: rgb(0, 0, 0); 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_03'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 0); 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_03'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span> test_topology_05<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="color: #7f0055"><b>order</b></span>
<span style="color: #7f0055"><b>by</b></span> is_closed <span style="color: #7f0055">
<b>desc</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">Time: 131125.236 ms (02:11.125)</p>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
<span style="margin: 0px; background: var(--white)"><span>The <span style="caret-color: rgb(0, 0, 0); font-family: Menlo; font-size: 12px; background-color: rgb(255, 255, 255); display: inline !important">
test_topology_05 </span>is located her </span></span><a href="https://github.com/larsop/resolve-overlap-and-gap/blob/add_postgis_topology_TopoGeo_addLinestring_thred_grid/src/test/sql/regress/test_topology_05.dump.gz" style="margin: 0px">https://github.com/larsop/resolve-overlap-and-gap/blob/add_postgis_topology_TopoGeo_addLinestring_thred_grid/src/test/sql/regress/test_topology_05.dump.gz</a><span style="margin: 0px; background: var(--white)"> f
. </span></div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
There was also some other cells that used 20 minutes and by changing the order it was reduced to less than a minute. But another strange thing was that time could vary a lot for the same dataset and order. In this test I did not care about closed or not but
only line length.</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="color: #7f0055"><b>select</b></span> topology.DropTopology (<span style="color: #0000ff">'test_topo_02'</span>);<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;">
topology.DropTopology (</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 255);">'test_topo_01'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal;">);</span><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;">
topology.DropTopology (</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 255);">'test_topo_03'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal;">);</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><br>
</p>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span style="color: #7f0055"><b>select</b></span> topology.CreateTopology (<span style="color: #0000ff">'test_topo_01'</span>, 4258,0.000001);</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_01'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span> test_topology_02 <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">Time: 57915.860 ms (00:57.916)</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="color: #7f0055"><b>select</b></span> topology.CreateTopology (<span style="color: #0000ff">'test_topo_02'</span>, 4258,0.000001);</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_02'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span> test_topology_02 <span style="color: #7f0055">
<b>order</b></span> <span style="color: #7f0055"><b>by</b></span> num_points <span style="color: #7f0055">
<b>asc</b></span>;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Time: 42424.087 ms (00:42.424)</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;">
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_03'</span><span style="font-family: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal;">,
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_03'</span>,geom,0.000001)
<span style="color: #7f0055"><b>from</b></span> test_topology_02 <span style="color: #7f0055">
<b>order</b></span> <span style="color: #7f0055"><b>by</b></span> id <span style="color: #7f0055">
<b>asc</b></span>;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Time: 738242.464 ms (12:18.242)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Time: 1197808.563 ms (19:57.809)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Time: 1003501.683 ms (16:43.502)</p>
<br>
The file is here <a href="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" id="LPlnk834076">
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</a> .</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background-color: rgb(255, 255, 255)">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Menlo; color: #000000"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Menlo; color: #000000">Running on this setup.</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Menlo; color: #000000"><br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures">POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 6.2.1, November 1st, 2019" GDAL="GDAL
3.0.4, released 2020/01/28" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" TOPOLOGY RASTER</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures"><br>
</span></p>
</div>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Thanks.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Lars</div>
</body>
</html>