[postgis-users] automatically update id wheather point is inside polygon

[Matthias Ludwig]

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();