mysql中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类型转化为字符串

2. 修改张三的位置信息

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

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

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

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执行结果

name distance point
李四 329 POINT(108.9465236664 34.2598766768)
王五 198 POINT(108.947725296 34.2590342786)
十九 317 POINT(108.9530360699 34.2599476152)
如果表中数据非常多时,这样查效率会非常低,这时就会用到geohash字段查询

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

推荐阅读更多精彩内容

  • pdf下载地址:Java面试宝典 第一章内容介绍 20 第二章JavaSE基础 21 一、Java面向对象 21 ...
    王震阳阅读 73,548评论 25 504
  • 1.场景 随着智能手机和传感器技术的发展,LBS(Location based service)类的应用也逐渐多了...
    Daniel_adu阅读 5,001评论 3 13
  • 手动不易,转发请注明出处 --Trance 数据库系统命令: (1).查看存储过程状态:show pro...
    Trance_b54c阅读 150评论 0 0
  • 什么是正则表达式 正则表达式是对字符串操作的一种逻辑公式,就是用事先定义好的一些特定字符,及这些特定的组合,组成一...
    追寻那抹阳光丶阅读 11评论 0 0
  • 每次到杭州都会为她的美折服。她的绿色不似柳州那样扎眼,充满活力而又温润如玉。用岁月静好来形容我眼中的杭州之美,最为...
    古道青峰阅读 89评论 0 2