I have two polygon tables (SRID=4326) in postgres: t_areas
and t_sites
. Once I insert through QGIS 3,8 a new area in t_areas
I'd like PostGIS (by a trigger) to list all sites within a certain km range from new area and insert this list to a memo
field of the t_area
table, for ex.: sitename01 -> 0,3 km, sitename02 -> 0,12 km
... etc. The distance to be calculated between borders, not centroids.
I've managed to compose a query on existing data like:
select array_to_string (array ( SELECT t_sites.shortname || ' -> ' ||
round(st_distance(geography (t_areas.geom), geography
(t_sites.geom)))/1000 || ' km' AS distance FROM t_areas, t_sites WHERE
t_areas.id_area=133 and round(st_distance(geography (t_sites.geom),
geography (t_areas.geom)))/1000 < 5 ORDER BY distance ASC), ', ')
and even got result like this:
Ванино Поле -> 0.661 km, Голубково 1 -> 3.636 km, Голубково 2 -> 4.03
km, Голубково 3 -> 3.661 km, Задубье 2 -> 0.005 km, Заорешье 1 ->
4.582 km, Заорешье 2 -> 4.631 km, Калгановка -> 4.385 km, Кут 2 -> 1.526 km, Наволок 1 -> 3.359 km, Петровская Горка 1 - and so on...
but I can't realize how to configure a function and a trigger to force PostGIS then automatically update the t_areas.memo
field since a new area serial id_area
hasn't been generated yet and so I can't point the function to that id (unlike in the query above).