In PostGIS I have imported a shapefile - some of this is Invalid according to PostGIS.

Following online help I have first split the invalid polygons into a new table:

WHERE NOT ST_IsValid(the_geom);

WHERE NOT ST_IsValid(the_geom);

I then buffered the invalid polygons by 0 to sort out the issue:

UPDATE s_invalid
SET the_geom = ST_Buffer(the_geom,0);

SELECT count(*), ST_IsValid(the_geom)
FROM s_invalid

All seems ok, now I want to append the cleaned-up polygons back into the original table and this is where I have an issue. I have tried:

SELECT * FROM s_invalid;

But there seems to be a mismatch with the geometry type from the best I can see - I get the message:

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

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

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

How can I sort this... where is the issue? Is there an easier way to sort this out? Thanks, I really want to get to grips with this!

  • a) what geometry contraint do you have in sdata? b) what does select geometrytype(the_geom) from s_invalid; say? – user1702401 Jan 7 '13 at 12:11
  • OK, I tried: SELECT geometrytype(the_geom) from s_invalid GROUP BY geometrytype(the_geom); and I get MULITPOLYGON and POLYGON - is this the problem...? – gisuser Jan 7 '13 at 12:53
  • You may have POLYGON contraint on sdata.the_geom and therefore can't insert MULTIPOLYGONs. Check out, what is definition for enforce_geotype_the_geom. – user1702401 Jan 7 '13 at 13:07
  • Sorry how do I check that.... – gisuser Jan 7 '13 at 13:35
  • Do you have pgAdmin? Just look for your table in left pane ('Object browser'), and there's Constraints section. When you are using plain psql, try \d table - but I'm not sure, whether it describes also constraints. – user1702401 Jan 7 '13 at 13:44

You have MULTIPOLYGON constraint in sdata table, but ST_Buffer creates some POLYGON objects. Try:

UPDATE s_invalid
SET the_geom = ST_Multi(ST_Buffer(the_geom,0));

(or, alternatively, change constraint, so it accepts also POLYGONS).

  • OK finally there - THANK YOU BOTH - I think I get it now. – gisuser Jan 7 '13 at 14:09

from my answer here.

try to alter your table for dropping constraint:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

than add your constraint again after chanaging table name...

ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE products ADD CONSTRAINT the_constraint_name;

i hope it helps you...

  • I took the constraint off, then yes it would let me insert. The statement to add the constraint is throwing an error and when I tried to add it back through pgadmin it won't let me as it says one of the rows violates it..... – gisuser Jan 7 '13 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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