[postgis-users] Slow ST_InteriorRingN and ST_intersection (using ST_ DumpRings makes intersection 230 times faster)

[Paul Ramsey]

On Wed, Feb 24, 2016 at 9:00 AM, Lars Aksel Opsahl <Lars.Opsahl at nibio.no> wrote:

> Then I rewrote ST_intersection to use ST_DumpRings and added a test on them and then it's more than 230 faster (from 933238.069 ms to 3912.089 ms)
>
> ... a SQL implementation that only tries to intersect rings that actually have an interaction with the geometry of interest ...
>
> Question 2 : Is it possible to do something like this in the standard postgis intersection ?
>
> (Or do I have do this in my own code ?)

This calculation is carried out in GEOS land. I'm willing to guess
that the performance problem is caused by the perhaps naive overlay
code in GEOS, that builds a topology for each input argument, then
merges those topologies. Since apparently one of your arguments only
interacts with a small % of the other argument, this ends up being
very inefficient for this particular case, building a complete
topology for the huge argument but only using a small portion of it in
the end. An overlay that was more selective, like the one you built
out, would perhaps be better, but maybe not as generally useful for
all overlay operations.

P.

>
>
> Lars
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users