MySQL中怎处理和地理位置有关的操作

2024年7月11日 13:05 by wst

database

---

需求

在项目中处理地理位置，包括：存储、查找、修改、求附近；

实现方法

在说这个之前，有个前提跟打家同步下，使用的MySQL版本为：8.0.37

别的版本没调研过，也许能用。

表结构如下：

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)')

查找

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

修改

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;"

意义：求和点point(80 45) 相距小于100公里的记录；

总结

MySQL还真是强大，竟然对地理位置操作支持的这么好，感谢开发者！

 

---