2024-08-0407:34:11数据库教程Comments1,554 views字数 1369阅读模式

需求

在项目中处理地理位置，包括：存储、查找、修改、求附近；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/64724.html

实现方法

在说这个之前，有个前提跟打家同步下，使用的MySQL版本为：8.0.37文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/64724.html

别的版本没调研过，也许能用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/64724.html

表结构如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/64724.html

CREATE TABLE `store_list` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '店铺名称',
  `note` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '店铺备注',
  `merchant_id` int NOT NULL COMMENT '商家ID',
  `position` point NOT NULL COMMENT '店铺经纬度',
  `status` tinyint DEFAULT '1' COMMENT '状态:1可用,0废弃',
  `ctime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `utime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='店铺列表'

存储

INSERT INTO store_list(`name`,`note`,`merchant_id`,`position`,`status`) VALUES ('聚宝源(永乐店)','涮羊肉','1',ST_GeomFromText('POINT(116 39)'),'1')

注意position字段值的表示方法：ST_GeomFromText('POINT(116 39)')文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/64724.html

查找

select id,name,note,merchant_id,ST_X(position) AS longitude, ST_Y(position) AS latitude, status,ctime,utime from store_list where id=1;

注意经纬度的表示方式：ST_X(position) AS longitude, ST_Y(position) AS latitude文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/64724.html

修改

update store_list set `position`=ST_GeomFromText('POINT(116.2 39.1)') where id=1;

求附近

SELECT id,name,merchant_id, ST_Distance_Sphere(point(80, 45), position, 6371) AS distance_km 
FROM store_list WHERE status=1 HAVING distance_km < 100 order by distance_km asc;"