MySQL高级讲座篇之：如何利用`Spatial Data Types`，构建一个基于地理位置的服务？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位，早上好（或者下午好，晚上好，取决于你什么时候看到这篇文章）。今天咱们来聊聊MySQL里面一个有点意思，但很多人又不太熟悉的家伙：Spatial Data Types。别怕，听名字高大上，其实用起来挺好玩的。咱们的目标是：学会用它来构建一个基于地理位置的服务。想象一下，你在做一个美食App，想让用户搜到附近的美食，或者做一个打车软件，想让司机知道附近有没有乘客。这就是Spatial Data Types大显身手的时候了。

一、啥是Spatial Data Types？

简单来说，Spatial Data Types就是MySQL用来存储地理位置信息的数据类型。就像INT用来存整数，VARCHAR用来存字符串一样，Spatial Data Types用来存地球上的点、线、面等等。

MySQL支持以下几种主要的Spatial Data Types：

| 数据类型 | 描述

POINT 和 GEOMETRY 用的最多，POINT 专门用来存点，比如经纬度， GEOMETRY则更灵活，可以存各种几何形状。

二、准备工作：开启空间数据支持

MySQL 5.7.21 版本之后，默认开启了空间数据支持。但为了以防万一，我们还是检查一下：

SHOW VARIABLES LIKE 'have_geometry';

如果Value是YES，那就没问题。如果是NO，那就需要在MySQL配置文件（my.cnf或my.ini）中添加以下配置：

[mysqld]
innodb_ft_enable_stopword=0
default-storage-engine=InnoDB
explicit_defaults_for_timestamp=true
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
innodb_spatial_index=ON

然后重启MySQL服务。

三、创建一个带有空间数据类型的表

咱们来创建一个restaurants表，用来存储餐馆的信息，其中包含餐馆的地理位置：

CREATE TABLE restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location POINT NOT NULL SRID 4326,
    address VARCHAR(255),
    cuisine VARCHAR(255),
    SPATIAL INDEX(location)
);

location POINT NOT NULL SRID 4326：这一列用来存储餐馆的经纬度信息，数据类型是POINT，NOT NULL表示不能为空。 SRID 4326表示使用WGS 84坐标系，这是最常用的经纬度坐标系。
SPATIAL INDEX(location)：这是空间索引，非常重要！有了它，才能高效地进行地理位置查询。必须在创建表的时候或者创建表之后添加。

四、插入一些数据

现在咱们往restaurants表里插入一些数据，注意使用ST_GeomFromText()函数来将经纬度字符串转换为POINT类型：

INSERT INTO restaurants (name, location, address, cuisine) VALUES
('老北京炸酱面', ST_GeomFromText('POINT(116.4074 39.9042)',4326), '王府井大街1号', '北京菜'),
('四川火锅', ST_GeomFromText('POINT(104.0665 30.6595)',4326), '春熙路步行街', '川菜'),
('广东早茶', ST_GeomFromText('POINT(113.2644 23.1291)',4326), '上下九步行街', '粤菜'),
('上海小笼包', ST_GeomFromText('POINT(121.4737 31.2304)',4326), '南京路步行街', '上海菜');

这里，ST_GeomFromText()函数接收两个参数：

第一个参数是表示经纬度的字符串，格式是'POINT(经度纬度)'。
第二个参数是SRID，这里我们用的是4326，表示WGS 84坐标系。

五、查询附近的美食

这才是重头戏！咱们要查询某个位置附近的美食。 MySQL提供了几个有用的空间函数：

ST_Distance()：计算两个地理位置之间的距离。
ST_Within()：判断一个地理位置是否在另一个地理位置之内。
MBRContains()：判断一个矩形是否包含另一个几何对象。
ST_Buffer()：创建一个指定距离的缓冲区。

咱们用ST_Distance()来查询距离某个经纬度10公里内的餐馆：

SELECT
    id,
    name,
    address,
    cuisine,
    ST_Distance(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)',4326)
    ) * 111195 AS distance -- 乘以111195将距离转换为米
FROM
    restaurants
WHERE
    ST_Distance(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)',4326)
    ) * 111195 <= 10000 -- 10公里 = 10000米
ORDER BY
    distance;

解释一下：

ST_Distance(location, ST_GeomFromText('POINT(116.4074 39.9042)',4326))：计算restaurants表中的每个餐馆的location和指定经纬度之间的距离。这个距离的单位是度（°），不是米或者公里。
* 111195：将距离从度转换为米。这个数字是个近似值，因为地球不是完美的球体，不同纬度上的经度一度代表的距离略有不同。更精确的计算需要考虑地球的椭球体形状，但对于一般的应用来说，这个近似值足够了。
WHERE ST_Distance(...) * 111195 <= 10000：筛选出距离小于等于10000米（10公里）的餐馆。
ORDER BY distance：按照距离排序，最近的餐馆排在前面。

六、进阶：使用MBRContains()进行矩形范围查询

如果想查询某个矩形区域内的餐馆，可以使用MBRContains()函数。首先，需要创建一个矩形。可以用ST_MakeEnvelope()函数来创建：

SELECT ST_AsText(ST_MakeEnvelope(116.3, 39.8, 116.5, 40.0, 4326));

这个SQL语句会返回一个表示矩形的WKT（Well-Known Text）字符串：POLYGON((116.3 39.8,116.3 40,116.5 40,116.5 39.8,116.3 39.8))。

ST_MakeEnvelope()函数的参数分别是：左下角的经度、左下角的纬度、右上角的经度、右上角的纬度、SRID。

然后，可以用MBRContains()函数来查询这个矩形区域内的餐馆：

SELECT
    id,
    name,
    address,
    cuisine
FROM
    restaurants
WHERE
    MBRContains(ST_MakeEnvelope(116.3, 39.8, 116.5, 40.0, 4326), location);

七、更高级的玩法：使用ST_Buffer()创建缓冲区

有时候，我们想查询距离某个点一定距离内的所有餐馆，但这个距离不是一个简单的圆形，而是一个不规则的形状。这时候，可以使用ST_Buffer()函数来创建一个缓冲区。

例如，我们可以创建一个以某个点为中心，半径为1公里的缓冲区：

SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(116.4074 39.9042)',4326), 0.008993));

这里，ST_Buffer()函数的第一个参数是几何对象，第二个参数是缓冲区半径，单位是度。半径0.008993度约等于1公里（也是一个近似值）。

然后，可以使用ST_Contains()函数来查询在这个缓冲区内的餐馆（注意，这里用的是ST_Contains()，而不是MBRContains()）：

SELECT
    id,
    name,
    address,
    cuisine
FROM
    restaurants
WHERE
    ST_Contains(ST_Buffer(ST_GeomFromText('POINT(116.4074 39.9042)',4326), 0.008993), location);

八、注意事项和优化

空间索引是关键！ 一定要为空间数据类型的列创建空间索引，否则查询效率会非常低。
选择合适的SRID。 最常用的SRID是4326（WGS 84），但也可以根据实际情况选择其他的SRID。
单位转换要小心。 ST_Distance()函数返回的距离单位是度，需要转换为米或者公里才能使用。
数据类型要一致。 在进行空间计算时，要确保参与计算的数据类型都是空间数据类型，并且SRID一致。
MySQL版本要支持。 MySQL 5.7.21版本之后，空间数据支持更加完善，建议使用较新的版本。
避免在ST_Distance()中使用索引。 直接在WHERE子句中使用ST_Distance()函数，MySQL可能无法使用空间索引。可以先用MBRContains()函数进行粗略的过滤，然后再用ST_Distance()函数进行精确的计算。

举个例子，假设我们要查询距离某个点10公里内的餐馆，可以这样写：

SELECT
    id,
    name,
    address,
    cuisine,
    ST_Distance(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)',4326)
    ) * 111195 AS distance
FROM
    restaurants
WHERE
    MBRContains(ST_MakeEnvelope(116.4074 - 0.09, 39.9042 - 0.09, 116.4074 + 0.09, 39.9042 + 0.09, 4326), location) -- 粗略过滤
AND
    ST_Distance(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)',4326)
    ) * 111195 <= 10000 -- 精确计算
ORDER BY
    distance;

这里，ST_MakeEnvelope()函数创建了一个边长约为20公里的正方形，先用MBRContains()函数筛选出在这个正方形内的餐馆，然后再用ST_Distance()函数计算精确的距离。

九、实战案例：美食App

咱们来模拟一个简单的美食App的场景。假设我们有以下需求：

用户可以搜索附近的美食。
用户可以按照菜系进行筛选。
用户可以按照距离排序。

可以这样设计数据库表：

CREATE TABLE restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location POINT NOT NULL SRID 4326,
    address VARCHAR(255),
    cuisine VARCHAR(255),
    rating DECIMAL(2,1), -- 评分，例如：4.5
    price_range VARCHAR(255), -- 价格范围，例如：'￥50-100'
    SPATIAL INDEX(location)
);

然后，可以编写SQL语句来实现搜索功能：

SELECT
    id,
    name,
    address,
    cuisine,
    rating,
    price_range,
    ST_Distance(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)',4326)
    ) * 111195 AS distance
FROM
    restaurants
WHERE
    MBRContains(ST_MakeEnvelope(116.4074 - 0.09, 39.9042 - 0.09, 116.4074 + 0.09, 39.9042 + 0.09, 4326), location)
AND
    ST_Distance(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)',4326)
    ) * 111195 <= 10000
AND
    cuisine = '北京菜' -- 按照菜系筛选
ORDER BY
    distance;

这个SQL语句可以查询距离指定经纬度10公里内的北京菜餐馆，并按照距离排序。可以根据用户的需求，动态地修改SQL语句中的参数，例如：经纬度、距离、菜系等等。

十、总结

今天咱们聊了MySQL的Spatial Data Types，从基础概念到实际应用，希望能帮助大家掌握这个强大的工具。记住，空间索引是关键，单位转换要小心，多练习才能熟练掌握。

Spatial Data Types的应用场景非常广泛，除了美食App，还可以用于地图应用、物流系统、车辆管理等等。只要涉及到地理位置信息，都可以考虑使用Spatial Data Types来提高查询效率和简化开发流程。

好了，今天的讲座就到这里，感谢大家的观看！希望大家能学有所获，并在实际项目中灵活运用Spatial Data Types。下课！

发表回复 取消回复

发表回复取消回复