7

I have a layer that has holes in it and I would like to fill only the holes that are smaller than 1/10th of an acre

enter image description here

I know how to fill all the holes with this code

SELECT id, ST_Collect(ST_MakePolygon(geom)) As geom
FROM (
    SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
    FROM layer
    ) s
GROUP BY id

but how I was not able to figure out the acreage of the holes.

should I be utilizing https://postgis.net/docs/ST_DumpRings.html ?

not sure how to think this process through.

I was thinking use the above query to fill in all the holes than run an st_difference from the swiss cheese hole layer to the filled holed layer and the output would be those holes as polygons where I can calculate the acreage..but then what merge them back to the swiss cheese hole layer?

is there a simpler approach?


I ended up running this query

select geom,st_area(geom)/43560 acres from(
    select (st_dumprings((st_dump(shape)).geom)).geom geom 
    from agriculture.agricultural_grtr_250) t
where st_area(geom)/43560 > .1

but it did not yield the results I wanted. I wanted to keep the holes greater than .1 but it still filled them.

I guess I want the SQL code that will emulate the ArcGIS tool eliminate polygon part

7

I think you were getting close. This should do it:

WITH rings AS (
  SELECT id, (ST_DumpRings((st_dump(shape)).geom)).geom
    FROM agriculture.agricultural_grtr_250
)
SELECT id,ST_BuildArea(ST_Collect(geom)) geom
from rings
WHERE ST_Area(geom)/43560 > .1
GROUP BY id;

Keep in mind that having the correct id (should be unique per geom) is important because this is what holds the rings together in the end.

  • cannot run because input is a multipolygon. table must be a polygon that is why I had st_dump initially – ziggy Aug 14 '17 at 16:06
  • got it to work, just replace (ST_DumpRings(shape)).geom with (ST_DumpRings((st_dump(shape)).geom)).geom in the cte and I will accept the answer – ziggy Aug 14 '17 at 16:07
  • 2
    so the key to this query is using st_buildarea() because of this "This function will assume all inner geometries represent holes" – ziggy Aug 14 '17 at 17:39
  • Hi, I am using st_DumpRings and I keep getting the following error: Input is not a polygon - even after using ST_MakeValid on the layer - any ideas why this is? – user1655130 Oct 21 '18 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.