PostGIS三表关联空间查询问题：结果重复或无返回

问题背景 #

现有三个空间数据表：

• Table_1：包含project_id（项目ID）、geom（点类型几何字段）
• Table_2：包含polygon_name（多边形名称）、geom（多面类型几何字段）
• Table_3：包含point_id（点ID）、geom（点类型几何字段）

需求逻辑：先找出与Table_1中指定project_id对应的点相交的所有Table_2多边形，再筛选出落在这些多边形内的Table_3点，返回point_id和geom字段。

第一个查询重复数据的原因与解决 #

第一个查询出现大量重复，是因为同一个Table_3的点可能落在多个符合条件的Table_2多边形中，或者同一个Table_2多边形对应多个同project_id的Table_1点，关联后生成了多条重复记录。

最简单的修复方式是给查询结果加DISTINCT去重：

SELECT DISTINCT t3.point_id, t3.geom
FROM Table_3 AS t3
JOIN Table_2 AS t2 ON ST_Intersects(t3.geom, t2.geom)
JOIN Table_1 AS t1 ON ST_Intersects(t2.geom, t1.geom)
WHERE t1.project_id = desired_project_id;

如果追求性能，推荐先筛选出符合条件的Table_2多边形集合，再关联Table_3（避免多次关联带来的冗余计算）：

SELECT t3.point_id, t3.geom
FROM Table_3 AS t3
JOIN (
    -- 先获取所有和指定project_id的Table_1点相交的Table_2多边形（去重）
    SELECT DISTINCT t2.geom
    FROM Table_2 AS t2
    JOIN Table_1 AS t1 ON ST_Intersects(t2.geom, t1.geom)
    WHERE t1.project_id = desired_project_id
) AS filtered_t2 ON ST_Intersects(t3.geom, filtered_t2.geom);

第二个查询无结果的原因 #

第二个查询存在两个致命问题：

1. 语法错误：ST_Within(t3.geom, ST_Intersection(t1.geom, t2.geom) 缺少一个闭合括号，数据库无法通过语法校验，自然不会返回结果。
2. 逻辑错误：ST_Intersection(t1.geom, t2.geom) 是点（t1的geom）与多边形（t2的geom）的交集，结果就是t1的点本身。ST_Within(t3.geom, 该点) 只有当t3的点与t1的点完全重合时才会成立，完全不符合你的需求。

最优写法推荐 #

首先给三个表的geom字段创建空间索引（大幅提升空间查询性能）：

CREATE INDEX idx_table1_geom ON Table_1 USING GIST(geom);
CREATE INDEX idx_table2_geom ON Table_2 USING GIST(geom);
CREATE INDEX idx_table3_geom ON Table_3 USING GIST(geom);

然后使用子查询筛选目标多边形，再关联Table_3，用ST_Contains更贴合“位于多边形内”的语义（若需排除边界点，可替换为ST_Within）：

SELECT DISTINCT t3.point_id, t3.geom
FROM Table_3 t3
JOIN (
    SELECT DISTINCT t2.geom
    FROM Table_2 t2
    JOIN Table_1 t1 ON ST_Intersects(t2.geom, t1.geom)
    WHERE t1.project_id = desired_project_id
) t2_filtered ON ST_Contains(t2_filtered.geom, t3.geom);

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