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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
