Ring Self-Intersections in PostGIS

Question

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 1

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.

Answer 2

I had the same problems a few months ago. I had polygons in my database with a lot of self-intersections. I used the method of Niklas Aven, but it did not work. I also used other methods.

My method is to remove the self intersection from my polygons. Here is the SQL query i used:

     -- Create the new polygons 

SELECT ST_GeomFromText(polygone) as geom

   from (

    SELECT st_astext(ST_MakePolygon(linear)) as polygone

        from (
              SELECT st_astext
                ((ST_dump
                   (st_boundary
                      (ST_MakeValid
                     (ST_Transform
                        (ST_SETSRID
                           (geometry,4326),32631))))).geom) 
                          as linear from tables_name)
        as query2)

  as query1

where ST_area(polygone) > 1

I calculated the area of ​​the polygon and the self-intersection and I will only keep polygons that have an area > 1. 1 is the tolerance because the area of the self-intersection is very small. Also note that the transformation of the SRID to meters is important if you have data in degrees.