postgresqlpostgisqgispostgresql-triggers

Using ST_DWithin to get id of closest object within an area


I have a trigger function in pgAdmin4 that grabs the closest object to the start point and end point of a line on placement in QGIS. However, I want to not only grab the id of the closest object, but only grab that id if the object is within a 20' radius, and only if the id of the object is not null.

This fiddle has my working trigger (a copy of the trigger can also be found below) as well as some sample data. I know that I need to add ST_DWithin to the function, but I am unsure how to go about it.

Below is the trigger code:

CREATE OR REPLACE FUNCTION insert_pipe() RETURNS TRIGGER AS $$
BEGIN
  SELECT 
  j.node_id,
  i.node_id
  INTO NEW.dwn_str, NEW.up_str
  FROM ST_Dump(ST_SetSRID(NEW.geom,2346)) dump_line,
  LATERAL (SELECT s.node_id,(ST_SetSRID(s.geom,2346)) 
           FROM structures s
           ORDER BY ST_EndPoint((dump_line).geom)<->(ST_SetSRID(s.geom,2346)) 
           LIMIT 1) j (node_id,geom_closest_downstream),
  LATERAL (SELECT s.node_id,(ST_SetSRID(s.geom,2346))
           FROM structures s
           ORDER BY ST_StartPoint((dump_line).geom)<->(ST_SetSRID(s.geom,2346)) 
           LIMIT 1) i (node_id,geom_closest_upstream);
           
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER t_insert_pipe 
BEFORE INSERT OR UPDATE ON pipes FOR EACH ROW EXECUTE PROCEDURE insert_pipe();

Solution

  • It turns out that my misunderstanding of ST_DWithin() is what cuased the errors. After taking some time away from this I came back and found a solution. Instead of ST_DWithin(), I used ST_Distance. Below is the code.

    BEGIN
      SELECT 
      j.node_id
      INTO NEW.dwn_str
      FROM ST_Dump(ST_SetSRID(NEW.geom,4326)) dump_line,
      LATERAL (SELECT s.node_id, 
                      ST_Distance(endpoint,ST_SetSRID(s.geom,4326)) as dist 
               FROM structures s,
                      Lateral (SELECT ST_EndPoint(ST_SetSRID(dump_line.geom,4326)) as endpoint
                              FROM ST_Dump(ST_SetSRID((NEW.geom),4326)) dump_line) l
               WHERE ST_Distance(endpoint, ST_SetSRID(s.geom,4326)) < '1'
               ORDER BY dist asc
               LIMIT 1) j;          
      
      RETURN NEW;
    END;
    

    I have two sets of this trigger. One for the upstream and one for the downstream. @JimJones was close, although the use of ST_DWithin() returned a Boolean and was causing issues. By nesting laterals I was able to do what I needed to.