1

I created a trigger that checks if geometry is valid on insert and update.

        create or replace function check_geom() returns trigger as
$$
begin
--insert a polygon
IF st_isvalid(new.geom) = 'f'
THEN RAISE EXCEPTION 'erreur de topologie' ;
END IF ;
RETURN NULL ;
end;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_check_geom ON public.uniteobservation ;
CREATE TRIGGER trg_check_geom AFTER INSERT OR UPDATE on public.uniteobservation
FOR EACH ROW EXECUTE PROCEDURE check_geom();

I have an error message when I create a feature with a wrong geometry (self intersection,...). I can correct and validate it. But I can't update a second times : QGIS mini dump each time I select a vertex again. Any idea ?

4
  • have you tried to return new.* on success ? Commented Jul 3, 2015 at 12:52
  • you mean if st_isvalid(new.geom) = 't' ?
    – Leehan
    Commented Jul 3, 2015 at 13:02
  • ... maybe. I mean : IF st_isvalid(new.geom) = 'f' THEN RAISE EXCEPTION 'erreur de topologie' ; ELSE (INSERT data here and call return ) RETURN NEW END IF ; end; Commented Jul 3, 2015 at 13:07
  • I tried ELSE RETURN NEW ;END IF ;..Whatever I change mini dump always on update...even when erasing RETURN NULL
    – Leehan
    Commented Jul 3, 2015 at 13:30

1 Answer 1

4

Instead of a trigger, you can add a constraint based on st_isvalid(), like:

ALTER TABLE public.uniteobservation
  ADD CONSTRAINT enforce_valid_geom CHECK (st_isvalid(geom));

Other useful constraints, that you might consider to ensure the consistency of user editings, especially when several users are using QGIS to edit data are:

ALTER TABLE public.uniteobservation
ADD CONSTRAINT enforce_type_polygon CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL);

ALTER TABLE public.uniteobservation
CONSTRAINT enforce_dims CHECK (st_ndims(geom) = 2);

ALTER TABLE public.uniteobservation
CONSTRAINT enforce_srid CHECK (st_srid(geom) = 3857);
2
  • Why not...good idea. Let's work on it.
    – Leehan
    Commented Jul 6, 2015 at 7:32
  • I made my decision : I am keeping the constraint solution. It works and I can't debug my trigger.
    – Leehan
    Commented Jul 8, 2015 at 11:14

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.