mysql中geometry类型的简单使用

ozil_oo关注

0.1892018.08.09 10:31:48字数 304阅读 4,012

创建表格脚本

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