如何使用ST_DWithin在PostGIS触发器中筛选指定半径内非空最近对象ID

调整逻辑说明 #

你需要在原代码的两个LATERAL子查询中新增筛选条件即可实现需求：

1. 过滤structures表中node_id为空的记录
2. 通过ST_DWithin函数限制匹配的点位必须在线端点20英尺半径范围内，你使用的SRID 2346单位本身就是美制英尺，因此距离阈值直接填20即可

完整修改后代码 #

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
           WHERE s.node_id IS NOT NULL 
             AND ST_DWithin(ST_EndPoint((dump_line).geom), ST_SetSRID(s.geom,2346), 20)
           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
           WHERE s.node_id IS NOT NULL 
             AND ST_DWithin(ST_StartPoint((dump_line).geom), ST_SetSRID(s.geom,2346), 20)
           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();

补充说明 #

新增的ST_DWithin筛选可以直接复用structures表的空间索引，相比先查最近点再判断距离的写法查询效率更高。如果某一端点没有符合条件的匹配对象，对应的dwn_str/up_str字段会自动赋值为NULL，符合需求。

内容的提问来源于stack exchange，提问作者AThomspon