MySQL基础14 — 空间数据类型
陸風睿
|
|
|
1,583
|
0
|
文档

5464 字
|
39 分钟
本文最后更新于 297 天前，其中的信息可能已经有所发展或是发生改变。

概述

本章，您将学习 MySQL 中的空间数据类型。

如同 Redis 的 geospatial 数据类型一样（geospatial 是一种使用经纬度标识地理位置的数据类型），MySQL 也有类似存储地理位置信息的数据类型，被称为空间数据类型。

术语说明

  • 地理特征：指地球上任何具有明确位置的事物，可分为以下三类：

    • 实体 - 自然实体（山脉、湖泊等）与人造实体（城市、道路等）
    • 空间 - 例如通过行政区域划分的城镇、通过气候带划分的热带等
    • 可定义位置 - 能够通过特定参照系或描述方式明确标识的空间点位。例如可以通过经纬度来描述地标建筑的位置；以当前道路为参照物，该地标建筑位于道路西北方向的 1 KM 位置处。

  • 几何（Geometry）：表示世界上任何具有地理位置的事物所对应的‌点或点的集合‌。地理要素（geographic feature）、地理空间要素（geospatial feature）、要素（feature）或几何（geometry）都是同义词，其中最常用的术语是‌几何。几何的本意为 "对地球的测量"，另一层含义源于测绘学，指制图师用于绘制世界的几何特征。

  • OGC（Open Geospatial Consortium）：开放地理空间联盟是一个由250多家企业、机构和高校组成的国际性联盟，其主要作用之一是制定开放标准（如 WMS、WFS、WCS等），作用之二是通过成员协作（包括政府、企业、学术机构）解决空间信息集成问题‌。OGC 标准是地理信息系统（GIS）开发的基础规范‌，除此之外，卫星调度、Web 地图服务等场景均依赖 OGC 标准实现数据交换‌。有关 OGC 的内容，请参阅 这里 和 这里。

  • OpenGIS：OGC 制定的一套服务框架的技术统称，关键组成包括：

    • 数据模型（如 ‌SRS‌）
    • 服务接口（如 ‌WMS、WFS、WCS‌）
    • 编码格式（如 GML、KML）

  • SRS（Spatial Reference System，空间参考系统）：OpenGIS 的核心基础组件（如地基一般的重要性），用来描述空间数据的坐标系统和测量基准。在 OpenGIS 中规定：

    • 所有空间数据必须绑定一个 SRS 标识（SRID）
    • 空间计算（如距离/面积）必须基于 SRS 定义的坐标规则执行

  • 笛卡尔 SRS（Cartesian SRS）：指一个没有指定地理坐标系的平面坐标系，常用于局部坐标系或假设的平面地图，如建筑平面图设计、仓库货架定位系统、电路板元件布局、游戏地图坐标

  • 地理 SRS：使用经纬度坐标表示地球表面位置的地理坐标系，常用于城市级地图应用、全球物流跟踪、地理信息系统（GIS）、任何涉及地球曲率的应用

Q：什么是 SRID？

用来唯一标识 SRS 的一种数值编码，其类似于现实世界中各个国家的邮政编码（每个国家的邮政编码规则都不同），不同 SRID 对应不同的坐标系统定义，确保空间数据的准确解析与转换，常有：

  • 4326 - 经纬度
  • 3857 - 网页地图
  • 4490 - 中国北斗导航系统

所有的 SRID 可通过 该网站 进行查询。

提示
EPSG 是国际石油与天然气生产者协会（IOGP）下属的权威组织，负责维护全球坐标参考系统的标准数据集，包括椭球体、投影参数等元数据‌。EPSG 为 SRID 提供权威编码规则，在 OGC 标准中，SRID 通常与 EPSG 代码相对应，比如 SRID 4326 等同于 EPSG:4326。

  • 几何对象：MySQL 在处理空间数据时，需要使用相关的函数（如 ST_GeomFromText( ) 函数）将 WKT 或 WKB 格式的字符串转换为数据库可识别的几何类型。

  • 质心（Centroid）：几何学中的一个重要概念，也称为几何中心或重心。对于均匀材质的物体，质心就是质量的中心点。

空间数据类型

GEOMETRY 是所有空间几何类型的超类（Superclass），可以表示任何几何形状（点、线、面、集合体等），其子类（或称派生类）包括：

  • POINT - 用来表示一个点的坐标，常用来存储经纬度坐标。有关经纬度的知识，请参阅 前面 Redis 的书写内容。
  • LINESTRING - 表示由多个点连接而成的线
  • POLYGON - 表示一个多边形，其由多个点组成的闭区间
  • MULTIPOINT - 多个点的集合
  • MULTILINESTRING - 多条线的集合
  • MULTIPOLYGON - 表示多个多边形的集合
  • GEOMETRYCOLLECTION - 表示多种几何形状的集合（如同时包含点、线、面）。

空间数据类型的格式

两种标准空间数据格式：

  1. WKT（Well-Known Text）格式 - 以 ASCII 格式交换几何数据
  2. WKB（Well-Known Binary）格式 - 以二进制流形式交换几何数据

WKT 格式

  1. 对于 POINT：

    POINT(15 20)

    横坐标在前，纵坐标在后，横坐标与纵坐标使用空格分隔（而不是用逗号）。若定义了 SRID，则 15 表示纬度，20 表示经度。

  2. 对于 LINESTRING：

    LINESTRING(0 0, 10 10, 20 25, 50 60)

    这是由四个点连接而成的线，每个点使用逗号进行分隔。

  3. 对于 POLYGON：

    POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

    在这个示例中，我们表示了一个包含两个环的多边形：

    • 外环多边形 - 由坐标点(0 0)→(10 0)→(10 10)→(0 10)→(0 0)构成的矩形
    • 内环多边形 - 由坐标点(5 5)→(7 5)→(7 7)→(5 7)→(5 5)构成的小矩形

  4. 对于 MULTIPOINT：

    MULTIPOINT(0 0, 20 20, 60 60)

  5. 对于 MULTILINESTRING：

    MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

  6. 对于 MULTIPOLYGON：

    MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

  7. 对于 GEOMETRYCOLLECTION：

    GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

    由两个 POINT 和一个 LINESTRING 组成的几何集合

可使用下面的函数将 WKB 格式转换为 WKT 格式：

SELECT ST_AsText(ST_GeomFromWKB(wkb_data));

WKB 格式

该格式通常需要使用特定函数进行转换，例如将 POINT 转换为几何对象，然后将几何对象转换为 WKB 格式：

SELECT ST_AsBinary(ST_GeomFromText('POINT(15 20)'));

格式对比

WKB WKT
优点 直接内存操作，性能提升 40% 以上；更加节省存储空间，适合大数据量和高频操作；避免了文本转换时的精度损失 可读性强，便于调试与修改；纯文本特性便于传输以及跨系统交换
缺点 人类不可读且难以调试 占用空间比 WKB 大 30%~50%；需要对文本进行解析，处理速度较慢
适用场景 高精度专业领域，如测绘、科学计算等 一般性质的常规应用

创建空间列

使用 create table 语法在创建表时创建空间列（这里没有显式定义 SRID）：

create table if not exists geom(
    g geometry
);
提示
在实际的生产环境下，通常都需要显式定义 SRID，这对于 InnoDB 存储引擎在处理空间数据时会带来极大的好处（在一致性、兼容性以及性能等方面）。
    create table if not exists geom1(
        g1 geometry srid 4326
    );

在表已经存在的情况下使用 alter table ... add ... 语法添加空间列：

alter table geom add pt POINT;

使用 alter table ... drop ... 语法删除表的空间列：

alter table geom drop pt;

填充空间列

我们需要在 insert into 语句中使用特定的函数将几何对象进行插入（注意！直接使用 WKB 或 WKT 格式的字符串进行填充会报错）。有关空间函数的内容，后面会介绍。

众所周知，MySQL 中的变量分为系统变量、用户变量以及特殊的局部变量。系统变量有全局级（global，影响所有的会话）和会话级（session，仅影响当前连接的会话），查询系统变量的值需要用到可选的 @@ 前缀，但修改系统变量的值时不需要 @@ 前缀；用户变量只能作用于当前的连接会话，查询与赋值时需要用到 @ 前缀，断开连接后释放变量；局部变量用在 begin...end 代码块中，必须通过 DECLARE 关键字进行显式声明，然后用 set 关键字进行赋值。

# 在插入数据之前执行转换
set @gdata1:=ST_GeomFromText('point(15 20)');

insert into geom values(@gdata1);

# 查询数据
select ST_AsText(g) as `a` FROM geom;
a
POINT(15 20)

# 也可以不断改变该变量的值然后插入数据
set @gdata1:=ST_GeomFromText('LINESTRING(0 0,1 1,2 2)');
insert into geom values(@gdata1);

set @gdata1:=ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))');
insert into geom values(@gdata1);

set @gdata1:=ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))');
insert into geom values(@gdata1);

# 查询的数据
select ST_AsText(g) as `b` FROM geom;
b
POINT(15 20)
LINESTRING(0 0,1 1,2 2)
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))
  • ST_GeomFromText( ) 函数表示将 WKT 格式的字符串转换为数据库可识别的几何对象。
  • ST_AsText( ) 函数表示将几何对象转换为 WKT 格式

获取空间数据

获取 WKT 格式的空间数据：

select ST_AsText(g) from geom;

获取 WKB 格式的空间数据:

select ST_AsBinary(g) from geom;

优化空间数据

与常规数据类型使用 B-Tree/B+ Tree 数据结构提高查询效率类似，MySQL 对空间列的 SPATIAL 索引采用基于二次分割（quadratic splitting）的 R-Tree 结构实现，相关的创建语法如下：

# 在创建表时创建空间列的 SPATIAL 索引
create table if not exists 表名(
    空间列名 空间数据类型关键字 not null srid 4326,
    spatial index(空间列名)
);

# 在表已经存在空间列的情况下创建 SPATIAL 索引
alter table 表名 add spatial index(空间列名);

使用 SPATIAL 索引请注意以下几点：

  • SPATIAL 索引仅适用于 InnoDB 和 MyISAM 存储引擎的表
  • SPATIAL 索引仅针对单个空间列
  • 相应的空间列 必须 是 not null 约束
  • 不能 应用于有主键约束或唯一约束的列

使用下面的语法查询表中的 SPATIAL 索引：

show index from 表名;

若要删除表中的 SPATIAL 索引，请使用下面的语法：

alter table 表名 drop index 空间列名;

以上面创建的 geom 表为例来演示 SPATIAL 索引：

# 为已经存在的空间列添加 not null 约束
alter table geom modify g geometry not null;

# 应用 SPATIAL 索引
alter table geom add spatial index(g);

# 查看 SPATIAL 索引
## 在输出信息中，index_type 列的值为 SPATIAL
show index from geom;

# 删除表中的 SPATIAL 索引
alter table geom drop index g;

# 再次查阅，无输出信息
show index from geom;

有关索引所涵盖的知识，后面会有专门的文档进行说明

空间函数

所有的空间函数请参阅此链接——https://dev.mysql.com/doc/refman/8.4/en/spatial-function-reference.html

根据不同的功能，可划分为：

  • 一般几何属性函数
  • 点几何属性相关函数
  • 线几何属性相关函数
  • 面几何属性相关函数
  • 空间关系相关函数
  • 几何计算函数
  • 辅助函数

一般几何属性函数

  • ST_Dimension( ) 函数：返回几何对象的拓扑维度，语法为 ST_Dimension(g)，参数 g 表示必选的几何对象（通过 ST_GeomFromText( ) 函数生成），其返回值可以是

    • -1 - 空几何
    • 0 - 没有长度与面积的几何
    • 1 - 长度非零且面积为零的几何
    • 2 - 非零面积的几何

    在 OpenGIS 中规定，POINT 或 MULTIPOINT 的维度为 0，LINESTRING 或 MULTILINETRING 的维度为 1，POLYGON 或 MULTIPOLYGON 的维度为 2

    select ST_Dimension(ST_GeomFromText('linestring(1 1,2 2)')) AS a;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

  • ST_GeometryType( ) 函数：返回几何对象的标准 OGC 名称，用法为 ST_GeometryType(g)

    # 这里只是示例，因为我已经提前输入了 POLYGON 空间数据
select ST_GeometryType(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))')) as b;
+---------+
| b       |
+---------+
| POLYGON |
+---------+
1 row in set (0.00 sec)

  • ST_IsEmpty( ) 函数：判断几何对象是否为空，为空，返回 1，不为空，返回 0

    正确示例：

    select ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION empty')) as c;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

select ST_IsEmpty(ST_GeomFromText('POINT(10 15)')) as c;
+------+
| c    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

    错误示例：

    # 语法错误，MySQL 不支持 POINT EMPTY
select ST_IsEmpty(ST_GeomFromText('POINT EMPTY'));
ERROR 3037 (22023): Invalid GIS data provided to function st_geomfromtext.

# 不能直接向函数传递 WKB 或 WKT 格式的字符串
select ST_IsEmpty('point(10 15)');
ERROR 3037 (22023): Invalid GIS data provided to function st_isempty.

点几何属性相关函数

对于笛卡尔 SRS，则可以使用以下函数

  • ST_X( ) 函数 - 提取点几何的横坐标，用法为 ST_X(p)，p 表示 POINT 几何对象

    select ST_X(ST_GeomFromText('point(100 200)')) as x;
+------+
| x    |
+------+
|  100 |
+------+
1 row in set (0.05 sec)

  • ST_y( ) 函数 - 提取点几何的纵坐标，用法为 ST_Y(p)

对于地理 SRS，则可以使用以下函数：

  • ST_Latitude( ) 函数 - 提取点几何的经度，用法为 ST_Latitude(p)

    select ST_Latitude(ST_GeomFromText('POINT(45 90)', 4326)) as a;
+------+
| a    |
+------+
|   45 |
+------+
1 row in set (0.00 sec)
  • ST_Longitude( ) 函数 - 提取点几何的纬度，用法为 ST_Longitude(p)

线几何属性相关函数

函数参数中的 ls 表示 LINESTRING 几何对象，部分函数也支持 MULTILINESTRING 几何对象

  • ST_StartPoint( ) 函数 - 返回线的起始点，用法为 ST_StartPoint(ls)

  • ST_EndPoint( ) 函数 - 返回线的最后一个点，用法为 ST_EndPoint(ls)

    select ST_AsText( ST_EndPoint(ST_GeomFromText('LINESTRING(0 0, 10 10, 20 25, 50 60)')) ) as a;
+--------------+
| a            |
+--------------+
| POINT(50 60) |
+--------------+
1 row in set (0.03 sec)

  • ST_IsClosed( ) 函数 - 判断 LineString 是否闭合（闭合指的是线的起始点相同）。若闭合则返回 1 ，若未闭合则返回 0 

    set @ls1:='LINESTRING(1 1,2 2,3 3,2 2)';
set @ls2:='LineString(1 1,2 2,3 3,1 1)';

select ST_IsClosed(ST_GeomFromText(@ls1));
+------------------------------------+
| ST_IsClosed(ST_GeomFromText(@ls1)) |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.02 sec)

select ST_IsClosed(ST_GeomFromText(@ls2));
+------------------------------------+
| ST_IsClosed(ST_GeomFromText(@ls2)) |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

  • ST_Length( ) 函数 - 计算 LineString 或 MultiLineString 的总长度，用法为 ST_Length(ls [, unit])

    • 如果几何对象是笛卡尔 SRS （Cartesian SRS）中的有效 LineString，则返回值是几何体的笛卡尔长度
    • 如果几何对象是笛卡尔 SRS 中的有效 MultiLineString，则返回值是其元素的笛卡尔长度之和
    • 如果几何对象是地理 SRS（geographic SRS） 中的有效 LineString，则返回值是该 SRS 中几何对象的大地测量长度，单位为米（metre）
    • 如果几何对象是地理 SRS 中的有效 MultiLineString，则返回值是该 SRS 中其元素的大地测量长度之和，单位为米（metre）
    • 未指定 unit 单位，笛卡尔 SRS 和地理 SRS 都已米为单位。支持的单位在 information_schema.ST_UNITS_OF_MEASURE 表中有记录
    select * from information_schema.st_units_of_measure where unit_name='metre';
+-----------+-----------+-------------------+-------------+
| UNIT_NAME | UNIT_TYPE | CONVERSION_FACTOR | DESCRIPTION |
+-----------+-----------+-------------------+-------------+
| metre     | LINEAR    |                 1 |             |
+-----------+-----------+-------------------+-------------+
1 row in set (0.00 sec)
    # 笛卡尔 SRS 示例
set @ls:=ST_GeomFromText('LineString(1 1,2 2,3 3)');
select ST_Length(@ls);
+--------------------+
| ST_Length(@ls)     |
+--------------------+
| 2.8284271247461903 |
+--------------------+
1 row in set (0.06 sec)

SET @mls:=ST_GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))');
SELECT ST_Length(@mls);
+-------------------+
| ST_Length(@mls)   |
+-------------------+
| 4.242640687119286 |
+-------------------+
1 row in set (0.00 sec)

# 地理 SRS 示例，使用英尺单位
set @ls:=ST_GeomFromText('LineString(1 1,2 2,3 3)', 4326);
select ST_Length(@ls,'foot');
+-----------------------+
| ST_Length(@ls,'foot') |
+-----------------------+
|    1029205.9131247795 |
+-----------------------+
1 row in set (0.00 sec)

  • ST_NumPoints( ) 函数 - 显示 LineString 中 Point 的数量，用法为 ST_NumPoints(ls)

    set @num:=ST_GeomFromText('LineString(1 1,5 5, 10 10 ,1 1)');
select ST_NumPoints(@num);
+--------------------+
| ST_NumPoints(@num) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.00 sec)

  • ST_PointN( ) 函数 - 返回 LineString 中指定位置的点几何，LineString 中的点几何位置从 1 开始记，用法为 ST_PointN(ls, N)

    set @pointn:=ST_GeomFromText('LineString(1 1,5 5, 10 10 ,1 1)');
select ST_AsText(ST_PointN(@pointn,1));
+---------------------------------+
| ST_AsText(ST_PointN(@pointn,1)) |
+---------------------------------+
| POINT(1 1)                      |
+---------------------------------+
1 row in set (0.00 sec)

面几何属性相关函数

  • ST_Area( ) 函数 - 返回 Polygon 或 MultiPolygon 参数在其 SRS 中测量的面积，用法为 ST_Area({poly|mpoly})

    • 如果几何对象是笛卡尔 SRS 中的有效 Polygon，则函数返回 Polygon 的面积
    • 如果几何对象是笛卡尔 SRS 中的有效 MultiPolygon，则函数返回 MultiPolygon 的面积之和
    • 如果几何对象是地理 SRS 中的有效 Polygon，则函数返回该 SRS 中多边形的大地测算面积（以平方米为单位）
    • 如果几何对象是地理 SRS 中的有效 MultiPolygon，则函数返回该 SRS 中多边形的大地测算面积之和（以平方米为单位）

    笛卡尔 SRS 示例：

    # 外环三角形的面积为 4.5，内环三角形的面积为 0.5，4.5 减去 0.5 等到 4
SET @poly:=ST_GeomFromText('Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))');
select ST_Area(@poly);
+----------------+
| ST_Area(@poly) |
+----------------+
|              4 |
+----------------+
1 row in set (0.03 sec)

# 外环矩形的面积为 9 ，内环矩形的面积为 1，9 减去 1 得到 8
set @mpoly:=ST_GeomFromText('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))');
select ST_Area(@mpoly);
+-----------------+
| ST_Area(@mpoly) |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.00 sec)

    地理 SRS 示例：

    # 由于以度为单位，因此函数返回的结果是一个近似值而非精准值
select ST_Area(
    ST_GeomFromText('POLYGON((0 0, 0 3, 3 0, 0 0),(1 1, 1 2, 2 1, 1 1))', 4326)
) AS geographic_area;
+--------------------+
| geographic_area    |
+--------------------+
| 49262684495.757416 |
+--------------------+
1 row in set (0.00 sec)

  • ST_Centroid( ) 函数 - 以 Point 形式返回 Polygon 或 MultiPolygon 的质心（中心），用法为 ST_Centroid({poly|mpoly})

    set @poly:=ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))');
select ST_GeometryType(@poly), ST_AsText(ST_Centroid(@poly));
+------------------------+--------------------------------------------+
| ST_GeometryType(@poly) | ST_AsText(ST_Centroid(@poly))              |
+------------------------+--------------------------------------------+
| POLYGON                | POINT(4.958333333333333 4.958333333333333) |
+------------------------+--------------------------------------------+
1 row in set (0.03 sec)

    请注意该函数的使用限制——如果几何对象使用了地理 SRS，执行该函数后会抛出 ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS 错误。换言之，MySQL 8.4 版本中的该函数不直接支持对地理 SRS 的质心计算。

  • ST_ExteriorRing( ) 函数 - 以 LineString 形式返回 Polygon 的外环，用法为 ST_ExteriorRing(poly)

    set @poly:=ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))');
select ST_AsText(ST_ExteriorRing(@poly));
+-------------------------------------+
| ST_AsText(ST_ExteriorRing(@poly))   |
+-------------------------------------+
| LINESTRING(0 0,10 0,10 10,0 10,0 0) |
+-------------------------------------+
1 row in set (0.00 sec)

  • ST_InteriorRingN( ) 函数 - 以 LineString 形式返回第 N 个内环，用法为 ST_InteriorRingN(poly, N)。按照从外环到内环的顺序，环的值从 1 开始记。

    set @poly:=ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))');
select ST_AsText(ST_InteriorRingN(@poly,1));
+--------------------------------------+
| ST_AsText(ST_InteriorRingN(@poly,1)) |
+--------------------------------------+
| LINESTRING(1 1,1 2,2 2,2 1,1 1)      |
+--------------------------------------+
1 row in set (0.00 sec)

  • ST_NumInteriorRing( ) 函数 - 返回 Polygon 的内环数，用法为 ST_NumInteriorRing(poly)

    set @poly:=ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))');
select ST_NumInteriorRing(@poly);
+---------------------------+
| ST_NumInteriorRing(@poly) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

空间关系相关函数

  • ST_Contains( ) 函数 - 返回 1 或 0 来判断是两个几何对象否存在完全包含关系，用法为 ST_Contains(g1,g2)，若存在完全包含关系（g1 完全包含 g2，g1 和 g2 永远不相交），返回 1

    set @g1:=ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), 
@p1:=ST_GeomFromText('Point(1 1)'),
@p2:=ST_GeomFromText('Point(3 3)'),
@p3:=ST_GeomFromText('Point(5 5)');

select ST_Contains(@g1, @p1) as a, 
ST_Within(@p1, @g1) as b,
ST_Disjoint(@g1, @p1) as c,
ST_Intersects(@g1, @p1) as d;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |    1 |    0 |    1 |
+------+------+------+------+
1 row in set (0.00 sec)

  • ST_Within( ) 函数 - 返回 0 或 1 来判断两个几何对象是否存在完全包含关系，用法为 ST_Within(g1,g2)，若 g2 完全包含 g1，则返回 1

  • ST_Crosses( ) 函数 - 返回 0 或 1 来判断两个几何对象在空间上是否存在交叉关系，用法为 ST_Crosses(g1,g2)。有效交叉关系包括：

    • 线几何与面几何
    • 线几何与线几何

    点几何与线几何交叉会返回 0，面几何与点几何交叉会返回 NULL

  • ST_Equals( ) 函数 - 返回 0 或 1 来判断两个几何对象在空间上是否相等，用法为 ST_Equals(g1,g2)，若 g1 等于 g2，则返回 1

  • ST_Intersects( ) 函数 - 返回 0 或 1 来判断两个几何对象在空间上是否存在相交关系，用法为 ST_Intersects(g1,g2)。有效相交关系包括：

    • 存在内部区域重叠（如两个多边形部分重叠）
    • 边界接触（如点在多边形边线上，线与多边形边界重叠）
    • 包含（一个几何对象完全在另外一个几何对象的内部）
    • 点与点重合（坐标相同）
    • 点落在线上
    • 点位于面的内部或边界
    • 线与面相交
    • 线与线交叉或部分重叠
    • 面与面边界接触或部分重叠

  • ST_Disjoint( ) 函数 - 返回 1 或 0 来判断两个几何对象在空间上是否不存在相交关系，用法为 ST_Disjoint(g1,g2)，若不存在相交关系，则返回 1

  • ST_Distance( ) 函数 - 计算两个几何对象之间的距离，用法为 ST_Distance(g1, g2 [, unit])

    # 笛卡尔 SRS
set @g1:=ST_GeomFromText('POINT(116.407396 39.904211)'), @g2:=ST_GeomFromText('POINT(121.473701 31.230416)');
select ST_Distance(@g1,@g2);
+----------------------+
| ST_Distance(@g1,@g2) |
+----------------------+
|   10.045007021154834 |
+----------------------+
1 row in set (0.04 sec)

# 地理 SRS
## 请注意！由于关联了 4326 这个 SRID，因此在 ST_GeomFromText 函数中默认必须纬度在前，经度在后，但可以通过该函数的 option 调整经纬度的顺序，如下：
## 以米为单位计算两个点几何的距离
set @g1:=ST_GeomFromText('POINT(116.407396 39.904211)',4326,'axis-order=long-lat'),@g2:=ST_GeomFromText('POINT(121.473701 31.230416)',4326,'axis-order=long-lat');
select ST_Distance(@g1,@g2,'metre');
+----------------------+-------+
| ST_Distance(@g1,@g2) | metre |
+----------------------+-------+
|   1065851.1029469664 | metre |
+----------------------+-------+
1 row in set (0.05 sec)

  • ST_Overlaps( ) 函数 - 用 0 或 1 来判断两个几何对象是否存在部分重叠但不完全包含的关系，主要应用于面几何和面几何，用法为 ST_Overlaps(g1, g2)

    set @g1:=ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), @g2:=ST_GeomFromText('POLYGON((3 3, 3 8, 8 8, 8 3, 3 3))');
SELECT ST_Overlaps(@g1, @g2);
+-----------------------+
| ST_Overlaps(@g1, @g2) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

  • ST_Touches( ) 函数 - 用 0 或 1 来判断两个几何对象是否存在仅边界接触但无重叠部分的关系，用法为 ST_Touches(g1, g2)

    set @g1:=ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), @g2:=ST_GeomFromText('POLYGON((5 0, 5 5, 10 5, 10 0, 5 0))');
select ST_Touches(@g1,@g2);
+---------------------+
| ST_Touches(@g1,@g2) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

几何计算函数

  • ST_Buffer( ) 函数 - 根据距离创建几何对象的缓冲区，用法为 ST_Buffer(g, d [, strategy1 [, strategy2 [, strategy3]]])，g 表示几何对象，d 表示以米为单位的距离（不能是负数），后面的参数表示创建缓冲区时最多三个可选策略（策略需要使用 ST_Buffer_Strategy( ) 函数进行生成）

    set @pt:=ST_GeomFromText('POINT(0 0)');
SET @pt_strategy:=ST_Buffer_Strategy('point_square');
select ST_AsText(ST_Buffer(@pt, 2, @pt_strategy));
+--------------------------------------------+
| ST_AsText(ST_Buffer(@pt, 2, @pt_strategy)) |
+--------------------------------------------+
| POLYGON((-2 -2,2 -2,2 2,-2 2,-2 -2))       |
+--------------------------------------------+
1 row in set (0.00 sec)

  • ST_Buffer_Strategy( ) 函数 - 返回策略的字符串，用法为 ST_Buffer_Strategy(strategy [, points_per_circle])

    第一个参数必须是：

    • 对于 point 策略，允许的值有 'point_circle' 和 'point_square'
    • 对于 join 策略，允许的值有 'join_round' 和 'join_miter'.
    • 对于 end 策略，允许的值有 'end_round' 和 'end_flat'

    若第一个参数是 'point_circle'、 'join_round'、 'join_miter' 或 'end_round'，则 points_per_circle 参数必须是正数值。points_per_circle 参数的最大值即系统变量 max_points_in_geometry 的值。

  • ST_Difference( ) 函数 - 以几何对象方式返回两个几何对象的差集，用法为 ST_Difference(g1, g2)，函数返回 g1 减去 g1 与 g2 的交集部分

    select ST_AsText(ST_Difference(
ST_GeomFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))'),
ST_GeomFromText('POLYGON((1 1,1 4,4 4,4 1,1 1))')
)) as diff;
+------------------------------------------------------+
| diff                                                 |
+------------------------------------------------------+
| POLYGON((0 0,5 0,5 5,0 5,0 0),(1 1,1 4,4 4,4 1,1 1)) |
+------------------------------------------------------+
1 row in set (0.00 sec)

  • ST_Intersection( ) 函数 - 以几何对象方式返回两个几何对象的交集，用法为 ST_Intersection(g1, g2)

    set @g1:=ST_GeomFromText('LineString(1 1, 3 3)'), @g2:=ST_GeomFromText('LineString(1 3, 3 1)');
select ST_AsText(ST_Intersection(@g1, @g2)) as a;
+------------+
| a          |
+------------+
| POINT(2 2) |
+------------+
1 row in set (0.00 sec)

  • ST_Union( ) 函数 - 以几何对象方式返回两个几何对象的并集，用法为 ST_Union(g1, g2)

    set @g1:=ST_GeomFromText('LineString(1 1, 3 3)'), @g2:=ST_GeomFromText('LineString(1 3, 3 1)');
select ST_AsText(ST_Union(@g1,@g2)) as b;
+--------------------------------------+
| b                                    |
+--------------------------------------+
| MULTILINESTRING((1 1,3 3),(1 3,3 1)) |
+--------------------------------------+
1 row in set (0.00 sec)

辅助函数

  • ST_GeomFromText( ) 函数 - 将 WKT 格式的字符串转换为数据库可识别的几何对象，用法为 ST_GeomFromText(wkt [, srid [, options]])。前面说过了，对于地理 SRS，若输入的是 WKT 格式的点几何，则默认是纬度在前，经度在后，可通过 option 调整顺序

    # 正确语法
set @g1:=ST_GeomFromText('POINT(116.407396 39.904211)',4326,'axis-order=long-lat');
set @g2:=ST_GeomFromText('POINT(121.473701 31.230416)',4326,'axis-order=long-lat');

# 错误语法
set @g1:=ST_GeomFromText('POINT(116.407396 39.904211)',4326);
ERROR 3617 (22S03): Latitude 116.407396 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

  • ST_GeomFromWKB( ) 函数 - 将 WKB 格式的字符串转换为数据库可识别的几何对象，用法为 ST_GeomFromWKB(wkb [, srid [, options]])

  • ST_AsText( ) 函数或 ST_AsWKT( ) 函数 - 将几何对象转换为 WKT 格式，用法为 ST_AsText(g [, options])

  • ST_AsWKB( ) 函数或 ST_AsBinary( ) 函数 - 将几何对象转换为 WKB 格式，用法为 ST_AsWKB(g [, options])

  • ST_SRID( ) 函数 - 返回几何对象的有效 SRID

    set @g:=ST_GeomFromText('LineString(1 1,2 2)', 0);
select ST_SRID(@g);
+-------------+
| ST_SRID(@g) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

  • ST_Transform( ) 函数 - 将几何对象从当前的 SRID 转换到另外一个 SRID，用法为 ST_Transform(g, target_srid)，target_srid 支持 EPSG 的所有 SRID，但是以下除外：

    • EPSG:1042
    • EPSG:1043
    • EPSG:9816
    • EPSG:9826

    例子如下：

    set @p:=ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
select ST_AsText(@p);
+----------------------------+
| ST_AsText(@p)              |
+----------------------------+
| POINT(52.381389 13.064444) |
+----------------------------+
1 row in set (0.00 sec)

set @p:=ST_Transform(@p, 4230);
select ST_AsText(@p);
+---------------------------------------------+
| ST_AsText(@p)                               |
+---------------------------------------------+
| POINT(52.38208611407426 13.065520672345304) |
+---------------------------------------------+
1 row in set (0.00 sec)

  • ST_Validate( ) 函数 - 验证几何对象是否合法有效，用法为 ST_Validate(g)。如果几何对象有效，则原样返回，否则返回 NULL 或者输出错误提示

    set @ls1:=ST_GeomFromText('LINESTRING(0 0, 1 1)');

select ST_AsText(ST_Validate(@ls1));
+------------------------------+
| ST_AsText(ST_Validate(@ls1)) |
+------------------------------+
| LINESTRING(0 0,1 1)          |
+------------------------------+
1 row in set (0.00 sec)

注意点

  • 对于笛卡尔 SRS，无论是点几何、线几何、面几何以及几何形状集合，在书写表示和输出时，其都是横坐标在前且纵坐标在后
  • 对于地理 SRS，由于需要符合 EPSG 的标准，无论是点几何、线几何、面几何以及几何形状集合，在书写表示和输出时，其都是纬度在前且经度在后
Avatar photo

关于 陸風睿

GNU/Linux 从业者、开源爱好者、技术钻研者，撰写文档既是兴趣也是工作内容之一。Q - "281957576"；WeChat - "jiulongxiaotianci"，Github - https://github.com/jimcat8
查看 陸風睿 所有文章 | 网站
版权声明:「自由转载-保持署名-非商业性使用-禁止演绎 3.0 国际」（CC BY-NC-ND 3.0）
用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。
MySQL8.4
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
在 Proxmox 非特权 LXC 容器中配置 Tailscale：子网路由与出口节点

							
							

							
在 Debian 13 上部署 Samba AD 域控制器：DNS、Kerberos 与组策略

							
							

							
Qdrant 入门系列02 – 在 Ubuntu 26.04、24.04 和 22.04 上安装 Qdrant

							
							

							
Qdrant 入门系列01 – 向量数据库：架构、部署与生产使用入门

							
							

							
gVisor、Kata、runc 与虚拟机：容器隔离性能与部署对比

							
							

							
用 Ansible 准确检查 Linux 软件包是否已安装

							
							

							
Linux 终端模拟器实测：15 款终端的速度、内存和适用场景

							
							

							
安装 Ubuntu 26.04 LTS 桌面后需要完成的设置

							
							

							
Rsync基础篇02—基础功能演示

							
							

							
Rsync基础篇01—简述