mysql空间索引

灰太狼 2023-06-19 15:26 7阅读 0赞

## 空间索引示例 ##

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();

## 经纬度距离计算 ##

![在这里插入图片描述][20191207110412100.png]

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

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\_MakeEnvelope][ST_MakeEnvelope]和[ST\_Distance\_Sphere][ST_Distance_Sphere]从MYSQL 5.7.6版本开始支持，`ST_Distance_Sphere`返回的单位为米。

[20191207110412100.png]: https://img-blog.csdnimg.cn/20191207110412100.png
[ST_MakeEnvelope]: https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-makeenvelope
[ST_Distance_Sphere]: https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere