Using a intersects spatial query as default value in PostGIS table

Question

I have two PostgreSQL tables :

table_a (polygon) with field_1 table_b (polygon) with field_2

For field 2, I would like to get the corresponding value in field_2 of the polygons of table 2 inside or touching polygons of table_a

So far I was using a default expression in QGIS :

field_2 = aggregate ('table_a','concatenate',"field_a",intersects ($geometry,geometry(@parent)),',',"field_a")

but I would like to create the same result using the default value inside PostgreSQL directly.

Answer 1

If you are on PostgreSQL > 12.0 you could elegantly use Generated Columns:

• create a function to retrieve <field_1> values (as array) from <table_a> found for all <geom> that intersect a reference_geom:

  CREATE OR REPLACE FUNCTION concat_field_values(
    reference_geom GEOMETRY(POINT, 4326)
  ) RETURNS TEXT[] AS
    $$  
    SELECT
      ARRAY_AGG(t.<field_1>::TEXT)
    FROM
      <table_a> AS t
    WHERE
      ST_Intersects(t.<geom>, reference_geom)
    ;
    $$
    LANGUAGE SQL
    IMMUTABLE STRICT
    PARALLEL SAFE
  ;
  

• add the column definition to <table_b> (drop the column before if necessary):

  ALTER TABLE
    <table_b>
  ADD COLUMN
    <field_2> TEXT[]
      GENERATED ALWAYS AS ( concat_field_values(<geom>) ) STORED
  ;
  

---

Traditionally you'd implement a Trigger:

• create the trigger function:

  CREATE OR REPLACE FUNCTION concat_field_values()
    RETURNS TRIGGER AS
    $$
    BEGIN
      SELECT
        ARRAY_AGG(t.<field_1>::TEXT)
      FROM
        <table_a> AS t
      WHERE
        ST_Intersects(t.<geom>, reference_geom)
      INTO
        NEW.<field_2>
      ;
  
      RETURN NEW;
    END;
    $$
    LANGUAGE 'plpgsql'
  ;
  

• add the Trigger to the table:

  CREATE TRIGGER concat_field_values__trigger
    BEFORE INSERT
    ON TABLE <table_b>
    EXECUTE PROCEDURE concat_field_values()
  ;
  

---

Make sure you replace all identifiers wrapped in <> with their correct names!

Both solutions work for INSERT operations, and the Generated Column approach additionally recalculates the <field_2> values when the respective row gets modified.

Neither solution can handle cases where the geometries of other rows change, with side effects on present rows. This would require a more sophisticated Trigger setup.