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

[Simon Greener]

Regina,

> What I was thinking of was this.
>
> CREATE OR REPLACE FUNCTION upgis_filter_rings(geometry,float) RETURNS geometry AS
> $$ SELECT ST_BuildArea(ST_Collect(a.geom)) as final_geom
> 	FROM ST_DumpRings($1) AS a
>           WHERE a.path[1] = 0 OR
> 		(a.path[1] > 0 AND ST_Area(a.geom) > $2)
> $$
>   LANGUAGE 'sql' IMMUTABLE;

Oh, yes, very nice and concise.

> The main disadvantage aside from possibly speed over Simon's is that if you have 3-d polygons, I think the above will squash them to 2d where as his approach will support 3D.

That's an advantage I didn't realise about. (My experience with PostGIS functions is fairly beginner and there are a lot of functions to master.)

> On closer inspection -- when applying the above to the example polygon Simon provided the above returns a multipolygon and filter_rings returns a POLYGON and that is because if you  do a validity check of the polygon, its invalid because the holes lie outside of the exterior ring.  Buildarea just assumes everything outside is a polygon and everything inside is hole where as ST_MakePolygon takes your categorization of hole/vs shell as gospel.

A nice explanation.

> So better compare
>
> SELECT ST_AsText(upgis_filter_rings(ST_GeomFromText('POLYGON((10 10,10
> 20,20 20,20 10,10 10),(13 17, 13 14, 15 15.82, 13 17), (18 15, 18 14, 18 14, 18 15))')
> ,2));
>
> SELECT ST_AsText(filter_rings(ST_GeomFromText('POLYGON((10 10,10
> 20,20 20,20 10,10 10),(13 17, 13 14, 15 15.82, 13 17), (18 15, 18 14, 18 14, 18 15))')
> ,2));
>
> Which yield the same answer.

Yes, they do, I have tried them.

(I'll add this additional, excellent, work by Regina - if you don't mind - to my simple blog article on this filter_rings.)

> Hope that helps,

Well, I found it excellent!

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