如何批量检测points表中的点是否位于zones表的多边形区域内

场景1：检测所有点是否在zones表的任意多边形区域内 #

直接关联两张表，通过空间函数判断点与区域的包含关系：

SELECT
    p.id AS point_id,
    p.address,
    p.lat,
    p.long,
    z.id AS zone_id,
    z.name AS zone_name
FROM
    points p
INNER JOIN
    zones z 
ON ST_WITHIN(
    ST_GeomFromText(CONCAT('POINT(', p.long, ' ', p.lat, ')')),
    z.area
);

关键注意事项： #

• WKT格式的POINT要求经度在前，纬度在后，所以拼接时要写p.long + p.lat，和表中lat（纬度）、long（经度）的字段顺序相反，这是最容易出错的细节。
• 若只需点的信息，可简化为SELECT p.*，去掉zone相关字段。

场景2：检测所有点是否在指定的单个多边形内（对应你之前的@multi_polygon变量） #

如果不需要关联zones表，而是用自定义多边形，修改为批量查询points表即可：

-- 定义目标多边形
SET @multi_polygon = ST_GeomFromText('MultiPolygon(((-72.2796106 42.9282426, -72.2819495 42.9280383, -72.2814775 42.9264358, -72.2792459 42.9267578, -72.2796106 42.9282426),(1 1,1 2,2 2,2 1,1 1)))');

-- 批量查询符合条件的点
SELECT
    *
FROM
    points p
WHERE
    ST_WITHIN(
        ST_GeomFromText(CONCAT('POINT(', p.long, ' ', p.lat, ')')),
        @multi_polygon
    );

性能优化建议（数据量大时必看） #

如果points或zones表数据量较大，动态拼接转换geometry会导致查询变慢，建议做以下优化：

1. 给zones表的空间字段创建空间索引：

CREATE SPATIAL INDEX idx_zones_area ON zones(area);

2. 给points表添加持久化的geometry字段并创建索引：

-- 添加geometry字段
ALTER TABLE points ADD COLUMN point_geom geometry;

-- 批量生成geometry数据
UPDATE points SET point_geom = ST_GeomFromText(CONCAT('POINT(', long, ' ', lat, ')'));

-- 创建空间索引
CREATE SPATIAL INDEX idx_points_point_geom ON points(point_geom);

优化后的查询可以简化为：

-- 关联zones表的情况
SELECT p.*, z.name FROM points p JOIN zones z ON ST_WITHIN(p.point_geom, z.area);

-- 指定自定义多边形的情况
SET @multi_polygon = ST_GeomFromText('你的多边形WKT');
SELECT * FROM points p WHERE ST_WITHIN(p.point_geom, @multi_polygon);

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