[postgis-users] Topology creation performance

[Sandro Santilli]

On Fri, Nov 20, 2020 at 04:55:06PM +0000, Alexandre Silva wrote:

> The slower ordering method results in the whole area being added to the topology in a layered style (rivers, roads, rural areas, etc.) and after the first one, there are already some faces with a large area, and the performance starts dropping rapidly. My suspicion is that this faces are the culprit of this slowing down.
> In a first attempt to fix it I tried deleting the faces after each line was added, and it improved a little at the start but by the second half it's not much of a difference.

Most likely. I would try splitting these big faces into smaller
pieces. A way to do so would be inserting abitrary lines cutting
the plane into a grid. This would also split lines, further
reducing working set for each further insertion. You could add
these lines upfront or during the load, to see how they affect
the loading (it's good that you use multiple transactions).

What version of PostGIS are you using ? What GEOS version ?

>  In another attempt, I used the AddEdge method, and it processed all the lines in about 15 minutes.

Nice timing!

> Even though this needs the polygonize method to be run afterwards, from what I could discover it seems that every edge is only processed once, instead of multiple times. (In a older post (https://postgis-users.postgis.refractions.narkive.com/Xg3wV8V2/postgis-topology-performance) this approach seems to be the way to go). The major disadvantage of this method is that every line needs to be split beforehand, so the AddEdge doesn't throw an error, but using any other of the existing methods (toTopogeom and TopoGeo_AddLineString) it doesn't seems to be a way to get the performance that I get with AddEdge.
> 
> Are my assumptions are correct? And is the AddEdge the way to go or is there another way?

Note that the Polygonize function will NOT properly setup edge-linking
(next_right_edge, next_left_edge) so you'd still not end up with a valid
topology when only using AddEdge + Polygonize.

One improvement that was implemented in spatialite was to allow for
TopoGeo_addLinestring to NOT detect the creation of new faces while
still doing edge-linking. It still implied constructing an invalid
topology but the Polgonize step would then make it valid.

--strk;