使用MySQL处理地理空间特征

发表于： 2025年9月13日 2025年9月13日
分类： mysql
标签： mysql

数据本质上是抽象的。地理空间设计管理为各学科的工程师提供了一种理解复杂数据的工具，使他们能够做出更明智的决策并更好地理解周围世界中的空间关系。在这篇博客文章中，我将探讨如何在MySQL中表示复杂数据和地理特征。
地理空间数据（通常在技术文档中称为地理数据或geodata）包括与地球表面位置相关的信息。在MySQL中，地理特征代表现实世界中具有位置的任何事物，并被定义为实体（Entities）或空间（Space）。

类型分类与定义

实体（Entities）

—— 描述具有定义边界和个体属性的特定对象

示例
- 地标：山脉、河流、森林、建筑物
- 基础设施：道路、桥梁、电力线
- 行政区域：国家、城市、州
- 兴趣点：餐馆、商店、ATM

空间（Spaces）

—— 描述由其位置和特征定义的连续区域

示例
- 地表覆盖：森林、草地、城市区域
- 海拔：地形、山丘、山谷
- 土壤类型：沙土、黏土、壤土
- 环境数据：温度、降水、空气质量

MySQL的空间数据类型

前文介绍了实体和空间的定义，那么如何在MySQL以及其他关系型数据库中表示这些地理特征？这些现实世界中的对象可以通过特定的数据类型和空间函数在数据库中建模。MySQL的地理空间功能核心围绕三个主要的地理对象类型：点、路径和多边形。
在MySQL中，空间数据类型用于在表列中存储几何和地理值。既支持单一几何类型，也支持多几何类型。单一几何类型包括：GEOMETRY、POINT、LINESTRING、POLYGON，而多几何类型表示包含多个同类型对象的集合，包括：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON和GEOMETRYCOLLECTION。

数据类型表

类型	描述	示例
GEOMETRY	存储任何类型的几何值。它是一个不可实例化的类，但具有所有几何值共有的属性。	链接到文档
POINT	存储一个X和Y坐标值。	`POINT(-74.044514 40.689244)`
LINESTRING	存储形成曲线的一组点。即点组成的有序列表，通过边连接。	`LINESTRING(0 0, 0 1, 1 1)`
POLYGON	存储多边形的一组点。类似于线字符串，但是封闭的（至少需要三个唯一点，且首尾点要相同）。	`POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))`；每个环由一组点组成。
MULTIPOINT	存储多个点的集合。	`MULTIPOINT(0 0, 20 20, 60 60)`
MULTILINESTRING	存储多个线字符串的集合。	`MULTILINESTRING((10 10, 20 20), (15 15, 30 15))`
MULTIPOLYGON	存储多个多边形的集合。	`MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))`
GEOMETRYCOLLECTION	存储多个几何对象。不过MySQL不支持空的GeometryCollection，除非是单一的GeometryCollection对象。	`GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))`

支持的空间数据格式

MySQL支持三种主要的格式，用于存储和操作地理空间数据：

1. 著名文本格式（Well-Known Text, WKT）

一种可读文本格式，适用于表示几何对象。
使用关键字如POINT、LINESTRING、POLYGON，后跟坐标和可选元数据。

2. 著名二进制格式（Well-Known Binary, WKB）

一种紧凑的二进制格式。虽然不可读，但易于软件解析，且比WKT具有更高效的存储和传输能力。

3. 内部格式

MySQL内部存储空间数据的格式与WKB类似，但加入了额外的4字节，用于存储空间参考标识符（SRID）。SRID定义几何的坐标系统，确保其解读准确性。

在MySQL中使用地理空间特征

地理空间对象是MySQL中的一种数据类型，可以与数字、字符串和JSON一同使用。

创建带有空间列的表

下面是一个创建包含空间列的表的示例，表名称为locations，列名g可以存储任何几何类型值。我们还为该列定义了SRID属性并明确指定了空间参考系统（SRS）。

CREATE TABLE
    `locations` (
        `id` int NOT NULL,
        `city` varchar(255) NOT NULL,
        `city_ascii` varchar(255) NOT NULL,
        `country` varchar(255) NOT NULL,
        `iso3` varchar(3) NOT NULL,
        `admin_name` varchar(255) NOT NULL,
        `capital` varchar(255) NOT NULL,
        `population` int NOT NULL,
        `g` geometry NOT NULL SRID 4326,
        PRIMARY KEY (`id`),
        SPATIAL KEY `g` (`g`),
        FULLTEXT KEY `city_ascii` (`city_ascii`)
    );

可以使用ALTER TABLE语句向现有表中添加或移除空间列：

ALTER TABLE geom ADD pt POINT;  
ALTER TABLE geom DROP pt;

查询地理空间数据

为了有效处理空间数据，必须了解地理特征的定义和它们在MySQL中的表示方式。这支持你在数据库中高效地存储、检索、分析和可视化地理信息。
MySQL提供了多种函数用于操作和分析地理空间数据，其中包括：

位置函数

提取坐标：
- ST_GeomFromText(wkt_string)：将WKT转换为几何对象
- ST_X(geom)、ST_Y(geom)：提取坐标

距离计算

测量特征间的距离：
- ST_Distance(geom1, geom2)

面积和周长计算

计算多边形的面积和周长：
- ST_Area(geom)

示例：计算两城市之间的距离

以下查询演示通过ST_Distance_Sphere计算两城市之间的大圆距离（球面距离）：

SELECT ST_Distance_Sphere(
            (SELECT g FROM locations WHERE city_ascii = 'Santos'),
            (SELECT g FROM locations WHERE city_ascii = 'Sao Paulo')
        );

基于半径的城市查询

以下示例演示如何通过ST_Distance_Sphere函数找到某个城市一定半径范围内的其他城市。示例查询中，也在结果中列出了城市名称、几何值以及到目标城市的距离：

查询距离纽约(New York)15,000米以内的城市

SELECT
    city,
    ST_AsText(g),
    ST_Distance_Sphere(
        g, (
            SELECT g
            FROM locations
            WHERE city_ascii = 'New York'
        )
    )
FROM locations
WHERE ST_Distance_Sphere(
        g, (
            SELECT g
            FROM locations
            WHERE city_ascii = 'New York'
        )
    ) <= 15000
ORDER BY 3 DESC;

查询距离Santos城市15,000米以内的城市

SELECT
    city,
    ST_AsText(g),
    ST_Distance_Sphere(
        g, (
            SELECT g
            FROM locations
            WHERE city_ascii = 'Santos'
        )
    )
FROM locations
WHERE ST_Distance_Sphere(
        g, (
            SELECT g
            FROM locations
            WHERE city_ascii = 'Santos'
        )
    ) <= 15000
ORDER BY 3 DESC;

其他例子（不基于先前创建的表）

使用地理空间数据进行查询可以实现对特定特征的过滤、分析和合并。以下提供几个实际场景中使用地理空间函数的例子：

1. 找到某点1公里范围内的所有餐厅

SELECT * FROM restaurants
WHERE ST_Distance(
    ST_GeomFromText('POINT(10 20)'), location
) <= 1000;

2. 找到与指定多边形相交的所有公园

SELECT * FROM parks
WHERE ST_Intersects(
    geom,
    ST_GeomFromText('POLYGON((10 20, 20 30, 30 20, 10 20))')
);

3. 计算所有森林的总面积

SELECT SUM(ST_Area(geom)) FROM forests;

对地理空间特性功能的补充说明

MySQL 8 vs. MySQL 5.7地理空间支持比较

MySQL 8在地理空间数据处理上的改进显著，虽然在MySQL 5.7及更早版本中已经可以存储和处理地理特性，但8.0版本新增了更好的支持，包括以下两大优化特点：

1. 空间参考系统(SRS)支持

MySQL 8显著提升了对坐标参考系统（空间参考系统，SRS）的支持。以下为三种类型的空间参考系统：

空间参考系统类型	说明	坐标类型	单位
投影SRS	将球面投影到平面（如地图）。	笛卡尔坐标	米、英尺等距离单位
地理SRS	未投影的球体坐标（椭球体）。	经度-纬度	任意角度单位
默认SRS（SRID 0）	MySQL中的空间数据默认SRID。	无限平面笛卡尔	无单位

在MySQL 8之前，空间参考系统信息仅存储在SRID中，而不能被数据库用于计算，所有操作默认是在平面(SRID为0)上执行。这导致用户需要创建自定义函数来转换单位并进行准确计算，同时需要深入了解数学和几何学。MySQL 8通过引入SRID的计算能力，简化了这些操作，使地理空间数据处理更加精准。

2. 空间索引优化

在MySQL 8中，为空间列启用索引需要满足以下两个要求：

几何列必须定义为NOT NULL。
列必须限制为某一个具体的空间参考系统标识符（SRID），且所有列值必须具有相同的SRID。

在MySQL 8之前，空间关系函数仅使用最小边界矩形（MBR）来计算空间关系，精度受到限制。而8.0版本支持基于实际几何形状的计算，这使得空间操作更加精确，尤其在复杂的空间查询场景中性能显著提升，例如交集、包含和缓冲区计算。

总结

在现代数据库系统中，地理空间数据处理正在变得越来越重要，特别是在需要理解空间关系的场景中，例如地理定位、导航、城市规划、环境监测等领域。通过MySQL提供的多种地理空间数据类型和函数，你可以高效地对地理空间数据进行存储、分析和优化。
MySQL 8进一步增强了空间数据的支持，在性能、精确度和计算能力上都取得了显著提升，使得从简单的地理查询到复杂的空间分析都变得更轻松。
无论是构建具有地理特征的应用程序，还是进行高效的地理数据处理，充分利用MySQL的地理空间功能将是实现数据价值的关键。
如果你希望从零开始体验MySQL的地理空间特性，可以考虑使用PlanetScale服务，它允许你在几秒内启动数据库集群并开始实验。

使用MySQL处理地理空间特征插图

关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台

除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接

本文链接：https://choupangxia.com/2025/09/13/geospatial-features-mysql/

secbro2

程序新视界，一个软实力、硬知识同时提升的平台

1291