POSTGIS workflow for cleaning up a noisy dataset

Question

I have a conservation dataset that im trying to simplify for the purpose of using it as a GML layer in openlayers, ie reducing the total shape and node count to quite small.

The data has issues: eg-

• about 15K orig polygons of which i only need the 500 largest post dissolve.
• overnoded, way too detailed
• lots of long thin slithers either inside or outside the main polygons which im not that interested in
• hundreds of invalid geometrys, rings with less than three points, self intersections etc

Using this as an exercise whats a good way to clean and simplify a dataset like this?

Ive been trying variations on this so far:

• st_simplifypreservetopology,10, to reduce time taken below
• hand fix invalids
• buffering +20m to swallow the internal slithers,
• st_union, trys ot make single feature, mem exhausts usually
• unbuffering 40m, to swallow external slithers.
• st_union,
• buffer +20 to return to right area.
• st_simplify,10
• st_dump
• bin any with area less than 1000ha

Its slow and simplify always creates yet more invalid shapes.

A subquestion is how to make postgis do the equivalent of qgis 'buffer and dissolve', ie merge shapes that adjoin or overlap, but leave other shapes as seperate features.

Answer 1

To delete invalid geometries:

DELETE FROM table WHERE NOT ST_isvalid(the_geom);