1

Updating committee name based on a spatial join within a trigger in Postgis. It appears right to me and it runs and executes successfully but still comes up with an error when I try to create a new feature.

CREATE OR REPLACE FUNCTION tf_committee()
  RETURNS trigger AS
$BODY$

BEGIN

NEW.committee := c.committee_name
        FROM housing_development_prospectus.committee as hdp, committee.committee_name as c
        WHERE hdp.wkb_geometry && NEW.wkb_geometry AND
        ST_Intersects(NEW.wkb_geometry, c.wkb_geometry);

RETURN NEW;
END;
$BODY$
  • What error do you get? For what it is worth you don't need the line with &&, as ST_Intersects automatically check for bbox intersection (though it won't make any difference to execution or results). – John Powell Jan 19 '15 at 13:41
  • I have removed the line with && re jigged the code. This is the error I then get: [main]>Could not commit changes to layer housing_development_prospectus Errors: ERROR: 1 feature(s) not added. Provider errors: PostGIS error while adding features: ERROR: query "SELECT c.committee_name FROM test_zone.housing_development_prospectus as hdp, political.committee as c WHERE ST_Intersects(NEW.wkb_geometry, c.wkb_geometry)" returned more than one row CONTEXT: PL/pgSQL function "tf_committee" line 4 at assignment[main] – daniel franklin Jan 19 '15 at 14:08
  • 1
    Sorry to be a pain, but it is better to put all the info in an edit to the question, than in comments. Anyway, the "returned more than one row" error is due to the fact that NEW.wkb_geometry intersects with more than one comittee_name.wkt_geometry, so it is impossible to assign just one value to NEW.committee. You need to use limit 1 or some other mechanism to return just one row. – John Powell Jan 19 '15 at 14:20
  • @JohnBarça - You should post your comment as an answer buddy :) – Joseph Sep 10 '15 at 10:40
2

The returned more than one row error is due to the fact that NEW.wkb_geometry intersects with more than one comittee_name.wkt_geometry, so it is impossible to assign just one value to NEW.committee. You need to use LIMIT 1 and/or some other mechanism to return just one row. You could also possibly use array_agg to group all the returned committee names to one variable.

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.