1

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

2

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.

CREATE OR REPLACE FUNCTION public.tg_f() RETURNS trigger AS
    $BODY$
    BEGIN

        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
    END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER tg BEFORE INSERT OR UPDATE
   ON  public.t_areas FOR EACH ROW
   EXECUTE PROCEDURE  public.tg_f();
3
  • This perfectly works!! Thanks a lot!
    – Stas
    Feb 16 '20 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
    Feb 16 '20 at 15:37
  • This needs ST_DWithin.
    – geozelot
    Feb 16 '20 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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