10

How do I write SQL (for PostGIS in PostgreSQL) to remove sliver polygons (including long narrow slivers and some small irregular polygons that were erroneous 'gaps' in the original dataset) from my dataset by merging with an adjacent polygon with the longest edge?

I have a dataset (tablename: "Vegetation" containing several thousand polygons identified as either a sliver or a 'real' polygon in a description column (column name: "desc") as "sliver" or "main". The polygons are not overlapping other polygons and are larger than the tolerance that I can accept on the coordinates.

[edit: Prior to importing my dataset into PostGIS I cleaned it up by importing into GRASS (which is also how I converted all the gaps into small polygons, which I subsequently merged with the main dataset. I found if I increased the snapping tolerance and minimum area past '1' in the GRASS import, I started loosing desired features.]

My understanding is that I may need the following steps:

  1. Identify the polygon (Vegetation.desc="main") adjacent to each sliver (Vegetation.desc="sliver") sharing the longest edge.
  2. Identify the edge between each sliver and it's adjacent main polygon sharing the longest edge identified in Step 1. (Possibly using ST_Relate)
  3. Remove the edge between each sliver and it's adjacent main polygon sharing the longest edge using the edge identifier returned in Step 2. (Possibly using ST_RemEdgeModFace)

I have only a very basic understanding of SQL and the above process is beyond my ability.

Rem : I know that this is possible in ArcGIS or GRASS with a single function ("eliminate" or "v.clean rmarea"), but I want a solution for PostGIS.

radouxju
  • 49,636
  • 2
  • 71
  • 144
DavidM
  • 109
  • 4
  • "postgis topology" extension , if i have understood right just creating topology with bigger tolerance than your avarage sliver will clean geometries. but i havenät done anything like that – simpleuser001 Jan 28 '14 at 11:46
  • I believe I would loose to much resolution by creating a topology with a big enough tolerance due to the size of some of the 'slivers' (see clarification in text). This may be due to my limited understanding, and simpler solutions are greatly welcomed! – DavidM Jan 28 '14 at 22:20
  • welcome to gis and data cleaning. Do you have access to FME ? that one has quite easy to use and has some option to remove slivers from polygons. Alternatives to http://gis.stackexchange.com/questions/24365/is-there-any-safe-fme-alternatives and google seems to find pl/pgsql answers to your question (no idea how good they are). That said , there is no super easy way to do it that i know, cleaning it by hand is "easiest" way to do it but i can take looong time – simpleuser001 Jan 29 '14 at 10:10
  • The silver stuff leave it holes if you create a union or will be there intersecten artefacts or both kind of "rounding errors"? – huckfinn Jan 30 '14 at 20:39
  • Thanks for your comments. I do not have access to FME. – DavidM Feb 03 '14 at 01:11
  • 1
    I have temporarily resolved my problem (for the current project) by using the ARCGIS trial and applying the eliminate function. This worked well. I would still be keen to work out a SQL solution using postgis. – DavidM Feb 03 '14 at 01:13

1 Answers1

3

I use this when aggregating the shapes from the GADM2 dataset in SQL Server:

SET @g2 = @g1.STBuffer(1).Reduce(1).STBuffer(-1).Reduce(1)

The reduce() clears out the extended artefacts and speeds things up by a factor of 100x - a bit rough, but good for approximations.

Jay Cummins
  • 14,642
  • 7
  • 66
  • 141
Daniel
  • 31
  • 2