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.

geozelot
31.4k4 gold badges38 silver badges59 bronze badges
asked Mar 14, 2023 at 14:57

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.

answered Mar 14, 2023 at 16:15
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? 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? Commented Mar 18, 2023 at 18:02

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.