[postgis-users] using spatial join inside trigger function to update a column

[Pekka Hurskainen]

Hi all,

I am a newbie in PostGIS and especially in triggers and spatial
functions. I would need some help from you guys to get this trigger to
work.

I have two tables, buildings and wards, both polygons. Whenever a new
building is inserted or updated, I want a trigger which automatically
updates column "ward" in the buildings table using spatial join
(ST_Intersects) with the wards table. The column "ward" in the wards
table stores the name of the ward as text.

Since a building in my data can lie on the boundary of two or even
three wards, I have used ST_PointOnSurface to calculate the centroid
of the building in order to get only one result of the spatial join.

I am using QGIS to edit my buildings table. After digizing a new
polygon and committing the changes, I don't get any errors, but the
ward field remains empty. I think there is something wrong with my
syntax.

Here is my code:

------------------

-- Function: landinfo.update_ward()

-- DROP FUNCTION landinfo.update_ward();

CREATE OR REPLACE FUNCTION landinfo.update_ward()
  RETURNS trigger AS
$BODY$
  BEGIN
    NEW.ward := w.ward
                  FROM landinfo.buildings as b, landinfo.wards as w
		  WHERE b.the_geom && NEW.the_geom AND
ST_Intersects(ST_PointOnSurface(NEW.the_geom), w.the_geom);
    RETURN NEW;
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION landinfo.update_ward() OWNER TO postgres;

------------------

-- Trigger: update_ward_value on landinfo.buildings

-- DROP TRIGGER update_ward_value ON landinfo.buildings;

CREATE TRIGGER update_ward_value
  BEFORE INSERT OR UPDATE
  ON landinfo.buildings
  FOR EACH ROW
  EXECUTE PROCEDURE landinfo.update_ward();


------------------


Thanks in advance!

Pekka