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类型等
示例说明:
-
假设我们的表结构是这样的:
-
那么凡是几何类型的数据,都可以往这列放,如:
-
常用几何函数
更多函数详见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 的长度对位置的精度有着非常直接的影响
Geohash长度 | 距离误差 | ||||
---|---|---|---|---|---|
9 | ±2.4m | ||||
10 | ±0.6m | ||||
11 | ±74mm | ||||
12 | ±18.5mm |