[postgis-devel] Postgis topology creation - O(n-squared)? - creates problems with large datasets.

[Sandro Santilli]

On Fri, Jan 10, 2014 at 04:29:42PM +0000, Graeme B. Bell wrote:

> 1 - I am not using a transaction.

You are. If you're not explicitly starting one you'll get
one for every query you run. 

> 2 - I am using the standard API constructor call to build a topology from a collection of geometry. If that is the wrong way to build a topology, then I don't know what the correct way is.

You mean the ST_CreateTopoGeo function ?

That function was the first implementation of a constructor
and I hadn't given it much love as I've always thought it would
have been inappropriate anyway, due to the constraints of doing
the building in a single transaction and while keeping the whole
input in memory during operations.

The function is pretty small, you can easily see how many
database-accessing function calls are being made, surely
more than needed. I suggest you not only build with POSTGIS_TOPOLOGY_DEBUG
defined, but also add more RAISE DEBUG lines to eye-spot which
step costs more. 

> > Q. Do postgis topology functions (including constructors) make use of a spatial index? Is there any way to put a spatial index on topology items? 
> >> 
> > They do, supposedly.
> 
> ... any idea where I can find out more, other than digging around in the source code? (e.g. design spec?)

You can install auto_explain module and configure it to show you
the plans for queries run from the plpgsql functions.

> > (huge improvement
> > made after that to the ST_BuildArea function -- wonder if our Graeme
> > had that in).
> 
> Could be. I'm not in a position to update our postgis at the moment but I will try to get from 2.1.0 to 2.1.1 asap.

Uhm, nope, ST_BuildArea speedup was in 2.0.1
I don't see performance improvements related to topology in 2.1.1.

Also make sure you're running latest GEOS version.

--strk;