創(chuàng)建表格腳本

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

這里我創(chuàng)建了一張位置信息表，每個人對應(yīng)的經(jīng)緯度都會以geometry類型存在表中，geohash字段是把坐標(biāo)系分成很多小方格，然后將經(jīng)緯度轉(zhuǎn)化成字符串，其原理可自行百度，在這里就不多說了。
哦，對了，geometry類型好像不能為null，所以建表時必須為not null。

插入表數(shù)據(jù)

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()函數(shù)是將字符串格式的點(diǎn)坐標(biāo)，轉(zhuǎn)化成geometry類型，還有個字段geohash是根據(jù)gis字段的值自動生成的，可以仔細(xì)看看建表腳本。

查詢

1. 查詢張三的經(jīng)緯度信息

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

astext()函數(shù)是將geometry類型轉(zhuǎn)化為字符串

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執(zhí)行結(jié)果

4. 查詢距離張三500米內(nèi)的所有人

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執(zhí)行結(jié)果

name distance point
李四 329 POINT(108.9465236664 34.2598766768)
王五 198 POINT(108.947725296 34.2590342786)
十九 317 POINT(108.9530360699 34.2599476152)
如果表中數(shù)據(jù)非常多時，這樣查效率會非常低，這時就會用到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 != '張三';