[postgis-users] Help with an area and hole filter query?

[Simon Greener]

Regina,

> This looks interesting.  Actually hadn't thought of using ST_DumpRings, but
> I think that would be better than the ST_InteriorRingN

Certainly leads to simpler SQL.

> Couple of comments
> 1)  You should do this
>
> SELECT (ST_DumpRings(a.geom)).*
>
> Instead of this
> SELECT (ST_DumpRings(a.geom)).geom As the_geom, path(ST_DumpRings(a.geom))
> as path
>
> (Which would mean in the upper part you would need to reference by .geom
> instead of the_geom
>
> The reason for that is internally PostgreSQL will call ST_DumpRings twice.
> This was pointed out to me by a very experienced PostgreSQL developer.

Ahh, yes, of course. I wondered about this when I did the SQL. I guess the function is deterministic which means it must process its input twice because the query optimizer doesn't realise the function is being called with the same input. 

A good improvement.

> His blog entry about it is here
> http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/

I'll look at it.

> 2) I think ST_BuildArea might be better than ST_MakePolygon in this regard.
> It will work fine with a single closed ring and if multiple, it turns the
> inners to holes.

Ahh, didn't notice ST_BuildArea as a replacement for ST_MakePolygon. I like what you have done in the second email and concur that it produces the sort of concise SQL we are after.

> So what I was thinking in verbiage
>
>  ST_BuildArea(ST_Collect all exterior/interior excluding all interior rings
> where area < desired (that would exclude holes that are too small))

Will discuss your other email.

regards
Simon
-- 
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, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
  Email: simon at spatialdbadvisor.com
  Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3