The feature in PostGIS 2.0 that excited me most was not topology support, raster support, or 3D functions. Ok, raster was near the top of my list. But what I was really excited by was the ST_MakeValid function. Sad, isn’t it? Lack of vision probably– excited to try to solve recurring technical snafus in a computationally inexpensive way, rather than being more excited by the shiny new toys the PostGIS team has brought us for applying to new problems.
Never shy to try the impossible before trying the practical, I threw a really nasty 4 million acre, 3 meter vectorized landscape position dataset a colleague of mine put together. It’s a nice dendritic, complicated mess, with polygon validity issues:
Always one for doing tests, I compared the speed with which ST_MakeValid fixed the polygons vs. Horst Duester’s cleanGeometry pgsql function, which I posted about earlier. Sadly ST_MakeValid was much slower, and completed with error. On the other hand, the cleanGeometry function dropped polygons, although I haven’t had time to look into why. I don’t like my functions dropping data… .
Having posted to the PostGIS forum, Martin Davis poked a bit at the data and concluded that my problem was “self-touching polygons”, and suggested I try buffering the data a zero distance. Ahh– it makes sense that this is the issue– it’s polygonized raster data in shapefile form. Self-touching polygons are valid in the ESRI world but not in the simple features world (see Paul Ramsey’s presentation on PostGIS for Power Users from Foss4G 2011 for more on this).
So, ST_Buffer(the_geom, 0), and we’re good to go in half the time of the cleanGeometry function, without losing data.
Stay tuned for an ST_MakeValid wrapper which will do this trick for self-touching and self-intersecting polygons before trying to fix with ST_MakeValid. Code below.
psql -U postgres -d test -f "C:\Program Files\PostgreSQL\9.1\share\contrib\postgis-2.0\legacy.sql" CREATE TABLE tpi_clean AS SELECT gid, id, gridcode, "class name", cleanGeometry(geom) AS the_geom FROM tpi;
CREATE TABLE tpi_valid AS SELECT gid, id, gridcode, "class name", ST_MakeValid(geom) AS the_geom FROM tpi;
CREATE TABLE facepalm AS SELECT gid, id, gridcode, "class name", ST_Buffer(geom, 0) AS the_geom FROM tpi;