参考

mysql中geometry类型的简单使用 - 知乎 (zhihu.com)

MySQL Geometry的使用 —— 地理空间类型Geometry - 长歌→ - 博客园 (cnblogs.com)

环境

MySQL 8+

说明

  • MySQL中支持的几何数据类型包括Geometry（几何）、Point（点）、LineString（线）、Polygon（面）
    以及集合类型的MultiPoint（多点）、MultiLineString（多线）、MultiPolygon（多面）、GeometryCollection（混合数据类型）

  • Geometry可以表示其他任意类型的值，剩下的只能表示单个类型的值

  • WKT（文本格式：在代码中的格式）

  • WKB（二进制格式：存储在Geometry类型的表字段中）

名称 类型 文本格式-数据示例
Point 点坐标 POINT(103 35)
LineString 线坐标 LINESTRING(103 35,103 36,104 36,105 37)
Polygon 面坐标 POLYGON((103 35,104 35,104 36,103 36,103 35))
MultiPoint 多点 MULTIPOINT(103 35, 104 34,105 35)
MultiLineString 多线 MULTILINESTRING((103 35, 104 35), (105 36, 105 37))
MultiPolygon 多面 MULTIPOLYGON(((103 35,104 35,104 36,103 36,103 35)),((103 36,104 36,104 37,103 36)))
GeometryCollection 混合类型 GEOMETRYCOLLECTION(POINT(103 35), LINESTRING(103 35, 103 37))

Geometry的常用函数

st_geohash(geometry, length)

计算 geometry 对象的 geohash 值并保留前 length 位

ST_POINTFROMTEXT

坐标点字符串转 point geometry 对象

创建表

  • gis 字段 geometry 类型存储空间位置信息，不能为 null
  • geohash 字段自动计算 gis 信息的 geohash 并存储，便于后面优化距离等计算的效率
1
2
3
4
5
6
7
8
9
10
CREATE TABLE `z_gis` (
  `id` varchar(45) NOT NULL,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `gis` geometry NOT NULL COMMENT '空间位置信息',
  `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,8)) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  SPATIAL KEY `idx_gis` (`gis`),
  KEY `idx_geohash` (`geohash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空间位置信息'

插入数据

  • ST_GeometryFromText 方法是将位置字符串转换为 geometry 类型对象
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
insert into z_gis(id,name,gis) values
(replace(uuid(),'-',''),'张三',ST_GeometryFromText('point(108.9498710632 34.2588125935)')),
(replace(uuid(),'-',''),'李四',ST_GeometryFromText('point(108.9465236664 34.2598766768)')),
(replace(uuid(),'-',''),'王五',ST_GeometryFromText('point(108.9477252960 34.2590342786)')),
(replace(uuid(),'-',''),'赵六',ST_GeometryFromText('point(108.9437770844 34.2553719653)')),
(replace(uuid(),'-',''),'小七',ST_GeometryFromText('point(108.9443349838 34.2595663206)')),
(replace(uuid(),'-',''),'孙八',ST_GeometryFromText('point(108.9473497868 34.2643456798)')),
(replace(uuid(),'-',''),'十九',ST_GeometryFromText('point(108.9530360699 34.2599476152)'));

// 设置地理字段-点信息
update basic_gpstrack
set geo = ST_GeometryFromText('POINT(109.92480850219728 34.92513786642915)')
where id='1496789003755139074'

// 设置地理字段-区域信息
update sys_depart_area
set geo = ST_GeometryFromText(
        'POLYGON((108.92480850219728 34.92513786642915,108.93347740173341 34.91605929268938,108.94145965576172 34.92091539891702,108.93330574035646 34.9290082707804,108.93150329589845 34.92914900931654,108.92480850219728 34.92513786642915))')
where id='1530041030945546242'

数据查询

查询包含指定坐标点的区域 - ST_Contains

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// 设置地理字段-区域信息
update sys_depart_area
set geo = ST_GeometryFromText(
        'POLYGON((108.92480850219728 34.92513786642915,108.93347740173341 34.91605929268938,108.94145965576172 34.92091539891702,108.93330574035646 34.9290082707804,108.93150329589845 34.92914900931654,108.92480850219728 34.92513786642915))')
where id='1530041030945546242'

// 获取数据在哪个区域内
// String point = 'point(108.93309909344663 34.92290439327385)'
select * from sys_depart_area where ST_Contains(geo, ST_GeometryFromText(#{point}))

// 设置地理字段-点信息
update basic_gpstrack
set geo = ST_GeometryFromText('POINT(109.92480850219728 34.92513786642915)')
where id='1496789003755139074'

查询指定范围之内的数据

中心点+半径的圆形区域内 - st_distance_sphere

1
2
3
4
5
6
7
8
9
# 距离点坐标（103,36）2000米之内的数据
select * from z_gis
where st_distance_sphere(ST_POINTFROMTEXT('POINT(103,36)'), gis) < 2000

# Geohash算法提速：计算指定点的geohash前6位，过滤前6位一样的数据；截取位数根据距离要求变化，参考文档最后面的 geohash 长度与精度关系
select * from z_gis
where geohash LIKE concat(st_geohash(ST_POINTFROMTEXT('POINT(103,36)'),6), '%' ) 
and st_distance_sphere(ST_POINTFROMTEXT('POINT(103,36)'), gis) < 2000
#where geohash = st_geohash(ST_POINTFROMTEXT('POINT(103,36)'), 8)

多边形区域内 - ST_Contains

1
2
3
// 指定多边形内有哪些数据
String geoText = 'POLYGON((108.92480850219728 34.92513786642915,108.93347740173341 34.91605929268938,108.94145965576172 34.92091539891702,108.93330574035646 34.9290082707804,108.93150329589845 34.92914900931654,108.92480850219728 34.92513786642915))'
select * from z_gis where ST_Contains(ST_GeometryFromText(#{geoText}, geo)

查询张三的位置信息

1
select name, gis from z_gis where name = '张三';

修改张三的位置信息

1
2
3
4
update z_gis set gis = ST_GeometryFromText('point(108.9465236664 34.2598766768)') where name = '张三';

# 支持通过非ID字段修改数据
set sql_safe_updates = 0;

查询张三和李四之间的距离 - st_distance_sphere

  • st_distance_sphere() 函数计算两点之间距离，需要两个参数，都是 geometry 类型的

  • floor() 函数是把计算出的距离取整

1
2
3
4
5
6
7
SELECT
	floor(
	st_distance_sphere ( ( SELECT gis FROM z_gis WHERE NAME = '张三' ), gis )) distance 
FROM
	z_gis 
WHERE
	NAME = '李四';

查询距离张三500米内的所有人

返回数据包含距离

一般查询

  • 如果表中数据非常多时，这样查效率会非常低，这时就会用到geohash字段查询
1
2
3
4
5
6
7
8
SELECT NAME, FLOOR(
	ST_DISTANCE_SPHERE (( SELECT gis FROM z_gis WHERE NAME = '张三' ), gis )) distance,
	gis  point 
FROM
	z_gis 
WHERE
	ST_DISTANCE_SPHERE (( SELECT gis FROM z_gis WHERE NAME = '张三' ), gis ) < 500 
	AND NAME != '张三';

使用 geohash 字段优化查询

  • geohash是把经纬度转成字符串，建表的时候我定义让它转成8位字符，当两个点离得越近时，它生成的geohash字符串前面相同的位数越多，所以我在这里先用left()截取前6位字符，前6位相同的误差在±600米左右，然后模糊查询，查出大概符合条件的数据，最后再精确比较

  • 用geohash 查询会有边界问题，所以查询出来的结果又可能不准确，可以用程序(例如java代码)先查出当前点周围8个范围的geohash值，然后再匹配这9个范围的所有数据，这样就解决了geohash 的边界问题

  • geohash官方文档地址：https://en.wikipedia.org/wiki/Geohash

1
2
3
4
5
6
7
8
9
SELECT NAME, floor(
	ST_DISTANCE_SPHERE (( SELECT gis FROM z_gis WHERE NAME = '张三' ), gis )) distance,
	 gis point 
FROM
	z_gis 
WHERE
	geohash LIKE concat( LEFT (( SELECT geohash FROM z_gis WHERE NAME = '张三' ), 6 ), '%' )
	AND ST_DISTANCE_SPHERE (( SELECT gis FROM z_gis WHERE NAME = '张三' ), gis ) < 500 
	AND NAME != '张三';

geohash

geohash长度 误差距离（km）
1 ±2500
2 ±630
3 ±78
4 ±20
5 ±2.4
6 ±0.61
7 ±0.076
8 ±0.019