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

[Lars Aksel Opsahl]

Hi


>From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Sandro Santilli <strk at kbt.io>

>Sent: Wednesday, February 26, 2020 11:18 AM

>To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>

>Subject: Re: [postgis-users] Postgis Topology and slow topology.TopoGeo_addLinestring on big datasets

>

>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.

>


Yes I can do that but then I have to remove this extra lines when done.


This should by able to do by checking if two faces that share edge belongs the same original polygon.


>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

>


If checkout this code "git fetch origin pull/28/head:batch-topo2" is any problem for me to compile this code and test it ?


Or do I have to do a lot changes in my own code to test it ?



Thanks a lot.


Lars
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200226/531c0042/attachment.html>