8

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". [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.

closed as off-topic by PolyGeo Jul 13 at 7:32

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "When seeking help to debug/write/improve code always provide the desired behavior, a specific problem/error and the shortest code (as formatted text, not pictures) needed to reproduce it in the question body. Providing a clear problem statement and a code attempt helps others to help you." – PolyGeo
If this question can be reworded to fit the rules in the help center, please edit the question.

  • "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 – simplexio 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 gis.stackexchange.com/questions/24365/… 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 – simplexio 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 3 '14 at 1:11
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.

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