建表
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='空间位置信息
插入数据
insert into z_gis(id,name,gis) values
(replace(uuid(),'-',''),'张三',geomfromtext('point(108.9498710632 34.2588125935)')),
(replace(uuid(),'-',''),'李四',geomfromtext('point(108.9465236664 34.2598766768)')),
查询
. 查询张三的经纬度信息:astext()函数是将geometry类型转化为字符串
select name, astext(gis) gis from z_gis where name = '张三';