1

I have a question regarding creating a trigger function in postgres with two conditions:

When creating a linestring in QGIS there must be either overlap nor intersect. So I wrote two functions, each working with either ST_Overlaps or ST_Intersects.

CREATE OR REPLACE FUNCTION check_linesintersecting() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN 
IF TG_OP = 'INSERT'
THEN
IF
(SELECT COUNT(*)
FROM
(SELECT gid
FROM line_layer AS t
WHERE st_intersects(NEW.geom, t.geom)) AS foo)
THEN
RAISE EXCEPTION 'Speicherung abgebrochen: Ueberschneidende Linien!';
RETURN NULL;
ELSE
RETURN NEW;
END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(SELECT COUNT(*)
FROM
(SELECT gid
FROM line_layer AS t
WHERE st_intersects(NEW.geom, t.geom)
AND (t.gid <> OLD.gid)) AS foo) > 0
THEN
RAISE EXCEPTION 'Speicherung abgebrochen: Ueberschneidende Linien!';
RETURN NULL;
ELSE RETURN NEW;
END IF;
END IF;

END;
$BODY$
LANGUAGE plpgsql;

All I had to to in order to make it detect overlapping was to change the ST_Intersects into ST_Overlaps. But how can I combine ST_Overlaps and ST_Intersects?

I've tried using the same loop right after each other but only the the first function then is working. Any clues?

  • Welcome to GIS SE. Some indentation would improve readability of the code. – Martin F Jan 30 '15 at 4:57
  • either overlap nor intersect do you mean either/or OR neither/nor? – nmtoken Dec 15 '17 at 9:26
1

what if you use a nested if-else clause?

if they are intersecting, are they also overlapping?

if --intersection

    if --both
     then
    else --only intersect
    then

    end if
end if

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.