MySQL 8.0 GIS功能 根据坐标圏选一定范围内的相关数据

解决方案

创建表

CREATE TABLE `store` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `item_id` bigint NOT NULL DEFAULT '0',
  `longitude` double(50,6) NOT NULL COMMENT '经度',
  `latitude` double(50,6) NOT NULL COMMENT '纬度',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `store` (`id`, `name`, `item_id`, `longitude`, `latitude`)
VALUES
    (3, '欧美金融城_星爸爸', 1, 120.011496, 30.287637),
    (4, '永乐城_星爸爸', 1, 120.011802, 30.280433),
    (5, '小镇_一鸣', 2, 120.011209, 30.298552),
    (6, '赛银国际', 2, 120.027181, 30.280808),
    (7, '合景天峻_星爸爸', 1, 120.004597, 30.291660),
    (8, '仓溢东苑_星爸爸', 1, 120.008622, 30.292783);

 GIS相关查询功能

SELECT
	shop.* 
FROM
	(
SELECT
	*,
	round( st_distance_sphere ( point ( 120.012484, 30.298926 ), point ( `longitude`, `latitude` ) ) ) dis 
FROM
	store 
HAVING
	dis < 5000 
ORDER BY
	dis 
	LIMIT 100000 
	) shop 
GROUP BY
	shop.item_id 
ORDER BY
	dis 
	LIMIT 10