mysql中geometry类型的简单使用

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

这里我创建了一张位置信息表,每个人对应的经纬度都会以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执行结果 
mysql中geometry类型的简单使用
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.

好像是除了用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执行结果 
mysql中geometry类型的简单使用
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执行结果 
mysql中geometry类型的简单使用
    如果表中数据非常多时,这样查效率会非常低,这时就会用到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 != '张三';  

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

mysql中geometry类型的简单使用

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

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

    之前没用过markdown编辑器,所以文档格式排版很乱,请大家见谅,上面有解释不对的地方,也请大佬们及时指出来,毕竟我也算是小白,还有很多地方需要学习。
———————
作者:MinjerZhang
来源:CSDN
原文:https://blog.csdn.net/MinjerZhang/article/details/78137795
版权声明:本文为博主原创文章,转载请附上博文链接!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/112671.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • shell中 -eq,-ne,-gt,-lt,-ge,-le数字比较符

    使用说明:-eq//equals等于-ne//noequals不等于-qt//qreaterthan大于-lt//lessthan小于-ge//greaterequals大于等于-le//lessequals小于等于注:1、在shell中进行比较时,结果为0代表真,为1代表假。2、-eq,-ne等比较符只能用于数字比较,有字符也会先转换成数字然后进行比较。…

  • c语言中字符串比较的库函数是什么_c语言比较字符串大小

    c语言中字符串比较的库函数是什么_c语言比较字符串大小在单片机串口实现字符串命令解析这篇文章中分析了在串口通信中如何去解析字符串命令,这篇文章就来讨论下字符串比较的方法都有哪些?说起比较运算,肯定第一时间想到了C语言中关于比较的相关运算符“>、<、!=、>=、<=、==”,那么要比较两个字符串是否相等是不是直接用“==”比较就行了。下面就来看看这种方法行不行?先看一个例子voidmain(void){chars1[]=”abc”;chars2[]…

    2022年10月26日
  • 自己编辑的英语stem课程

    自己编辑的英语stem课程Lesson1 Excuseme!  对不起!   Listentothetapethenanswerthisquestion.Whosehandbagisit?  听录音,然后回答问题,这是谁的手袋?   Excuseme!  Yes?  Isthisyourhandbag?  Pardon?  Isthisyourhandbag?  …

  • goland 2021.11.4 激活码-激活码分享

    (goland 2021.11.4 激活码)这是一篇idea技术相关文章,由全栈君为大家提供,主要知识点是关于2021JetBrains全家桶永久激活码的内容https://javaforall.cn/100143.htmlIntelliJ2021最新激活注册码,破解教程可免费永久激活,亲测有效,上面是详细链接哦~23LNPMIJZT-eyJsaWNlb…

  • qq截图存放在电脑的哪个文件夹

    qq截图存放在电脑的哪个文件夹1,登陆QQ,页面最下面的“主菜单”,选择“设置”,点击进入;2,在弹出的窗口中选择“文件管理”,点击;3,在“文件管理”页面选择“打开文件夹”,返回到上层文件夹:QQ文件夹页面4,在QQ文件夹

  • Mac安装ElasticSearch介绍

    Mac安装ElasticSearch介绍原文地址:https://segmentfault.com/a/1190000005792528今日项目需要一个小型的搜索的功能的支持,有很多开源的搜索引擎啦,当然Elasticsearch个人认为上手容易,安装也比较方便。ES安装前需要确认你笔记本上已经安装了java,如果没有安装了,可以oracle官网下载就行了,java弄完后,我们可以在命令后工具中输

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号