1

I wan't to update a column in PostgreSQL that counts the number of points inside a polygon using a trigger function. The function should be fired on each insert or update of the polygons or the points layer.

CREATE OR REPLACE FUNCTION public.upd_points_in_polygons()
  RETURNS trigger AS
$BODY$ 
BEGIN
SELECT count(p.gid) INTO NEW.num_points FROM polygons po, points p WHERE st_intersects(NEW.the_geom, p.the_geom);
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Here the trigger from the table polygons.

CREATE TRIGGER t_upd_points_in_polygons
  AFTER INSERT OR UPDATE
  ON public.polygons
  FOR EACH ROW
  EXECUTE PROCEDURE public.upd_points_in_polygons();

The column num_points isn't updated, but I don't see any errors?

2

The trigger is set to be AFTER insert, so it is executed once the row has been inserted in the DB and it is too late to modify it. The doc says

The return value of a row-level trigger fired AFTER [...] is always ignored; it might as well be null.

To be able to alter the NEW record, you will want to change the trigger to a BEFORE trigger

CREATE TRIGGER t_upd_points_in_polygons
  BEFORE INSERT OR UPDATE
  ON public.polygons
  FOR EACH ROW
  EXECUTE PROCEDURE public.upd_points_in_polygons();

Edit following your comment/other answer:

The query is also wrong, as you are referencing the polygon table without using it. The query is therefore making a cross-product between the polygon table and the intersection result, which is not desired. Should you be tempted to restrict polygon by id, the query will fail for inserts as the ID doesn't exist, and therefore the cross product will be empty.

To solve it, you can remove any reference to the polygon table, which is not needed as you have the NEW geometry.

SELECT count(p.gid) 
INTO NEW.num_points 
FROM points p 
WHERE st_intersects(NEW.the_geom, p.the_geom);
  • I changed the trigger to BEFORE insert, but still get no updates. So probably there is something wrong with my update string. – geom Sep 8 '18 at 20:06
0

OK, on pgAdmin 4 I had the problem, that the updates were only visible after saving and reloading the data.

I had to change my update string as well, so that it applies only to the actual row:

SELECT count(p.gid) INTO NEW.num_points 
FROM polygons po, points p 
WHERE st_intersects(NEW.the_geom, p.the_geom) 
AND po.gid = NEW.gid;

works fine now. thanks for help.

  • This query will not work for inserts. See my updated answer – JGH Sep 9 '18 at 21:34

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.