MySQL 5.7地理空间匹配适配实操指南_Mysql

一. 背景

为实现警情案例的区域化管理与精准溯源，需完成 “经纬度警情案例与围栏区域的空间匹配” 核心任务：基于含精准经纬度坐标的警情数据，通过空间几何计算判定每个警情所属的预设围栏区域，最终输出 “警情信息 - 经纬度 - 围栏标识” 的关联整合结果，为后续区域警情分析、资源调度提供数据支撑。

输入数据说明

警情案例数据：

含具体警情相关信息及对应的经纬度坐标（示例数据如下）

id	longitude	latitude	incident_content
1	113.886000	22.558000	壹方城购物中心前发生两车追尾事故，造成交通拥堵，需交警前往处理
2	113.868000	22.578000	西乡地铁站b出口发生盗窃案件，嫌疑人已逃跑，需要民警介入调查
3	113.810000	22.639000	t3航站楼2号门有旅客突发晕倒，需要医疗救助
4	113.915000	22.578000	新安公园旁餐饮店发生小火情，已初步控制，需要消防检查
5	113.880000	22.561000	宝安中心区夜间施工噪声扰民，居民多次投诉无果
6	113.880500	22.583200	宝安大仟里夜间施工噪声扰民，居民多次投诉无果

围栏数据：

包含围栏唯一名称及区域范围定义，支持 圆形围栏 和 多边形围栏 两种类型（字段互斥，同一围栏仅对应一种类型），示例如下：

name	geofence_point	radius	geofence_polygon
宝安大仟里商圈	point(113.8781 22.5806)	500
西丽国际创新谷商圈	point(113.9472 22.5925)	600
高新园万象城商圈	point(113.9398 22.5412)	800
宝安中心区			polygon((113.875 22.556,113.885 22.556,113.885 22.566,113.875 22.566,113.875 22.556))
西乡街道			polygon((113.86 22.575,113.87 22.575,113.87 22.585,113.865 22.588,113.86 22.585,113.86 22.575))
宝安机场			polygon((113.805 22.635,113.815 22.635,113.815 22.645,113.81 22.648,113.805 22.645,113.805 22.635))
新安街道			polygon((113.91 22.572,113.92 22.572,113.92 22.582,113.915 22.584,113.91 22.582,113.91 22.572))

预期输出结果

核心匹配规则

优先保障匹配唯一性：若单个警情同时满足多个围栏的判定条件（极端重叠场景），按 “多边形围栏优先于圆形围栏”“小范围围栏优先于大范围围栏” 的优先级输出唯一结果；
无匹配处理：若警情坐标未落在任何预设围栏内，围栏名称字段留空。

预期输出结果

输出整合后的结构化数据，包含警情全量信息、经纬度及对应围栏名称，示例如下：

id	longitude	latitude	incident_content	name
6	113.880500	22.583200	宝安大仟里夜间施工噪声扰民，居民多次投诉无果	宝安大仟里商圈
5	113.880000	22.561000	宝安中心区夜间施工噪声扰民，居民多次投诉无果	宝安中心区
2	113.868000	22.578000	西乡地铁站b出口发生盗窃案件，嫌疑人已逃跑，需要民警介入调查	西乡街道
3	113.810000	22.639000	t3航站楼2号门有旅客突发晕倒，需要医疗救助	宝安机场
4	113.915000	22.578000	新安公园旁餐饮店发生小火情，已初步控制，需要消防检查	新安街道
1	113.886000	22.558000	壹方城购物中心前发生两车追尾事故，造成交通拥堵，需交警前往处理

二 . 实操

1.mysql 5.7 环境部署（docker 方式）

1.1 部署脚本优化

优化说明：修复日志查看容器名不一致问题，增加目录提前创建逻辑，避免挂载失败，同时优化脚本可读性和可维护性。

# 1. 创建相关目录（避免挂载时权限或目录不存在问题）
mkdir -p /home/mysql/{conf,data,logs}
cd /home/mysql

# 2. 编写 docker-compose 配置文件
cat > /home/mysql/docker-compose.yml << eof
version: '3'
services:
  scs-mysql: 
    image: mysql:5.7.30
    hostname: scs-mysql
    container_name: scs-mysql
    environment:
      - mysql_root_password=123456
      - tz=asia/shanghai
    restart: always
    healthcheck:
      test: ["cmd", "mysqladmin" ,"ping", "-h", "localhost", "-uroot", "-p123456"]
      interval: 5s
      timeout: 1s
      retries: 10
    privileged: true
    ports:
      - 3306:3306
    volumes:
      - $pwd/data:/var/lib/mysql
      - $pwd/logs:/var/log/mysql
      - $pwd/conf/my.cnf:/etc/mysql/my.cnf
    command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
    entrypoint: bash -c "chown -r mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"

eof

# 3. 编写 mysql 配置文件
cat > /home/mysql/conf/my.cnf << eof
!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysql.server]
default-character-set=utf8

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1024
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql/slow.log
log_error=/var/log/mysql/error.log
eof


# 4. 启动容器并查看日志
docker-compose up -d
docker logs -f scs-mysql

1.2 部署注意事项

确保服务器已安装 docker 和 docker compose；
若 3306 端口已被占用，可修改 ports 中的宿主端口（如 3307:3306）；
部署完成后，可通过 mysql -h 127.0.0.1 -uroot -p123456 验证连接。

2. 地理空间知识说明

2.1 地理空间字段类型

分类	类型	描述	示例（wkt格式）
多边形类型	`polygon`	一个由封闭边界定义的多边形区域。	`polygon((0 0,10 0,10 10,0 10,0 0))`
单值类型	`point`	一个坐标点。	`point(121.213342 31.234532)`
集合类型	`multipolygon`（暂时不用）	多个多边形的集合。	`multipolygon(((0 0, 100 0, 100 100, 0 100, 0 0), (20 20, 80 20, 80 80, 20 80, 20 20)), ((200 0, 250 0, 250 50, 200 50, 200 0)))`

2.2 地理函数

函数类别	函数示例	用途说明
数据转换	`st_geomfromtext('point(1 2)')`	将文本（wkt格式）转换为空间数据。
	`st_astext(geom_column)`	将空间数据转换回可读的文本格式。
空间关系判断	`st_within(point, polygon)`	判断一个点是否在一个多边形内。
	`st_contains(polygon, point)`	判断一个多边形是否包含一个点。
空间计算	`st_distance_sphere(point1, point2)`	计算两点在地球球面上的近似距离（单位：米）。

3.地理匹配实战：数据库表设计与 sql 操作

3.1 表结构设计

-- 区域围栏表
drop table if exists mcp_geofence_xxxx;
create table `mcp_geofence_xxxx` (
  `name` varchar(100) comment '围栏名称',
  `geofence_point` point  null comment '圆心坐标',
  `radius` double null comment '半径（单位：米）',
  `geofence_polygon` polygon null comment '边界'
);
-- spatial key `idx_geofence` (`geofence_point`),
-- spatial key `idx_geofence` (`geofence_polygon`)
-- 由于空间索引不允许为空，所以删除

-- 警情表
drop table if exists mcp_police_test;
create table mcp_police_test (
    id int primary key auto_increment,
    longitude decimal(10,6) not null comment '经度',
    latitude decimal(10,6) not null comment '纬度',
    incident_content text not null comment '警情内容',
    index idx_location(longitude, latitude)
) comment='警情信息表';

3.2 数据插入（场景示例）

-- 圆形
-- 宝安大仟里商圈（大约位置：22.5806° n, 113.8781° e）
insert into `mcp_geofence_xxxx` (`name`, `geofence_point`, `radius`) 
values ('宝安大仟里商圈', st_geomfromtext('point(113.8781 22.5806)'), 500);

-- 西丽国际创新谷商圈（大约位置：22.5925° n, 113.9472° e）
insert into `mcp_geofence_xxxx` (`name`, `geofence_point`, `radius`) 
values ('西丽国际创新谷商圈', st_geomfromtext('point(113.9472 22.5925)'), 600);

-- 高新园万象城商圈（大约位置：22.5412° n, 113.9398° e）
insert into `mcp_geofence_xxxx` (`name`, `geofence_point`, `radius`) 
values ('高新园万象城商圈', st_geomfromtext('point(113.9398 22.5412)'), 800);

-- 多边形
insert into mcp_geofence_xxxx (name, geofence_polygon) values ('宝安中心区',st_geomfromtext('polygon((113.875 22.556, 113.885 22.556, 113.885 22.566, 113.875 22.566, 113.875 22.556))')),('西乡街道',st_geomfromtext('polygon((113.860 22.575, 113.870 22.575, 113.870 22.585, 113.865 22.588, 113.860 22.585, 113.860 22.575))')),('宝安机场',st_geomfromtext('polygon((113.805 22.635, 113.815 22.635, 113.815 22.645, 113.810 22.648, 113.805 22.645, 113.805 22.635))')),('新安街道',st_geomfromtext('polygon((113.910 22.572, 113.920 22.572, 113.920 22.582, 113.915 22.584, 113.910 22.582, 113.910 22.572))'));

select name, st_astext(geofence_point), radius,st_astext(geofence_polygon) as point_text from mcp_geofence_xxxx ;



-- 插入6条分布在宝安区不同地点的警情数据
insert into mcp_police_test 
(longitude, latitude, incident_content) values

(113.886, 22.558, 
 '壹方城购物中心前发生两车追尾事故，造成交通拥堵，需交警前往处理'),

-- 2. 西乡街道 - 治安事件
(113.868, 22.578,
 '西乡地铁站b出口发生盗窃案件，嫌疑人已逃跑，需要民警介入调查'),

-- 3. 宝安机场 - 紧急求助
(113.810, 22.639,
 't3航站楼2号门有旅客突发晕倒，需要医疗救助'),

-- 4. 新安街道 - 消防警情
(113.915, 22.578,
 '新安公园旁餐饮店发生小火情，已初步控制，需要消防检查'),

-- 5. 宝安中心区 - 噪声投诉
(113.880, 22.561,
 '宝安中心区夜间施工噪声扰民，居民多次投诉无果'),
-- 6. 宝安大仟里 - 噪声投诉
(113.8805,22.5832, '宝安大仟里夜间施工噪声扰民，居民多次投诉无果');

3.3 地理匹配查询（核心逻辑）

drop table if exists mcp_police_result; 
  create table mcp_police_result select 
      pi.id,
      pi.incident_content,
      pi.longitude,
      pi.latitude,
      gf.name
  from 
      mcp_police_test pi
  left join 
      mcp_geofence_xxxx gf on (
        (gf.geofence_polygon is not null 
         and st_within(st_geomfromtext(concat('point(', pi.longitude, ' ', pi.latitude, ')')),gf.geofence_polygon))
        or
        (gf.geofence_point is not null and gf.radius is not null
              and st_distance_sphere(
                  st_geomfromtext(concat('point(', pi.longitude, ' ', pi.latitude, ')')),
                  gf.geofence_point
              ) <= gf.radius)
      );

select * from mcp_police_result;