mysql空间索引

空间索引示例

CREATE TABLE `shop_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `shop_name` varchar(64) NOT NULL COMMENT '门店名称',
  `geom_point` geometry NOT NULL COMMENT '经纬度',
  PRIMARY KEY (`id`),
  SPATIAL KEY `geom_index` (`geom_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

初始化500万条测试数据：

delimiter  //
CREATE PROCEDURE init_shop_info() 
BEGIN 
	DECLARE count INT;
	DECLARE batch INT;
	DECLARE initLong INT;
	DECLARE initLat INT;
	SET count = 0;
	SET initLong = 121;
	SET initLat = 31;
	WHILE count < 5000000 DO
		SET batch = 0;
		START TRANSACTION; 
		WHILE batch < 1000 DO
			insert into shop_info(shop_name, geom_point) values (concat('shop', count), Point(initLong + Rand(),initLat + Rand()));
			SET batch = batch + 1;
			SET count = count + 1;
		END WHILE;
		COMMIT; 
	END WHILE;
END //
delimiter ;
call init_shop_info();

经纬度距离计算

如果知道两个点的纬度和经度，则可以计算它们之间的距离。计算该距离的最简单方法是假设地球是一个完美的球体。但实际上地球是一个两极稍扁、赤道略鼓的不规则球体，因此许多情况下仅仅是计算一个合理的近似值。

1个纬度 ≈ 69英里（111km），与经度无关

在纬度 = 0的赤道处，1度经度 ≈ 69英里（111km），但这随纬度而变化：
纬度±10，1度经度 = 68英里（109km）
纬度±20，1度经度 = 65英里（104km）
纬度±30，1度经度 = 60英里（96km）
纬度±40，1度经度 = 53英里（85km）
纬度±50，1度经度 = 44英里（71km）
纬度±60，1度经度 = 35英里（56km）
纬度±70，1度经度 = 24英里（38km）
纬度±80，1度经度 = 12英里（19km）
纬度±90，1度经度 = 0英里（0km）

执行SQL获取附近2KM以内的记录。

为了指定位置周围创建边界矩形（以便可以利用其上的空间索引），可以使用经度和纬度之间的平均距离111公里。每纬度近似111km，而每经度则超过111km。因此创建出来的边界矩形会比实际需求的边界大。

-- 东经121.5
set @longitude = 121.5;
-- 北纬31.5
set @latitude = 31.5;
-- 2KM范围内
set @distance = 2000;

select id, x(geom_point) longitude, y(geom_point) latitude, ST_Distance_Sphere(Point(@longitude, @latitude), geom_point) as distance 
from shop_info 
where MBRContains(ST_MakeEnvelope(
			point((@longitude+(@distance/1000/111)), (@latitude+(@distance/1000/111))),
			point((@longitude-(@distance/1000/111)), (@latitude-(@distance/1000/111))))
		, geom_point) 
order by distance limit 10;

如果需要边界矩形更精确，则可以使用cos(radians(${latitude})) * 111进行经度计算。示例SQL如下：

-- 东经121.5
set @longitude = 121.5;
-- 北纬31.5
set @latitude = 31.5;
-- 2KM范围内
set @distance = 2000;

select id, x(geom_point) longitude, y(geom_point) latitude, ST_Distance_Sphere(Point(@longitude, @latitude), geom_point) as distance 
from shop_info 
where MBRContains(ST_MakeEnvelope(
			point((@longitude+(@distance/1000/111*cos(radians(@latitude)))), (@latitude+(@distance/1000/111))),
			point((@longitude-(@distance/1000/111*cos(radians(@latitude)))), (@latitude-(@distance/1000/111))))
		, geom_point) 
order by distance limit 10;

注：ST_MakeEnvelope和ST_Distance_Sphere从MYSQL 5.7.6版本开始支持，ST_Distance_Sphere返回的单位为米。