<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7653.38">
<TITLE>RE: [postgis-users] Help with an area and hole filter query?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>What I was thinking of was this.<BR>
<BR>
CREATE OR REPLACE FUNCTION upgis_filter_rings(geometry,float) RETURNS geometry AS<BR>
$$ SELECT ST_BuildArea(ST_Collect(a.geom)) as final_geom<BR>
FROM ST_DumpRings($1) AS a<BR>
WHERE a.path[1] = 0 OR<BR>
(a.path[1] > 0 AND ST_Area(a.geom) > $2)<BR>
$$<BR>
LANGUAGE 'sql' IMMUTABLE;<BR>
<BR>
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.<BR>
<BR>
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.<BR>
<BR>
So better compare<BR>
<BR>
SELECT ST_AsText(upgis_filter_rings(ST_GeomFromText('POLYGON((10 10,10<BR>
20,20 20,20 10,10 10),(13 17, 13 14, 15 15.82, 13 17), (18 15, 18 14, 18 14, 18 15))')<BR>
,2));<BR>
<BR>
SELECT ST_AsText(filter_rings(ST_GeomFromText('POLYGON((10 10,10<BR>
20,20 20,20 10,10 10),(13 17, 13 14, 15 15.82, 13 17), (18 15, 18 14, 18 14, 18 15))')<BR>
,2));<BR>
<BR>
Which yield the same answer.<BR>
<BR>
<BR>
Hope that helps,<BR>
Regina<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Paragon Corporation<BR>
Sent: Thu 1/1/2009 6:36 AM<BR>
To: 'PostGIS Users Discussion'<BR>
Subject: RE: [postgis-users] Help with an area and hole filter query?<BR>
<BR>
Simon,<BR>
<BR>
This looks interesting. Actually hadn't thought of using ST_DumpRings, but<BR>
I think that would be better than the ST_InteriorRingN<BR>
<BR>
Couple of comments<BR>
1) You should do this<BR>
<BR>
SELECT (ST_DumpRings(a.geom)).*<BR>
<BR>
Instead of this<BR>
SELECT (ST_DumpRings(a.geom)).geom As the_geom, path(ST_DumpRings(a.geom))<BR>
as path<BR>
<BR>
(Which would mean in the upper part you would need to reference by .geom<BR>
instead of the_geom<BR>
<BR>
The reason for that is internally PostgreSQL will call ST_DumpRings twice.<BR>
This was pointed out to me by a very experienced PostgreSQL developer.<BR>
<BR>
His blog entry about it is here<BR>
<A HREF="http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-i">http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-i</A><BR>
n-selects/<BR>
<BR>
2) I think ST_BuildArea might be better than ST_MakePolygon in this regard.<BR>
It will work fine with a single closed ring and if multiple, it turns the<BR>
inners to holes.<BR>
<BR>
So what I was thinking in verbiage<BR>
<BR>
ST_BuildArea(ST_Collect all exterior/interior excluding all interior rings<BR>
where area < desired (that would exclude holes that are too small)) <BR>
<BR>
<BR>
Thanks,<BR>
Regina<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net<BR>
[<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Simon<BR>
Greener<BR>
Sent: Thursday, January 01, 2009 1:48 AM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Help with an area and hole filter query?<BR>
<BR>
Bob,<BR>
<BR>
> I have a polygon table that has many small areas and holes. Now, I<BR>
> would like to remove small areas and holes that are 2800 m^2.<BR>
><BR>
> Any help or advice would be really appreciated.<BR>
<BR>
I had a bash with a simple WKT Polygon with two inner holes.<BR>
<BR>
SELECT ST_AsText(ST_MakePolygon(c.outer_ring, d.inner_rings )) as final_geom<BR>
FROM (/* Get outer ring of polygon */<BR>
SELECT ST_ExteriorRing(b.the_geom) as outer_ring<BR>
FROM (SELECT (ST_DumpRings(a.geom)).geom As the_geom,<BR>
path(ST_DumpRings(a.geom)) as path<BR>
FROM (SELECT ST_PolyFromText('POLYGON((10 10,10 20,20<BR>
20,20 10,10 10),(0 0,0 1,1 1,1 0,0 0),(5 5,5 7,7 7,7 5,5 5))') as geom<BR>
) a<BR>
) b<BR>
WHERE path[1] = 0 /* ie the outer ring */<BR>
) c,<BR>
(/* Get all inner rings > a particular area */<BR>
SELECT ST_Accum(ST_ExteriorRing(b.the_geom)) as inner_rings<BR>
FROM (SELECT (ST_DumpRings(a.geom)).geom As the_geom,<BR>
path(ST_DumpRings(a.geom)) as path<BR>
FROM (SELECT ST_PolyFromText('POLYGON((10 10,10 20,20<BR>
20,20 10,10 10),(0 0,0 1,1 1,1 0,0 0),(5 5,5 7,7 7,7 5,5 5))') as geom<BR>
) a<BR>
) b<BR>
WHERE path[1] > 0 /* ie not the outer ring */<BR>
AND ST_Area(b.the_geom) > 1<BR>
) d;<BR>
<BR>
The splitting of the SQL into the two halfs to extract the outer ring and<BR>
the inner rings (separately) had to be done because the only method of<BR>
reconstructing the polygon was via the ST_MakePolygon function and it needs<BR>
two inputs: a linestring for the outer ring and an array of linestrings for<BR>
the inner rings left after being filtered by area. Sadly, ST_Collect() on<BR>
the straight output of ST_DumpRings (with no filtering by path only area)<BR>
just generates a multipolygon. I tried playing around with ST_Intersection<BR>
etc but these still return a multipolygon.<BR>
<BR>
I think it might be wise to wrap this SQL up in a function if you are going<BR>
to process a lot of polygons in a table.<BR>
<BR>
CREATE OR REPLACE FUNCTION Filter_Rings(geometry,float) RETURNS geometry AS<BR>
$$ SELECT ST_MakePolygon(c.outer_ring, d.inner_rings) as final_geom<BR>
FROM (/* Get outer ring of polygon */<BR>
SELECT ST_ExteriorRing(b.the_geom) as outer_ring<BR>
FROM (SELECT (ST_DumpRings($1)).geom As the_geom,<BR>
path(ST_DumpRings($1)) as path) b<BR>
WHERE b.path[1] = 0 /* ie the outer ring */<BR>
) c,<BR>
(/* Get all inner rings > a particular area */<BR>
SELECT ST_Accum(ST_ExteriorRing(b.the_geom)) as inner_rings<BR>
FROM (SELECT (ST_DumpRings($1)).geom As the_geom,<BR>
path(ST_DumpRings($1)) as path) b<BR>
WHERE b.path[1] > 0 /* ie not the outer ring */<BR>
AND ST_Area(b.the_geom) > $2<BR>
) d<BR>
$$<BR>
LANGUAGE 'sql' IMMUTABLE;<BR>
<BR>
Example: select ST_AsText(Filter_Rings(ST_PolyFromText('POLYGON((10 10,10<BR>
20,20 20,20 10,10 10),(0 0,0 1,1 1,1 0,0 0),(5 5,5 7,7 7,7 5,5 5))')<BR>
,1::float));<BR>
<BR>
"POLYGON((10 10,10 20,20 20,20 10,10 10),(5 5,5 7,7 7,7 5,5 5))"<BR>
<BR>
Anyway, I hope it gives you some idea as to the direction you could take...<BR>
<BR>
regards<BR>
SImon<BR>
--<BR>
SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle<BR>
Database 10g Administrator Certified Associate; Oracle Database 10g SQL<BR>
Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE,<BR>
Manifold GIS, Radius Topology and Studio Specialist.<BR>
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.<BR>
Website: www.spatialdbadvisor.com<BR>
Email: simon@spatialdbadvisor.com<BR>
Voice: +613 9016 3910<BR>
Mobile: +61 418 396391<BR>
Skype: sggreener<BR>
Longitude: 147.20515 (147° 12' 18" E)<BR>
Latitude: -43.01530 (43° 00' 55" S)<BR>
NAC:W80CK 7SWP3<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>