mysql中geometry类型的简单使用(搜索附近的人)

mysql中geometry类型的简单使用

编写本文的目的：

• 让和两天前的我一样的初学者，能够更快的使用geometry类型存储空间点数据
• 也是为了自己加深印象，更熟练的使用geometry类型

建表脚本

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='空间位置信息'

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

这里我创建了一张位置信息表，每个人对应的经纬度都会以geometry类型存在表中，geohash字段是把坐标系分成很多小方格，然后将经纬度转化成字符串，其原理可自行百度，在这里就不多说了。 
哦，对了，geometry类型好像不能为null，所以建表时必须为not null。

插入表数据

insert into z_gis(id,name,gis) values
(replace(uuid(),'-',''),'张三',geomfromtext('point(108.9498710632 34.2588125935)')),
(replace(uuid(),'-',''),'李四',geomfromtext('point(108.9465236664 34.2598766768)')),
(replace(uuid(),'-',''),'王五',geomfromtext('point(108.9477252960 34.2590342786)')),
(replace(uuid(),'-',''),'赵六',geomfromtext('point(108.9437770844 34.2553719653)')),
(replace(uuid(),'-',''),'小七',geomfromtext('point(108.9443349838 34.2595663206)')),
(replace(uuid(),'-',''),'孙八',geomfromtext('point(108.9473497868 34.2643456798)')),
(replace(uuid(),'-',''),'十九',geomfromtext('point(108.9530360699 34.2599476152)'));

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

名字是我随便起的，不要喷我哦，经纬度是我在地图上随便取的点，geomfromtext()函数是将字符串格式的点坐标，转化成geometry类型，还有个字段geohash是根据gis字段的值自动生成的，可以仔细看看建表脚本。

接下来是几个简单的查询例子

1. 查询张三的经纬度信息

select name, astext(gis) gis from z_gis where name = '张三';

• 1

• astext()函数是将geometry类型转化为字符串

sql执行结果　

2. 修改张三的位置信息

update z_gis set gis = geomfromtext('point(108.9465236664 34.2598766768)') where name = '张三';

• 1

• 我用的Mysql Workbench工具，修改时报错如下：

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

• 1

• 好像是除了用id修改，其他修改都会报这个错，下面这样设置一下就OK了 。

set sql_safe_updates = 0;

• 1

3. 查询张三和李四之间的距离

select floor(st_distance_sphere(
    (select gis from z_gis where name= '张三'),
    gis
)) distance from z_gis where name= '李四';

• 1
• 2
• 3
• 4

• 本来想格式化sql语句的，但是发现格式化之后的sql 基本全变成大写的了，我觉得辨识度更低了，所有大家就这样将就看吧，st_distance_sphere()函数是计算两点之间距离的，所以传两个参数，都是geometry类型的，floor()函数是把计算出的距离取整。

sql执行结果　

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

SELECT 
    name,
    FLOOR(ST_DISTANCE_SPHERE((SELECT 
                            gis
                        FROM
                            z_gis
                        WHERE
                            name = '张三'),
                    gis)) distance,
                    astext(gis) point
FROM
    z_gis
WHERE
    ST_DISTANCE_SPHERE((SELECT 
                    gis
                FROM
                    z_gis
                WHERE
                    name = '张三'),
            gis) < 500
        AND name != '张三';

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21

sql执行结果　

• 如果表中数据非常多时，这样查效率会非常低，这时就会用到geohash字段查询

sql语句如下：

SELECT 
    name,
    floor(ST_DISTANCE_SPHERE((SELECT 
                    gis
                FROM
                    z_gis
                WHERE
                    name = '张三'),
            gis)) distance,
            astext(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 != '张三';  

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22

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

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

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

• 之前没用过markdown编辑器，所以文档格式排版很乱，请大家见谅，上面有解释不对的地方，也请大佬们及时指出来，毕竟我也算是小白，还有很多地方需要学习。

https://blog.csdn.net/MinjerZhang/article/details/78137795