地理空间数据Geometry在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 的边界问题。