2

I have a PostGIS trigger assuring that records with a Point-geometry inserted in a table fall within Polygons in another table.

Simple example:

-- Trigger function
CREATE OR REPLACE FUNCTION check_pointWithinPolygon()
RETURNS trigger AS $body$
BEGIN
  IF TG_OP = 'INSERT'  
  THEN
    IF ((SELECT count(*) FROM (SELECT poly.id FROM poly WHERE st_Within(NEW.geom, poly.geom)) as foo) > 0) THEN
        RETURN NEW; 
    ELSE
        RAISE EXCEPTION 'inserted feature ID:% is not within a poly', NEW.id;
    END IF;
  END IF;
END;
$body$ LANGUAGE plpgsql;
COMMIT;


-- apply the trigger function in a trigger
CREATE TRIGGER myPointInPoly BEFORE INSERT OR UPDATE
ON points FOR EACH ROW
EXECUTE PROCEDURE check_pointWithinPolygon();
COMMIT;

All this works well. I am using QGIS to edit the layer. Some point fall within polygons ( ok) some outside ( and are caught by the trigger). The problem comes when QGIS "save edits" committs the changes.

Currently, all edits are discarded if even a single record is not accepted. I would like to commit per row, so that those that are correct, are inserted. I am not sure whether this is something that can be caught in the trigger logic ( likely), or also needs reflection in the QGIS commit code.

  • 1
    just don't raise an EXCEPTION, but a NOTICE maybe, and RETURN NULL in that case. I'd also use IF EXISTS (SELECT 1 FROM poly WHERE ST_Within(NEW.geom, poly.geom) THEN ... as condition; should be more performant if bool is all you want to check for. – ThingumaBob Apr 5 at 10:34
  • This is a great idea, will test and report. If you add this as an answer, I will accept this. – MartinT Apr 7 at 11:50

Your Answer

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

Browse other questions tagged or ask your own question.