2

I have a PostGIS database which has a wonderful topology rule (ST_overlap), implemented following "dbaston"s suggestions in this post: Enforce PostGIS constraint that geometries do not overlap .

(I did not post enough on Stack Exchange to add comments directly under the post)

It took me quite a while to ensure the topology in QGIS with lots of manual editing and vertices movement. But eventually, I could add all polygons without an error message regarding overlaps. I saved the PostGIS edits, and everything works fine.

However, if I now try to edit some attributes in my database in the QGIS edit mode, a common exercise that will happen in the future, I get this error message after trying to save the change of attribute:

  PostGIS error while changing attributes: ERROR:  new row for relation "MyTable" violates check constraint "myOverlapConstraint"

How can this be? Or is PostGIS assuming a new temporal polygon that conflicts with the existing one when I edit attributes?

Weird, as all polygons were saved with the same constraint before and did not produce an error, as said above.

Additional Info: I have tried to edit field values in PGAdmin resulting in the same error message. But if I run:

SELECT *
FROM YourProject.YourTable a
INNER JOIN YourProject.YourTable b ON 
   (a.geom && b.geom AND ST_Relate(a.geom, b.geom, '2********'))
WHERE a.ctid != b.ctid
LIMIT 1

I get 0 rows are affected.

Improve this question
2
  • 1
    What attributes are you changing? If you change the id, your trigger will assume it overlaps with the old record because the id is now different, and the trigger function assumes the id will not change.
    – jbalk
    Commented Jul 31, 2023 at 18:50
  • Thanks for your answer! No, I tried to edit general attributes. However, I have some some generated columns: area: round(((st_area((geom)::geometry) / (10000)::double precision))::numeric, 4) ID: nextval('YourProject.YourTable_fid_seq"'::regclass) could they be the problem?
    – KoGIS
    Commented Aug 1, 2023 at 6:57

1 Answer 1

Reset to default
1

Ok, quite embarrassing. I was experiencing the exact same problem discussed in the thread I was referencing above! I did not implement the ID check in the constraint correctly which exactly caused what we assumed.

If everyone else experiences the problem above: Check carefully dbastons edits of his own post ( WHERE id != id )

Improve this answer
2
  • I'm glad you figured it out. You should click the big check mark next to your answer to accept it so this question can be marked as answered.
    – jbalk
    Commented Aug 1, 2023 at 17:11
  • @jbalk thanks a lot, I had to wait and then forgot
    – KoGIS
    Commented Aug 3, 2023 at 7:15

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.