[postgis-users] Question on topology

[Sandro Santilli]

On Mon, Jul 11, 2016 at 04:52:40PM +0200, Neumann, Andreas wrote:

> The ST_CreateTopoGeo() function runs for a very long time. >45 minutes
> on this relatively small dataset. Did I use the wrong combination of
> functions at the top, or is it a problem that after ST_CurveToLine() my
> data contains a lot of vertices? 
> 
> My total dataset has 75'000 polygons, so I fear that, unless I did a
> erroneous combination of the functions, that the ST_CreateTopoGeo() may
> be too slow for my purpose? How big where the datasets that you used so
> far with Postgis topology? I hope it is a mistake on my side. 

Topology loading is known to be slow, and there's been work toward drafting
ideas to speed it up. What could help is very data-specific, but some things
are common to all datasets, like keeping the number of vertices below a given
threshold (ST_Subdivide can help). None of those heuristics are currently
implemented in ST_CreateTopoGeo, but you could find strategies yourself
using the TopoGeo_addLinestring function, passing it each split portion of
each polygon boundary in desceding geometry order (which helps with getting
polygon holes be included before polgon shells).

There are some load times on the rttopo wiki (but it's in italian):
https://git.osgeo.org/gogs/rttopo/librttopo/wiki/ItProfilazioneUcsRt

According to those numbers:

  - a Xeon E31245 (3,30Ghz) with 24GB Ram
    could load 840'143 polygons with a total of 47'254'882 vertices
    in about 3 days (250'518 seconds) using PostGIS master.

  - an i7-4750HQ (2.00Ghz) with 16GB Ram
    could load 16,757 polygons with a total of 794,020 vertices in
    about 1 hour and 13 minutes.

Depending on the distribution of your polygons (how many bounding box
overlaps) your number may be better or worst.

I recommend doing the loading in separate transaction chunks, so that
you can look at the topology as its being built and more importantly you don't
end up loosing all the work shall an exception arise.

--strk;