GIS 地理围栏与区域分析

这是 SRID 0 的平面围栏例子,坐标单位由业务定义;不能直接将 SRID 改成 4326 当作经纬度围栏。

DROP DATABASE IF EXISTS gis_fence_402;
CREATE DATABASE gis_fence_402;
USE gis_fence_402;
CREATE TABLE regions(id INT PRIMARY KEY, area POLYGON);
CREATE TABLE events(id INT PRIMARY KEY, customer VARCHAR(20), location POINT);
INSERT INTO regions VALUES (1,ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0))'));
INSERT INTO events VALUES (1,'inside',ST_GeomFromText('POINT(2 2)')),(2,'boundary',ST_GeomFromText('POINT(0 2)')),(3,'outside',ST_GeomFromText('POINT(5 5)'));
SELECT e.id,ST_Contains(r.area,e.location) AS contained,ST_Covers(r.area,e.location) AS covered FROM events e CROSS JOIN regions r ORDER BY e.id;
SELECT r.id,COUNT(*) AS covered_events FROM regions r JOIN events e ON ST_Covers(r.area,e.location) GROUP BY r.id;
EXPLAIN SELECT e.id FROM events e JOIN regions r ON ST_Covers(r.area,e.location);
DROP DATABASE gis_fence_402;

inside 点 contained/covered 均为 1;boundary 点 contained=0、covered=1;outside 均为 0,区域计数为 2。选择业务上是否包含边界后再决定 Contains 或 Covers,不能仅按函数名判断。

增加订单/设备维表后以主键 JOIN;重复事件须先按业务粒度处理,否则区域统计重复计数。无效多边形、NULL 和不同 SRID 是单独测试项。MBR 只比较包围框,不能代替精确几何关系;观察 EXPLAIN 与实际扫描,不能从有 ST 函数推出存在空间索引。

导出固定结果和区域数据以供复核。参见 GIS 入门和 空间函数。