PostGIS: relational value for spatial intersection

Question

I have two layers:

1. nomen : point layer
2. nomenclature : polygon layer

In PostGIS, when adding a point, you must take the nomenclature value by intersection with the polygons layer and insert it in the nomen field.

In QGIS, in the attributes form of the point layer, I use the default expression for the nomen field:

array_to_string(overlay_intersects('polygons', "nomenclature"))

It works as expected, but I need it to be a function of the PostGIS database.

Answer 1

You have to create a trigger.

Test and trigger creation below :

-- DROP TABLE IF EXISTS public.nomen;
-- DROP TABLE IF EXISTS public.nomenclature;
CREATE TABLE public.nomen (nomen SMALLINT, geom GEOMETRY(POINT, 2154));
CREATE TABLE public.nomenclature (nomenclature SMALLINT, geom GEOMETRY(POLYGON, 2154));

-- Trigger part
CREATE OR REPLACE FUNCTION func_trigger_nomen()
  RETURNS trigger AS
$$
BEGIN
     NEW.nomen = (SELECT poly.nomenclature
     FROM nomenclature poly
     WHERE ST_CONTAINS(poly.geom, NEW.geom)
     LIMIT 1);
     RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_nomen
  BEFORE INSERT OR UPDATE ON public.nomen
  FOR EACH ROW
  EXECUTE PROCEDURE func_trigger_nomen();
-- End of trigger part

INSERT INTO public.nomenclature (nomenclature, geom) VALUES (42, ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'));
INSERT INTO public.nomen (geom) VALUES (ST_GeomFromText('POINT(1 1)'));
SELECT * FROM public.nomen;