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.

  • 1
    what does "select geometry_type(the_geom) from sgm_buffer limit 1" return? Also, related to the runtime of 40 hours mentioned in the other question, are there spatial indexes defined on your table?
    – diciu
    Oct 3, 2011 at 14:04
  • You may need a ST_Multi() to force all polygons to be MULTIPOLYGONs (this is an assumption)
    – Mike T
    Oct 3, 2011 at 19:03
  • Thanks for comments - diciu I think there are indexes, they seem to be visible in pgAdminIII, but perhaps I need to refresh them? I could do with finding out solutions to speed up the query - I don't know what would be a reasonable time for it to run? Landcover has approx 20million features.
    – ESRIHelp
    Oct 4, 2011 at 16:11

2 Answers 2


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.

  • Hi and thanks for your reply! i am using QGIS, where should I put this code?
    – Robagb
    Feb 7, 2022 at 12:05

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
                          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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.