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

执行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_MakeEnvelopeST_Distance_Sphere从MYSQL 5.7.6版本开始支持,ST_Distance_Sphere返回的单位为米。

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页