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.

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:

CREATE TABLE s_invalid AS
SELECT * FROM sdata
WHERE NOT ST_IsValid(the_geom);

DELETE FROM sdata
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
GROUP BY ST_IsValid;

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:

INSERT INTO sdata
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!

share|improve this question
    
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
show 1 more comment

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
OK finally there - THANK YOU BOTH - I think I get it now. –  gisuser Jan 7 '13 at 14:09
add comment

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

share|improve this answer
    
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
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.