Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
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 '11 at 14:04
    
You may need a ST_Multi() to force all polygons to be MULTIPOLYGONs (this is an assumption) –  Mike T Oct 3 '11 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 '11 at 16:11
add comment

1 Answer

up vote 6 down vote accepted

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.

HTH Nicklas

share|improve this answer
    
Thanks Nick, I did the first one last night just to see the effect and it did the trick. It is useful to see the new table option too, this might be better in future. –  ESRIHelp Oct 4 '11 at 16:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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