[postgis-users] Postgis Topology and slow topology.TopoGeo_addLinestring on big datasets

[Sandro Santilli]

On Wed, Feb 26, 2020 at 09:59:52AM +0000, Lars Aksel Opsahl wrote:

>   *   The output from pg_stat_statements is below, The only strange thing I see here is that the query “SELECT edge_id,geom FROM "test_topo_ar5".edge_data WHERE edge_id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,” has length of 599090

It sounds like a big "edge ring", that is a face bound by many edges.
You should be able to speed things up by cutting that big face into
smaller faces. Since you already made a grid you may start by adding
edges of that grid in the topology.

Note that every time you add an edge with TopoGeo_addLinestring, the
code tries to determine if a new face is created by walking on that
"edge ring" (which is done using that query you mention). The Tuscany
Region had funded, for spatialite, code to prevent that step while
adding linestrings, for doing it at the very end, when all edges
are in place. Doing so somewhat speed things up, because at the time
in which faces are computed, all edges are in place and thus there
each "edge ring" is computed exactly once instead of per-every-edge
added.

The work was never ported back to PostGIS because it would require
defining a policy to leave a topology in invalid state (with faces
being formed but not marked as such) and I personally didn't find
the time to work on that. An initial work to implement this in
ST_CreateTopoGeo (which would not have the problem of leaving the
topology in an invalid state, given it all happens in a single
transaction) is here: https://git.osgeo.org/gitea/postgis/postgis/pulls/28

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   https://strk.kbt.io/services.html