<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>Hello, </div>
<div><br>
</div>
<div>Seems there is a bug when trying to add some specific lines to a topology, you can find a SQL script that creates the necessary table and topology, and adds the lines in certain order. When adding the last line, an error occurs:
<br>
</div>
<div>WARNING: Loading of record 5 failed: Corrupted topology: ring of edge -7 is geometrically not-closed
</div>
<div><br>
</div>
<div>On the loop, if we order by id DESC, all lines are added to the topology. </div>
<div><br>
</div>
<div>This problem only occurs if its a 3D topology. If we work on 2D only, no error is raised.
</div>
<div><br>
</div>
<div>We also tried to order the query with a subquery (the commented code), but always with the error, it seems the query is not being ordered.
<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>DROP TABLE IF EXISTS lines;
<div>CREATE TABLE lines (</div>
<div> id serial PRIMARY KEY,</div>
<div> geom geometry(LINESTRINGZ, 3763) NOT NULL</div>
<div>);</div>
<div><br>
</div>
<div>CREATE INDEX idx_lines_geom ON lines USING gist(geom);</div>
<div><br>
</div>
<div>INSERT INTO lines (geom) values</div>
<div>('01020000A0B30E000005000000295C8FC235C5F040295C8FC2BF8D0641713D0AD7A350774066666666FEC4F040B81E85EBD78D0641713D0AD7A350774015AE47E112C5F040E17A14AEE38D06417B14AE47E14E7740CDCCCCCC4CC5F040AE47E17ACA8D0641F6285C8FC24D7740295C8FC245C5F04067666666C68D064152B81E85EB4D7740'),</div>
<div>('01020000A0B30E000002000000295C8FC235C5F040295C8FC2BF8D0641713D0AD7A35077406666666626C5F040D7A3703DA08D0641AE47E17A14567740'),</div>
<div>('01020000A0B30E0000060000009A999999B1C4F0405C8FC2F59A8D064133333333338F7740C3F5285CC3C4F0403E0AD7A3B48D064133333333338F774000000000C8C4F0400AD7A370C98D064148E17A14AE8B77403333333307C5F0400AD7A370C98D064167666666668A774048E17A1406C5F0403E0AD7A3A68D064148E17A14AE8777409A99999911C5F0403E0AD7A3A48D0641C3F5285C8F867740'),</div>
<div>('01020000A0B30E0000020000009A99999911C5F0403E0AD7A3A48D0641C3F5285C8F8677406666666626C5F040D7A3703DA08D0641C3F5285C8F867740'),</div>
<div>('01020000A0B30E00000200000066666666FEC4F040B81E85EBD78D0641713D0AD7A3507740F6285C8FE6C4F0400AD7A370C98D06410000000000607740');</div>
<div><br>
</div>
<div><br>
</div>
<div>SELECT topology.droptopology('lines_topo')</div>
<div>WHERE EXISTS (</div>
<div> SELECT * FROM topology.topology WHERE name = 'lines_topo'</div>
<div>);</div>
<div><br>
</div>
<div>SELECT topology.CreateTopology('lines_topo', 3763, 0, true);</div>
<div><br>
</div>
<div>SELECT topology.AddTopoGeometryColumn('lines_topo', 'public', 'lines', 'topo', 'LINE');</div>
<div><br>
</div>
<div>-- UPDATE lines</div>
<div>-- SET topo = topology.totopogeom(geom, 'lines_topo', 1, 0)</div>
<div>-- WHERE id in (</div>
<div>-- SELECT id</div>
<div>-- FROM lines</div>
<div>-- ORDER BY id DESC</div>
<div>-- FOR UPDATE</div>
<div>-- );</div>
<div><br>
</div>
<div>DO</div>
<div>$$</div>
<div> DECLARE</div>
<div> id_line integer;</div>
<div> BEGIN</div>
<div> FOR id_line IN SELECT id FROM lines ORDER BY id --DESC</div>
<div> LOOP</div>
<div> BEGIN</div>
<div> UPDATE lines</div>
<div> SET topo = topology.totopogeom(geom, 'lines_topo', 1, 0)</div>
<div> WHERE id = id_line;</div>
<div> EXCEPTION</div>
<div> WHEN OTHERS THEN</div>
<div> RAISE WARNING 'Loading of record % failed: %', id_line, sqlerrm;</div>
<div> END;</div>
<div> END LOOP;</div>
<div> END</div>
$$;<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>PS-> This has been tested on versions:</div>
<div><br>
</div>
<div>MacOS Catalina<br>
</div>
<div>POSTGIS=3.0.2 2fb2a18 [EXTENSION] PGSQL=120" GEOS=3.8.1-CAPI-1.13.3 PROJ=6.2.1" LIBXML=2.9.10 LIBJSON=0.13.1" LIBPROTOBUF=1.3.2 WAGYU=0.4.3 (Internal) TOPOLOGY</div>
<div><br>
</div>
<div>Docker image - postgis/postgis:13-master<br>
</div>
<div>POSTGIS="3.1.0 3.1.0" [EXTENSION] PGSQL="130" GEOS="3.9.0-CAPI-1.14.1" PROJ="7.2.1" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)" TOPOLOGY<br>
</div>
<div><br>
</div>
<div>Cheers,</div>
<span>Diogo Marques</span> <img src="http://newsletter.impresapublishing.pt/i/barra_ip.jpg">
</body>
</html>