I have the following setup with points in polygons. After moving a point the db should update the hex_id number automatically depending whether polygon it falls inside (or not). I solved this with a trigger. Is this the only and/or best solution?
CREATE TABLE points ( gid serial NOT NULL, geom geometry(Point,3857), hex_id integer, CONSTRAINT energiepoints_pkey PRIMARY KEY (gid), CONSTRAINT energiepoints_hexagone_fkey FOREIGN KEY (hex_id) REFERENCES hexagone (gid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE TABLE hexagone ( gid serial NOT NULL, geom geometry(Polygon,3857), CONSTRAINT hexagone_pkey PRIMARY KEY (gid) ); DROP TRIGGER IF EXISTS trigger_update_hex_id ON energiepoints; DROP FUNCTION update_hex_id(); CREATE OR REPLACE FUNCTION update_hex_id() RETURNS TRIGGER AS $$ BEGIN NEW.hex_id = (SELECT gid FROM hexagone as hex WHERE ST_Intersects(NEW.geom, hex.geom)); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_update_hex_id BEFORE INSERT OR UPDATE ON points FOR EACH ROW EXECUTE PROCEDURE update_hex_id(); _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users