如何在Sequelize中结合ST_Within编写复杂WHERE子句？

错误原因 #

SQL的执行顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，你在SELECT里定义的字段别名（比如contains）在WHERE阶段还未生成，因此数据库会提示该字段不存在于目标表中。

解决方案 #

1. 直接在WHERE子句中调用ST_Within

这是最简洁的写法，把空间判断逻辑直接嵌入WHERE条件，无需先作为字段返回：

SELECT id, geom, device_name
FROM Device
WHERE ST_Within(geom, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) = true;

2. 用子查询/CTE先计算字段再筛选

如果需要同时返回空间判断结果并筛选，可通过子查询或CTE提前计算字段，外层再做筛选：

-- 子查询写法
SELECT *
FROM (
    SELECT id, geom, device_name,
           ST_Within(geom, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS is_inside_boundary
    FROM Device
) AS device_subquery
WHERE is_inside_boundary = true;

-- CTE写法（可读性更强）
WITH device_with_boundary_check AS (
    SELECT id, geom, device_name,
           ST_Within(geom, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS is_inside_boundary
    FROM Device
)
SELECT *
FROM device_with_boundary_check
WHERE is_inside_boundary = true;

3. 用HAVING子句筛选（适用于含聚合的场景）

如果查询包含GROUP BY聚合操作，可使用HAVING子句筛选——HAVING在SELECT之后执行，能识别字段别名：

SELECT device_group, COUNT(*),
       ST_Within(geom, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS is_inside_boundary
FROM Device
GROUP BY device_group, geom
HAVING is_inside_boundary = true;

额外注意事项 #

• 确保Device表的geom字段是PostGIS的GEOMETRY类型，若未注册空间字段需先执行AddGeometryColumn或通过DDL定义。
• 边界POLYGON的WKT格式需正确闭合（首尾坐标一致），避免空间函数报错。
• 若边界POLYGON存储在另一张表中，可通过JOIN关联后执行ST_Within判断：

SELECT d.id, d.geom, d.device_name
FROM Device d
JOIN Boundary b ON ST_Within(d.geom, b.boundary_geom)
WHERE b.boundary_id = 1;

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