mysql中geometry类型的简单使用

网友投稿 895 2022-05-29

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

这里我创建了一张位置信息表,每个人对应的经纬度都会以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)'));

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

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

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

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

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

sql执行结果

2. 修改张三的位置信息

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

我用的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.

mysql中geometry类型的简单使用

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

set sql_safe_updates = 0;

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

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

本来想格式化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 != '张三';

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 != '张三';

MySQL SQL

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:PDF文件数字签名和加密使用
下一篇:MongoDB 数据库条件查询技巧
相关文章