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
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 areference_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.
-
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– tonyduky2023年03月18日 17:29:49 +00:00Commented 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– tonyduky2023年03月18日 18:02:43 +00:00Commented Mar 18, 2023 at 18:02