登录 注册
开源 企业版 高校版 私有云 Gitee AI NEW
扫描微信二维码支付
取消
支付完成
Watch
不关注 关注所有动态 仅关注版本发行动态 关注但不提醒动态
6 Star 72 Fork 28

JustryDeng / notebook

代码 Issues 0 Pull Requests 0 Wiki 统计 流水线
服务
加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目，私有仓库也完全免费 ：）
免费加入
克隆/下载
[17]MySQL空间函数之点、线、面、几何.md 18.03 KB
一键复制 编辑 原始数据 按行查看 历史
JustryDeng 提交于 2023-03-31 11:33 . MySQL空间函数之点、线、面、几何

MySQL空间函数之点、线、面、几何

点、线、面、几何

声明：本文中涉及到的测试sql是基于mysql-8.0.18版本进行的

提示

  • 坐标经纬度可去百度地图坐标拾取系统查询
  • 本文只介绍常用的功能，更多功能详见MySQL空间函数
  • MySQL提供的点、线、面等空间几何能力常用来处理经纬度相关数据，但是并不意味着只能处理经纬度，实际上正如名字一样，任何点线面等几何都可以处理

创建点

更多见here

语法

-- Point(经度, 纬度)
Point(x, y)

示例：

select Point(104.048599, 30.668247) AS '成都文化公园的位置'

创建线

更多见here

语法

LineString(pt [, pt] ...)

注：多个点，可以不在同一个直线上

示例：

select LineString(
    Point(104.048599, 30.668247), 
    Point(103.957762, 30.566805), 
    Point(104.137136, 30.771566)
    ) AS '成都文化公园 - 成都双流机场 - 成都植物园'

创建面

提示

  • 创建Polygon时，所使用的的LineString必须是闭合的线，否则会报错（有的mysql版本不会报错，但是会返回null）
  • 更多见here

语法

Polygon(ls [, ls] ...)

示例：

select Polygon(
				LineString(
						Point(104.050323,30.66775), 
						Point(104.146334,30.635936),
						Point(104.006055,30.562825), 
						Point(104.050323,30.66775)
				)
		) AS '成都文化公园 -> 成都东站 -> 四川大学江安校区 -> 成都文化公园 围成的多边形区域'
select Polygon(
				LineString(
						Point(104.050323,30.66775), 
						Point(104.146334,30.635936),
						Point(104.006055,30.562825), 
						Point(104.050323,30.66775)
				),
				LineString(
						Point(103.912344,30.646873), 
						Point(103.828406,30.730351),
						Point(103.962936,30.670235), 
						Point(103.912344,30.646873)
				)
		) AS '成都文化公园 -> 成都东站 -> 四川大学江安校区 -> 成都文化公园,围成的多边形区域
		      + 
		      四川爱华学院 -> 国色天香陆地公园 -> FF体育公园 -> 四川爱华学院,围成的多边形区域
		     '

几何数据类型

  • point类型的数据列：只能存Point数据

  • linestring类型的数据列：只能存LineString数据

  • polygon类型的数据列：只能存Polygon数据

  • geometry类型的数据列：能存所有几何类型的数据，包括不限于point类型、linestring类型、polygon类型、multipoint类型、multilinestring类型、multipolygon类型等

    示例说明：

    • 假设我们的表结构是这样的：

      image-20230330102421602

    • 那么凡是几何类型的数据，都可以往这列放，如：

      image-20230330102500903

常用几何函数

更多函数详见MySQL空间函数

ST_GeoHash

计算一个点的地理hash值

  • 语法说明

    -- 参数：经度、纬度、最大hash长度
ST_GeoHash(longitude, latitude, max_length)

-- 参数：Point、最大hash长度
ST_GeoHash(point, max_length)

  • 示例

    mysql> SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
+----------------------+-------------------------+
| ST_GeoHash(180,0,10) | ST_GeoHash(-180,-90,15) |
+----------------------+-------------------------+
| xbpbpbpbpb           | 000000000000000         |
+----------------------+-------------------------+

ST_LatFromGeoHash

从地理hash值中提取纬度

  • 语法说明

    ST_LatFromGeoHash(geohash_str)

  • 示例

    mysql> SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
+------------------------------------------+
| ST_LatFromGeoHash(ST_GeoHash(45,-20,10)) |
+------------------------------------------+
|                                      -20 |
+------------------------------------------+

ST_LongFromGeoHash

从地理hash值中提取经度

  • 语法说明

    ST_LongFromGeoHash(geohash_str)

  • 示例

    mysql> SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));
+-------------------------------------------+
| ST_LongFromGeoHash(ST_GeoHash(45,-20,10)) |
+-------------------------------------------+
|                                        45 |
+-------------------------------------------+

ST_PointFromText

使用文本构造点Point

  • 语法说明

    ST_PointFromText(wkt [, srid [, options]])

  • 示例

    mysql> SELECT ST_PointFromText('point(1 2)');
+--------------------------------+
| ST_PointFromText('point(1 2)') |
+--------------------------------+
| POINT(1 2)                     |
+--------------------------------+

ST_LineFromText

使用文本构造线LINESTRING

  • 语法说明

    ST_LineFromText(wkt [, srid [, options]])
-- 或
ST_LineStringFromText(wkt [, srid [, options]])

  • 示例

    mysql> SELECT ST_LineFromText ( 'linestring(1 2, 110 20)' );
+-----------------------------------------------+
| ST_LineFromText ( 'linestring(1 2, 110 20)' ) |
+-----------------------------------------------+
| LINESTRING(1 2, 110 20)                       |
+-----------------------------------------------+

ST_PolyFromText

使用文本构造面LINESTRING

  • 语法说明

    ST_PolyFromText(wkt [, srid [, options]])
-- 或
ST_PolygonFromText(wkt [, srid [, options]])

  • 示例

    mysql> SELECT ST_PolyFromText ( 'polygon((1 2, 110 20, 130 50, 1 2), (92 50, 120 2, 3 10, 92 50))' );
+----------------------------------------------------------------------------------------+
| ST_PolyFromText ( 'polygon((1 2, 110 20, 130 50, 1 2), (92 50, 120 2, 3 10, 92 50))' ) |
+----------------------------------------------------------------------------------------+
| POLYGON((1 2, 110 20, 130 50, 1 2), (92 50, 120 2, 3 10, 92 50))                       |
+----------------------------------------------------------------------------------------+

ST_GeometryFromText

使用文本构造（点、线、面等）几何

  • 语法说明

    ST_GeomFromText(wkt [, srid [, options]])
-- 或
ST_GeometryFromText(wkt [, srid [, options]])

  • 示例

    SELECT ST_PolyFromText ( 'polygon((1 2, 110 20, 130 50, 1 2), (92 50, 120 2, 3 10, 92 50))' );
-- 等价于
SELECT ST_GeomFromText ( 'polygon((1 2, 110 20, 130 50, 1 2), (92 50, 120 2, 3 10, 92 50))' );

SELECT ST_LineFromText ( 'linestring(1 2, 110 20)' );
-- 等价于
SELECT ST_GeomFromText ( 'linestring(1 2, 110 20)' );

SELECT ST_PointFromText('point(1 2)');
-- 等价于
SELECT ST_GeomFromText('point(1 2)');

ST_GeomFromGeoJSON

使用json构造（点、线、面等）几何

注：如果json为null，则解析结果也为null

注：此函数对json中的内容，大小写敏感

  • 语法说明

    ST_GeomFromGeoJSON(str [, options [, srid]]

  • 示例

    mysql> SET @json = '{ "type": "Point", "coordinates": [102.0, 0.0]}';
mysql> SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
+--------------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@json)) |
+--------------------------------------+
| POINT(0 102)                         |
+--------------------------------------+

ST_AsText

将几何的值转换为字符串的形式

  • 语法说明

    ST_AsText(g [, options])
-- 或
ST_AsWKT(g [, options])

  • 示例

    mysql> set @pointA = Point(56.7, 53.34);
mysql> SELECT ST_AsText(@pointA);
+------------------------------------+
| ST_PolyFromText ST_AsText(@pointA) |
+------------------------------------+
| POINT(56.7 53.34)                  |
+------------------------------------+

ST_X与ST_Y

  • 语法说明

    -- 查询point x的值
ST_X(p)
-- 查询point，y值正常返回，但是x值返回为new_x_val
ST_X(p , new_x_val)

-- 查询point y的值
ST_Y(p)
-- 查询point，x值正常返回，但是y值返回为new_y_val
ST_Y(p , new_y_val)

  • 示例

    -- 这些sql按顺序执行， 通过观察一下结果可知其功能
set @pointA = Point(56.7, 53.34);
-- 查询结果为：56.7
SELECT ST_X(@pointA);
-- 查询结果为：53.34
SELECT ST_Y(@pointA);
-- 查询结果为：POINT(56.7 53.34)
SELECT ST_AsText(@pointA);
-- 查询结果为：POINT(1 53.34)
SELECT ST_X(@pointA, 1);
-- 查询结果为：POINT(56.7 2)
SELECT ST_Y(@pointA, 2);
-- 查询结果为：POINT(56.7 53.34)
SELECT ST_AsText(@pointA);

ST_Distance_Sphere

返回球体上两个几何之间的最小球面距离（单位为米）

  • 语法说明

    ST_Distance_Sphere(g1, g2 [, radius])

    radius：指定半径大小（单位为米）。如果省略，则默认为地球半径（6370986米），geo中的point的点X和点Y坐标分别解释为经度和纬度，单位为度

  • 示例

    SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(180 0)');
—— 不设置半径，则默认按照经纬度计算最小球面距离
SELECT ST_Distance_Sphere(@pt1, @pt2)

SELECT ST_Distance_Sphere(@pt1, @pt2, 100)

ST_Distance

返回两个几何之间的最小距离

  • 语法说明

    ST_Distance(g1, g2)
-- 从MySQL 8.0.14开始，允许一个可选的单位参数，该参数指定返回距离值的线性单位。 metre-米(默认即为米) foot-步
ST_Distance(g1, g2 [, unit])

    unit：单位，默认为米。注：从MySQL 8.0.14开始，允许一个可选的单位参数，该参数指定返回距离值的线性单位）

  • 示例

    mysql> SET @g1 = ST_GeomFromText('POINT(1 1)');
mysql> SET @g2 = ST_GeomFromText('POINT(2 2)');
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
|    1.4142135623730951 |
+-----------------------+

mysql> SET @g1 = ST_GeomFromText('POINT(1 1)', 4326);
mysql> SET @g2 = ST_GeomFromText('POINT(2 2)', 4326);
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
|     156874.3859490455 |
+-----------------------+
mysql> SELECT ST_Distance(@g1, @g2, 'metre');
+--------------------------------+
| ST_Distance(@g1, @g2, 'metre') |
+--------------------------------+
|              156874.3859490455 |
+--------------------------------+
mysql> SELECT ST_Distance(@g1, @g2, 'foot');
+-------------------------------+
| ST_Distance(@g1, @g2, 'foot') |
+-------------------------------+
|             514679.7439273146 |
+-------------------------------+

使用示例

  • 创建表并初始化测试数据

    -- 为方便，这里本人设置home_geo_hash列的值自动根据home_geo计算更新
CREATE TABLE `user_info`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) NULL DEFAULT NULL COMMENT '姓名',
  `home_geo` geometry NULL COMMENT '家庭地址地理位置',
  `home_geo_hash` varchar(12) GENERATED ALWAYS AS (if((`home_geo` is null),NULL,st_geohash(`home_geo`,12))) VIRTUAL COMMENT 'home_geo的geohash' NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `home_geo_hash_idx`(`home_geo_hash` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;


INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('张三', ST_GeomFromText('POINT(104.048599 30.668247)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('李四', ST_GeomFromText('POINT(104.058059 30.636433)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('王五', ST_GeomFromText('POINT(104.12778 30.564318)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('赵六', ST_GeomFromText('POINT(104.038059 30.585212)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('孙七', ST_GeomFromText('POINT(103.960062 30.5678)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('周八', ST_GeomFromText('POINT(103.689276 30.618037)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('吴九', ST_GeomFromText('POINT(104.065968 30.746244)'));
INSERT INTO `user_info` (`name`, `home_geo`)  VALUES ('郑十', ST_GeomFromText('POINT(104.234871 30.528986)'));

    得到的表数据如下：

    id name home_geo home_geo_hash
    1 张三 POINT(104.048599 30.668247) wm3yrwvzkpde
    2 李四 POINT(104.058059 30.636433) wm3yrchy0t09
    3 王五 POINT(104.12778 30.564318) wm6jckpd11u6
    4 赵六 POINT(104.038059 30.585212) wm6jczy7c8v2
    5 孙七 POINT(103.960062 30.5678) wm3vvstqm4bt
    6 周八 POINT(103.689276 30.618037) wm3wptb4zrb0
    7 吴九 POINT(104.065968 30.746244) wm6nbj4tbg3c
    8 郑十 POINT(104.234871 30.528986) wm6jevts0x3p

  • 使用测试

    • 查询张三李四的距离

      SELECT
	ST_Distance_Sphere ( ( SELECT home_geo FROM user_info WHERE `name` = '张三' ), home_geo ) AS '距离（米）' 
FROM
	user_info 
WHERE
	`name` = '李四';
      +---------------------+
| 距离（米）            |
+---------------------+
|   10150.54382852189 |
+---------------------+

    • 查询张三附近12000米以内的人

      set @zhangsanGeo = ( SELECT home_geo FROM user_info WHERE `name` = '张三');
	
-- 查询张三附近12000米以内的人
SELECT
	`name`
FROM
	user_info 
where ST_Distance_Sphere(@zhangsanGeo, home_geo) < 12000 and `name` != '张三';
      +---------+
| name   |
+--------+
| 李四    |
+--------+
| 赵六    |
+--------+
| 吴九    |
+--------+

    • 查询张三附近12000米以内的人（使用geo_hash优化查询范围）

      set @zhangsanGeo = ( SELECT home_geo FROM user_info WHERE `name` = '张三');
-- 一般的，前缀匹配得越多，越近（注：因为是网格机制，难免存在明明挨得很近的两个点，却
-- 属于不同网格的情况。所以这里匹配多少个geo_hash前缀，要根据业务上要求取的距离，对照geo_hash误差表(见后文补充说明项)，合理取值）
set @zhangsanGeoHashLike = ( SELECT CONCAT(LEFT(home_geo_hash, 2), '%')  FROM user_info WHERE `name` = '张三');

	
SELECT
	`name`
FROM
	user_info 
where 
  home_geo_hash like @zhangsanGeoHashLike
	and 
	ST_Distance_Sphere(@zhangsanGeo, home_geo) < 12000 
	and 
	`name` != '张三';
      +---------+
| name   |
+--------+
| 李四    |
+--------+
| 赵六    |
+--------+
| 吴九    |
+--------+

相关补充

GeoHash

为了提升效率，我们在使用经纬度等位置信息时，库表设计往往会考虑同时存经纬度对应的geohash

GeoHash是一种地理编码。GeoHash算法对二维的有经度和维度的地理坐标进行编码，将二维坐标映射为一个字符串，一个GeoHash字符串表示经度和纬度两个坐标，每个字符串代表特定的矩形（网格），该矩形（网格）范围内的所有坐标都共用这个字符串。

网格cell的颗粒度级别=GeoHash的长度，GeoHash越长，则网格所代表的的范围越小。Geohash 的长度对位置的精度有着非常直接的影响

image-20230330123829588

Geohash长度 距离误差
9 ±2.4m
10 ±0.6m
11 ±74mm
12 ±18.5mm

相关资料

1
https://gitee.com/JustryDeng/notebook.git
git@gitee.com:JustryDeng/notebook.git
JustryDeng
notebook
notebook
master
点此查找更多帮助

搜索帮助

Git 命令在线学习 如何在 Gitee 导入 GitHub 仓库
Git 仓库基础操作
企业版和社区版功能对比
SSH 公钥设置
如何处理代码冲突
仓库体积过大，如何减小？
如何找回被删除的仓库数据
Gitee 产品配额说明
GitHub仓库快速导入Gitee及同步更新
什么是 Release（发行版）
将 PHP 项目自动发布到 packagist.org
评论
仓库举报
回到顶部