Cleaning geometries inside PostGIS

Its been an enlightening few days. Horst Duester (of SO!GIS fame) is paying me a visit for a two month sabbatical. Horst is here to provide valuable training and knowledge exchange to South Africans interested in FOSS GIS (more on that to come in future blog posts no doubt!). Its been really great having an opportunity to sit down with a fellow FOSSGIS geek and exchange notes – and today Horst showed me something really cool. Horst has written a PostGIS function to clean geometries within the database. Normally I use GRASS if I want to clean the topology of my vector dataset. With Horst’s approach this is no longer needed which is a great timesaver. Here’s what to do in a nutshell:

Assuming you already have a spatial database created (with lwpostgis, spatial_ref_sys etc. sql scripts run on it), download and install Horst’s clean geometry function. Here is on way to do that:

wget http://www.sogis1.so.ch/sogis/dl/postgis/cleanGeometry.sql
psql mydatabase < cleanGeometry.sql

Now lets create a test table and show you how to use it. I used Martin Dobias' excellent PostGIS Manager plugin (available in the python plugin repositories) to create a table as shown in the screenshot below (click for a larger version). As an aside, Martin's tool is a great way for new users to get started with building their own spatially enabled database.

Next I created a self-intersecting polygon (in this case a figure of eight-ish shape) as shown below:

The little green cross in the middle of the polygon shows the self-intersection. To clean the geometry, I then ran a little sql command that uses Horst's cleangeometry to fix all the geometries in the table like this:

update testpolys set the_geom=cleangeometry(the_geom);

The result is a multipart polygon with no self intersections but preserving the original shape and attributes. In the image below I've pulled the connecting vertices in the center apart so you can see that two sub-polygons were created: