CODEWITHSUNDEEP
postgresqlpostgisqgispostgresql-triggers
AThomspon
AThomspon

Reputation: 135

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

Upvotes: 1

Views: 428

Answers (2)

AThomspon
AThomspon

Reputation: 135

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.

Upvotes: 0

Jim Jones
Jim Jones

Reputation: 19653

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

Upvotes: 2

Related Questions