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

[Simon Greener]

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

On Sat, 17 Mar 2012 09:16:58 +1100, Sandro Santilli <strk at keybit.net> wrote:

> On Fri, Mar 16, 2012 at 05:38:07PM -0400, Dan Bernier wrote:
>> Hello, and apologies in advance if this is a naive question - I'm
>> still very new to GIS.
>>
>> Does PostGIS provide a way to measure the "complexity" of a shape?
>>
>> I have some shapes that I suspect are slowing down queries - normally,
>> joining against them with ST_within takes < 1 second, but for some
>> rows, it takes from 3 to 25 seconds. The explain analyze for the
>> different queries looks the same. The complexity of the shape is the
>> only thing I can think of that might cause this, but I don't know of
>> any way to measure this, to guide my reasoning. I tried googling, but
>> don't quite know the right terms to search for.
>
> The complexity you can measure by counting number of vertices:
> SELECT gid, st_npoints(the_geom) from mytable;
>
>> Beyond general shape-complexity, are there any other properties of a
>> shape that might cause this kind of slow-down?
>
> Your queries make use of GEOS calls (ST_Within).
> Such call may apply an heuristic to catch robustness issues.
> If it does it will perform the operation again after reducing precision.
> Then if it fails, it'll do again with another approach.
> And so on.
>
> See if passing the "expensive" inputs trought ST_SnapToGrid helps.
>
> --strk;
>
>   ,------o-.
>   |   __/  |    Delivering high quality PostGIS 2.0 !
>   |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
>   `-o------'
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


-- 
Holder of "2011 Oracle Spatial Excellence Award for Education and Research."
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
   Email: simon at spatialdbadvisor.com
   Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3