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).

1 Answer 1


The new (or modified) row is available in the trigger using the NEW record. You can directly use its geometry or any other attributes to query the 2nd table. The output of the query is then saved in the NEW record, and when done the entire record is commited.


        SELECT array_to_string(
            array ( 
                SELECT t_sites.shortname || 
                    ' -> ' ||  
                    round(st_distance(NEW.geom::geography, t_sites.geom::geography))/1000 || ' km' AS distance 
                FROM t_sites 
                WHERE round(
                    st_distance(t_sites.geom::geography, NEW.geom::geography))/1000 < 5 
                ORDER BY distance ASC), ', ')
        INTO new.memo;

        RETURN NEW; -- return the updated row
$BODY$ LANGUAGE plpgsql;

   ON  public.t_areas FOR EACH ROW
   EXECUTE PROCEDURE  public.tg_f();
  • This perfectly works!! Thanks a lot!
    – Stas
    Commented Feb 16, 2020 at 15:00
  • Done! Tested this solution on a few areas, including 1000 km long and 200 m wide pipeline area to calc archaeological sites within 5, 1 and 0,5 km distance and this works, though saving the updated area record takes 5-6 seconds in qgis. The less area is - the less time takes saving. Similar and a bit modified solution should help to calc also distance and azimuth from a site to settled places around - this is what I'll try next. Thank you again!
    – Stas
    Commented Feb 16, 2020 at 15:37
  • This needs ST_DWithin.
    – geozelot
    Commented Feb 16, 2020 at 20:39

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.