MySQL 空间数据类型 GIS：地图功能的数据库实现

开场白做 LBS基于位置的服务的时候很多人直接用经纬度两个字段存然后算距离用公式在应用层算。数据量小的时候没问题数据一大每次查附近的人都要全表扫描算一遍距离性能根本扛不住。我之前做一个附近的门店查询功能10 万条门店数据每条都要算一遍距离再排序直接把接口拖到 5 秒以上。后来换成 MySQL 的空间数据类型和空间索引查询时间降到了 50 毫秒以内。空间数据类型MySQL 支持的空间数据类型类型说明示例GEOMETRY空间数据的基类可以存任何几何类型通用POINT点经纬度门店位置LINESTRING线一组有序的点路线POLYGON多边形封闭的区域商圈MULTIPOINT多个点-MULTILINESTRING多条线-MULTIPOLYGON多个多边形复杂区域GEOMETRYCOLLECTION混合集合-最常用的是 POINT 和 POLYGON。创建空间列CREATETABLEstores(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),locationPOINTNOTNULLSRID4326,SPATIALINDEXidx_location(location));SRID 4326 是 WGS84 坐标系就是 GPS 用的经纬度坐标系。MySQL 8.0 开始支持指定 SRID不同 SRID 的空间数据不能互相计算。 ## 插入和查询空间数据 ### 插入数据 sql-- 用 ST_GeomFromText 插入INSERTINTOstores(name,location)VALUES(星巴克国贸店,ST_GeomFromText(POINT(116.461447 39.908714),4326));-- MySQL 8.0 推荐用 ST_PointINSERTINTOstores(name,location)VALUES(瑞幸望京店,ST_Point(116.480817,39.993378,4326));注意 POINT 的参数顺序是POINT(经度 纬度)即 X经度Y纬度。这个顺序和很多人习惯的纬度在前不一样搞反了距离计算会出问题。我之前就搞反过查出来的附近门店全都不对。 ### 查询坐标 sql-- 获取 WKT 格式SELECTid,name,ST_AsText(location)FROMstores;-- 获取经度和纬度SELECTid,name,ST_Longitude(location)ASlng,ST_Latitude(location)ASlatFROMstores;计算距离-- 计算两个点之间的距离米使用 WGS84 椭球体SELECTid,name,ST_Distance_Sphere(location,ST_Point(116.397128,39.916527,4326))ASdistanceFROMstoresORDERBYdistanceLIMIT10;ST_Distance_Sphere返回的是球面距离单位是米考虑了地球曲率比简单的勾股定理准确得多。 ### 查询范围内的点 sql-- 查询 5 公里内的门店SELECTid,name,ST_Distance_Sphere(location,ST_Point(116.397128,39.916527,4326))ASdistanceFROMstoresWHEREST_Contains(ST_Buffer(ST_Point(116.397128,39.916527,4326),5000),location)ORDERBYdistance;等等ST_Buffer 在经纬度坐标系下单位是度不是米5 公里不是 5000 度。正确做法是用 MBRTContains sql-- 先算 5km 对应的经纬度范围近似SETlat39.916527;SETlng116.397128;SETdistance5000;-- 5km-- 纬度方向 1 度约 111kmSETlat_deltadistance/111000;-- 经度方向 1 度约 111km * cos(纬度)SETlng_deltadistance/(111000*COS(RADIANS(lat)));-- 用矩形范围过滤SELECTid,name,ST_Distance_Sphere(location,ST_Point(lng,lat,4326))ASdistanceFROMstoresWHEREST_Longitude(location)BETWEENlng-lng_deltaANDlnglng_deltaANDST_Latitude(location)BETWEENlat-lat_deltaANDlatlat_deltaANDST_Distance_Sphere(location,ST_Point(lng,lat,4326))distanceORDERBYdistance;这个写法先用矩形粗筛走索引再用球面距离精筛兼顾性能和精度。 ## 空间索引 空间索引是 R-Tree 索引和 B-Tree 不一样专门用于空间数据的范围查询。 ### 创建空间索引 sql-- 建表时创建CREATETABLEstores(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),locationPOINTNOTNULLSRID4326,SPATIALINDEXidx_location(location));-- 后续添加ALTERTABLEstoresADDSPATIALINDEXidx_location(location);空间索引的要求列必须声明为 NOT NULLMySQL 8.0 建议指定 SRID只能对单列建空间索引不支持复合空间索引的查询优化-- 使用 MBRContains 走空间索引SELECT*FROMstoresWHEREMBRContains(ST_GeomFromText(POLYGON((116.3 39.8, 116.5 39.8, 116.5 40.0, 116.3 40.0, 116.3 39.8)),4326),location);MBRContains 判断点是否在最小边界矩形MBR内走空间索引。ST_Contains 是精确判断但 8.0 之前不走索引。 实际开发中推荐组合使用先 MBRContains 粗筛走索引再 ST_Contains 或 ST_Distance_Sphere 精筛。 ## POLYGON 多边形操作 ### 存储商圈/区域 sqlCREATETABLEbusiness_districts(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),areaPOLYGONNOTNULLSRID4326,SPATIALINDEXidx_area(area));-- 插入一个商圈区域国贸商圈简化为矩形INSERTINTObusiness_districts(name,area)VALUES(国贸商圈,ST_GeomFromText(POLYGON((116.44 39.89, 116.48 39.89, 116.48 39.92, 116.44 39.92, 116.44 39.89)),4326));POLYGON 的坐标必须首尾闭合第一个点和最后一个点要一样。 ### 查询点是否在区域内 sql-- 查询某个门店属于哪个商圈SELECTbd.nameFROMbusiness_districts bdWHEREST_Contains(bd.area,ST_Point(116.461447,39.908714,4326));查询区域内的所有门店SELECTs.id,s.nameFROMstores sJOINbusiness_districts bdONST_Contains(bd.area,s.location)WHEREbd.name国贸商圈;GeoJSON 支持MySQL 8.0MySQL 8.0 支持 GeoJSON 格式的导入导出-- GeoJSON 转内部格式INSERTINTOstores(name,location)VALUES(测试门店,ST_GeomFromGeoJSON({type:Point,coordinates:[116.461447,39.908714]}));-- 内部格式转 GeoJSONSELECTid,name,ST_AsGeoJSON(location)FROMstores;和前端地图组件高德、百度对接时特别方便前端直接用 GeoJSON 渲染。踩坑记录坑一POINT 参数顺序经度在前、纬度在后X, Y即POINT(lng, lat)。国内很多地图 API 是纬度在前搞反了距离计算全错。坑二SRID 不一致MySQL 8.0 要求计算的两个空间对象 SRID 必须一致否则报错ERROR 3516: SRID of geometry argument does not match SRID of function建表时统一指定 SRID 4326查询时也用 ST_Point(…, 4326) 传参。坑三空间索引不生效ST_Distance_Sphere 不会走空间索引它是对每一行计算距离。要先用 MBRContains 或矩形范围过滤减少计算量。坑四坐标精度丢失FLOAT 类型的经纬度精度不够约 10 米误差DOUBLE 或 DECIMAL(10,6) 才够用。MySQL 的 POINT 类型内部用 DOUBLE 存储精度没问题。小结空间数据类型比经纬度两列存更规范空间索引性能优势明显POINT 的参数顺序是经度在前、纬度在后ST_Distance_Sphere 算球面距离比勾股定理准确空间索引走 MBRContainsST_Distance_Sphere 不走索引附近查询的推荐模式矩形粗筛 球面距离精筛统一使用 SRID 4326避免 SRID 不一致报错相关阅读MySQL 官方文档 - Spatial Data TypesSpatial Function ReferenceOpenGIS 标准