Using ST_DWithin to get id of closest object within an area

Question

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();

Just add a corresponding `WHERE` clause to the subqueries. – Laurenz Albe Jul 20 '21 at 15:03 — Laurenz Albe, Jul 20 '21 at 15:03

Jim Jones · Answer 1 · 2021-07-20T16:15:53.090

You're most likely using ST_SetSRID wrong. It is supposed to be used, as the name suggests, to set a SRS to a geometry that is either wrong or non-existent. However, your geometries already have a SRS, namely EPSG:2965. So, if you want to perform your spatial operations using a different SRS and you can afford doing this change in query time (mind the overhead!), use ST_Transform. Other than that, just place the ST_DWithin in the WHERE clauses of your subqueries:

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

Change the placeholder your_dist to the distance you want based on the unit of your new SRS.
Consider updating your geometries to use the "right" SRS instead of doing it in query time.

Further reading: How to use ST_Transform

I dropped this project for some time, but have finally come back to it. Using the code as you wrote it results in an issue. It seems to insert the same upstream node id into both the upstream and downstream column. — AThomspon, Aug 16 '21 at 16:40
@AThomspon can you at least provide a scenario where it does happen? Put it all in a fiddle so that we can reproduce your error — Jim Jones, Aug 17 '21 at 04:24
I found that the issue came from the use of `ST_DWithin()` as it returned a Boolean when I was wanting to return a numeric value for distance. My posted solution better illustrates that. — AThomspon, Aug 19 '21 at 18:18

AThomspon · Accepted Answer · 2021-08-19T19:46:45.550

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.

Using ST_DWithin to get id of closest object within an area

2 Answers2