mysql空间索引

空间索引示例

1
2
3
4
5
6
7
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万条测试数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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();

经纬度距离计算

image

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

1
2
3
4
5
6
7
8
9
10
11
12
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。因此创建出来的边界矩形会比实际需求的边界大。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 东经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如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 东经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返回的单位为米。