2

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.

1 Answer 1

2

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.

2
  • First solution works but Qgis does not recognize the column format so it's impossible to make any manual changes to these values afterwards wich is not ideal. I tried the second one but when I want to create a new entity I got an error saying that reference_geom does not exist. Also I had to remove the TABLE after ON because of a syntax error according to pgadmin. If I want to be able to edit the values manually afterwards, is it better to use a trigger than generated columns?
    – tonyduky
    Commented Mar 18, 2023 at 17:29
  • is there a way to use generated columns but to get the same result in a varchar column instead of an array?
    – tonyduky
    Commented Mar 18, 2023 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.