Vjylku

How can I make my BBEG immortal short of making them a Lich or Vampire?

How to draw the figure with four pentagons?

Today is the Center

In a spin, are both wings stalled?

Why doesn't H₄O²⁺ exist?

What is a clear way to write a bar that has an extra beat?

What is the word for reserving something for yourself before others do?

What do you call someone who asks many questions?

Is there a hemisphere-neutral way of specifying a season?

What is the intuition behind short exact sequences of groups; in particular, what is the intuition behind group extensions?

Did Shadowfax go to Valinor?

Why is consensus so controversial in Britain?

How do conventional missiles fly?

A reference to a well-known characterization of scattered compact spaces

Is it canonical bit space?

Took a trip to a parallel universe, need help deciphering

Western buddy movie with a supernatural twist where a woman turns into an eagle at the end

Alternative to sending password over mail?

Will google still index a page if I use a $_SESSION variable?

What about the virus in 12 Monkeys?

What does it mean to describe someone as a butt steak?

What is the most common color to indicate the input-field is disabled?

How can saying a song's name be a copyright violation?

I would say: "You are another teacher", but she is a woman and I am a man

Per row commit when editing PostGIS data with QGIS, with PostGIS triggers checking inserted data

How to update attribute values of points outside an area?update tables data dynamicallyInsert geometric data to a column from coordinate values in double precisionidentify subgraphs within planet_osm_line tableIssues with QGIS WFS editing/PostGIS renderingAdd individual fields to delete-log-trigger when deleting PostGis-Objects via QGisHow to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons?QGIS crashes when using PostgreSQL/PostGIS triggersEditable views in QGIS - update affects the wrong rowLines sometimes disappear when editing Postgis layer in QGIS 2.16

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

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.

qgis postgis postgresql trigger

share|improve this question

asked 10 mins ago

MartinTMartinT

38919

add a comment | 

0

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.

qgis postgis postgresql trigger

share|improve this question

asked 10 mins ago

MartinTMartinT

38919

add a comment | 

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.

qgis postgis postgresql trigger

share|improve this question

asked 10 mins ago

MartinTMartinT

38919

-- 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;

share|improve this question

asked 10 mins ago

MartinTMartinT

38919

share|improve this question

asked 10 mins ago

MartinTMartinT

38919

asked 10 mins ago

MartinTMartinT

38919

asked 10 mins ago

MartinTMartinT

38919