Subscribe to this thread
Home - General / All posts - Eliminating small holes revisited v.2 for M9
danb


1,859 post(s)
#23-Dec-21 22:43

I have been using Tim's code for efficient hole removal in the following thread:

Eliminating small holes revisited v.2 (georeference.org)

and wondering how to recode this efficiently for M9. I have had a few runs at it so far but to no avail. I am guessing that the key is to break it into multiple queries, but any pointers would be much appreciated as it is a very useful bit of code.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

adamw


9,821 post(s)
online
#24-Dec-21 14:01

As far as I can see, the idea of the query is, for each area:

  • split the area into branches, then union all branches (some of which will correspond to outer and some to inner contours) back together, producing the area with all holes filled,
  • subtract the original area from the filled area, producing just the holes,
  • split the holes into their own branches, filter out branches that are too large or too small (whichever you want to remove), then union what remains back, producing a subset of the holes,
  • subtract the subset of the holes from the filled area, producing a new geom with the holes that have been filtered out in the previous step filled.

Here's my take on it, using the example MAP file from the thread you are referencing:

--SQL9

 

DELETE FROM [holey object 2 table];

INSERT INTO [holey object 2 table] ([ID][Geom (I)]-- just two fields

SELECT [ID][Geom (I)] FROM [holey object table];

 

FUNCTION AreaSubtract(@g GEOM, @holes GEOMGEOM AS (

  GeomClip(@g, @holes, FALSE, 0)

END;

FUNCTION AreaBranchesBigger(@g GEOM, @t FLOAT64GEOM AS (

  SELECT GeomMergeAreas([value]FROM CALL GeomToBranches(@g)

  WHERE GeomArea([value], 0) > @t

END;

FUNCTION AreaFillHoles(@g GEOMGEOM AS (

  SELECT GeomUnionAreas([value]FROM CALL GeomToBranches(@g)

END;

FUNCTION AreaFillHolesSmallerPrep(@gfill GEOM, @g GEOM, @t FLOAT64GEOM AS (

  AreaSubtract(@gfill, AreaBranchesBigger(AreaSubtract(@gfill, @g), @t))

END;

FUNCTION AreaFillHolesSmaller(@g GEOM, @t FLOAT64GEOM AS (

  AreaFillHolesSmallerPrep(AreaFillHoles(@g), @g, @t) -- fill holes just once

END;

 

UPDATE [holey object 2 table]

   SET [Geom (I)] = AreaFillHolesSmaller([Geom (I)], 10000);

Here's what is going on:

The first DELETE / INSERT just make a copy of 'Holey object table' in 'Holey object 2 table' so we can do as many test runs as we want without harming original data.

Then we have a bunch of functions:

  • AreaSubtract is just a simplification of GeomClip which allows you to subtract one area from the other without specifying any additional parameters,
  • AreaFillHoles is a function that fills all holes in an area -- it takes an area, splits it into branches, then unions all branches back, filling all holes,
  • AreaBranchesBigger is a helper function that I use to filter the holes -- it takes an area representing the holes, splits it into branches, then merges the branches that are bigger than the specified threshold - I am using GeomMergeAreas instead of GeomUnionAreas here because we know that this function is called on a geom whose branches are disjoint, each branch is a separate shape,
  • AreaFillHolesSmaller / AreaFillHolesSmallerPrep are functions that fill holes smaller than the specified threshold in an area -- the code takes an area, fills all holes, computes just the holes by subtracting the original area from the filled area, passes the result to AreaBranchesBigger to remove holes that are too small, then reconstructs the area by subtracting remaining holes from the filled area, I am using two functions with one of them calling the other so that the filled area only has to be computed once despite being used for two separate purposes.

The final UPDATE takes 'Holey object 2 table' and fills holes smaller than 10,000 (I ran a SELECT that computed the area of each branch to check what value will fill some of the holes, but not others).

Hope this helps.

lionel

873 post(s)
#24-Dec-21 20:24

what mean function( <geom>,<tolerance>...) : A tolerance of zero means automatic tolerance.

Attachments:
holes_SQL.png


union, doc , APIand most important deepl & keyboard shortcut

danb


1,859 post(s)
#11-Jan-22 20:18

Back on deck now. Thanks so much for this Adam, it will serve as an excellent working example and highlights to me that I am still that I am still thinking about SQL in a very M8(centric) way. I believe however that as an example, this will go a long way to rectifying this.

Though very useful, I was actually using this and another query in a slightly different way. Not to fill small holes, but instead to remove polygons below a size threshold from vectorised rasters by amalgamating with their largest common neighbour.

In thinking about this a bit more and knowing how computationally difficult it might get on larger datasets, I realised that an optimised built-in transform might be more appropriate. To this end, I have sent a suggestion to sales requesting an equivalent of GDAL/R’s ‘Sieve’ function for rasters:

gdal_alg.h: GDAL Algorithms C API — GDAL documentation

https://gdal.org/programs/gdal_sieve.html

And ESRI’s ‘Eliminate’ function for vectors.

Eliminate (Data Management)—ArcGIS Pro | Documentation

Both would be very useful, and ‘Sieve’ would certainly open up production of LiDAR derivatives which are more suited for pragmatic paddock scale policy setting and guidance using our newly acquired LiDAR datasets.

Thanks for this excellent example however which I will refer to when approaching this sort of task and others in the future.

PS and to Lionel for his really useful diagram showing the flow of the query.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

lionel

873 post(s)
#24-Dec-21 19:47

really like the edit mode " Atl click" of manifold 9 that show us where the holes are .

with the new SQL engine version and functions, the number of lines to obtain the same result is greatly reduced !!

Thank's for the code adam and also thank's to all Manifold users with SQL and C# post in the forum .. I learn a lot ...

Before and after remove the 164 Holes ?

Attachments:
holes_editmode.png
hole_before_after.png


union, doc , APIand most important deepl & keyboard shortcut

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.