[postgis-users] Over-complicated shapes slowing down my queries?

Dan Bernier danbernier at gmail.com
Tue Mar 20 19:11:15 PDT 2012

On Tue, Mar 20, 2012 at 6:51 PM, Simon Greener
<simon at spatialdbadvisor.com> wrote:
> I looked at this in Oracle many years ago.
> I would have thought number of vertices is an unreliable measure of
> complexity - how
> do we know that any one straight line is not described by a lot more than a
> start and
> end node?

True, but if a line, which could be described by only 2 points, is
described by 100s, doesn't the algorithm still take longer? Isn't
removing the redundancies part of what simplification does?

I tried ST_Simplify, and using ST_npoints (thanks Sandro!), I was able
to get some measure of how much simpler the shapes were - or at least,
how many fewer data points the algorithm had to consider - as I
gradually simplified them more and more. I found that using ST_within
on multi-polygons with >11k points is pretty slow, and our
multi-polygons with the most points (~35k) were VERY slow. We dropped
the slowest ones down to around 2000 points, and they're much faster.
I'm still concerned about accuracy, though.

> If one can remove or reduce such vertex complexity then triangulating the
> polygon and
> counting the triangles may be a better measure. Perhaps looks at
> ST_SimplifyPreserveTopology()
> with very small tolerances first.

Thanks for the suggestion, I'll check it out. I'm not familiar enough
with the terms to understand how it's different from ST_Simplify, but
I'm still learning.

Thank you both for your help!

More information about the postgis-users mailing list