地理空間資料Geometry在MySQL中使用

Ruthless發表於2022-01-21
MySql

建表指令碼

CREATE TABLE `z_gis` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `gis` geometry NOT NULL COMMENT '空間位置資訊',
  `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,8)) VIRTUAL,
  `item_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  SPATIAL KEY `idx_gis` (`gis`),
  KEY `name` (`name`),
  KEY `idx_geohash` (`geohash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='空間位置資訊';

此時建立了一張位置資訊表，每個人對應的經緯度都會以geometry型別存在表中，geohash欄位是把座標系分成很多小方格，然後將經緯度轉化成字串，其原理可自行百度，在這裡就不多說了。

geometry型別好像不能為null，所以建表時必須為not null。

插入表資料

insert into z_gis(id,name,gis,item_id) values
(880000,'張三',st_geomfromtext('point(108.9498710632 34.2588125935)'), 100),
(890000,'李四',st_geomfromtext('point(108.9465236664 34.2598766768)'), 101);


1. 查詢張三的經緯度資訊

#496 張三 POINT(108.9465236664 34.2598766768)
select id, name, st_astext(gis) gis from z_gis where name = '張三';

注：st_astext()函式是將geometry型別轉化為字串

2. 修改張三的位置資訊

update z_gis set gis = st_geomfromtext('point(108.9465236664 34.2598766768)') where name = '張三';

 

3. 查詢張三和李四之間的距離
注：st_distance_sphere()函式是計算兩點之間距離，所以傳兩個引數，都是geometry型別的，floor()函式是把計算出的距離取整，以米為單位。

-- 計算廈門到安溪的距離，單位米
select floor(st_distance_sphere(
   POINT(118.03394,24.48405), -- 廈門經緯度
    POINT(118.18685,25.05544) -- 安溪經緯度
)) distance

-- 查詢張三和李四之間的距離
select floor(st_distance_sphere(
    (select gis from z_gis where name= '張三'),
    gis
)) distance from z_gis where name= '李四';

 

4. 查詢距離張三500米內的所有人

張三資料資訊：(id, name, gis, geohash)(183    張三    POINT(120.259718 30.138463)    wtme2wd3    53)

-- POINT(120.259718,30.138463) 張三的經緯度
-- wtme2w 張三的geohash值
-- 183 張三的主鍵id
select 
    name,
    --推薦用經緯度傳入，不要子查詢
    -- floor(st_distance_sphere((SELECT gis FROM z_gis WHERE  name = '張三'), gis)) distance, 
    floor(st_distance_sphere(point(120.259718,30.138463), gis)) distance,
    st_astext(gis) point
from z_gis
where
   geohash like 'wtme2w%' and
   st_distance_sphere(point(120.259718,30.138463), gis) < 5000 and
   id<>183;

前面說過geohash是把經緯度轉成字串，建表的時候我定義讓它轉成8位字元，當兩個點離得越近時，它生成的geohash字串前面相同的位數越多，所以我在這裡先用left()擷取前6位字元，前6位相同的誤差在±600米左右，然後模糊查詢，查出大概符合條件的資料，最後再精確比較，下面是geohash官方文件對geohash長度和距離誤差的說明：

注意：用geohash 查詢會有邊界問題，所以查詢出來的結果又可能不準確，可以用程式(例如java程式碼)先查出當前點周圍8個範圍的geohash值，然後再匹配這9個範圍的所有資料，這樣就解決了geohash 的邊界問題。

相關文章