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 ?

  • have you tried to return new.* on success ? – simplexio Jul 3 '15 at 12:52
  • you mean if st_isvalid(new.geom) = 't' ? – Leehan Jul 3 '15 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; – simplexio Jul 3 '15 at 13:07
  • I tried ELSE RETURN NEW ;END IF ;..Whatever I change mini dump always on update...even when erasing RETURN NULL – Leehan Jul 3 '15 at 13:30
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);
  • Why not...good idea. Let's work on it. – Leehan Jul 6 '15 at 7:32
  • I made my decision : I am keeping the constraint solution. It works and I can't debug my trigger. – Leehan Jul 8 '15 at 11:14

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.