topology - Ring Self-Intersections in PostGIS

This is a follow on from previous question. You will see I am a new PostGIS user.

I have looked at the validity of the topology in my data and find around 1700 topology issues in the landcover (polygon) dataset. These are all "Ring Self_intersection".

Using some useful information in this presentation by Paul Ramsay(page 20), I have attempted to buffer my data accordingly:

UPDATE schema.data SET the_geom=buffer(the_geom,0.0) WHERE isvalid(the_geom)=false and isvalid(buffer(the_geom, 0.0))=true;

But I get the response:

NOTICE: Ring Self-intersection at or near point (edited) ERROR: new row for relation "sgm_buffer" violates check constraint "enforce_geotype_the_geom"

********** Error **********

ERROR: new row for relation "sgm_buffer" violates check constraint "enforce_geotype_the_geom" SQL state: 23514

I have also tried just:

UPDATE csgn.sgm_buffer SET the_geom=buffer(the_geom,0.0)

and get:

ERROR: new row for relation "sgm_buffer" violates check constraint "enforce_geotype_the_geom"

********** Error **********

ERROR: new row for relation "sgm_buffer" violates check constraint "enforce_geotype_the_geom" SQL state: 23514

So do I assume that this buffer technique will not work on my issues? Or am I doing something wrong?

Mr Ramsay then goes on in the same document to suggest an additional technique to deal with "figure of 8" polygons. I don't entirely understand how to write this out in full in code (or what it is doing and whether it will help me):

SELECT ST_BuildArea( ST_Union( ‘LINESTRING EMPTY’, ST_ExteriorRing( ‘POLYGON((...))’

So.... can anyone help? This is proving a real nightmare with my tight timescales and limited skills in PostGIS.

Answer

I think what is happening is that your self-intersecting polygons becomes MULTIPOLYGONS when buffering.

you have two options:

1 remove the constraint "enforce_geotype_the_geom", you can do that in pgAdmin
2 put the result in a new table instead of updating the old. that is often a good way of doing things because then you don't change anything in your original table. the query can look something like:

CREATE TABLE new_buffered_table as
SELECT ST_Buffer(the_geom,0.0) as the_geom, gid FROM original_table;

of course you might want to bring more fields to your new table.

try the buffer trick first. In the second approach Paul can tell himself what affect the empty linestring has. I don't remember how that magic happens.