mysql空间索引
祈雨的笔记
·
2019-12-01
·
via 祈雨的笔记
空间索引示例
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();
|
经纬度距离计算
如果知道两个点的纬度和经度，则可以计算它们之间的距离。计算该距离的最简单方法是假设地球是一个完美的球体。但实际上地球是一个两极稍扁、赤道略鼓的不规则球体，因此许多情况下仅仅是计算一个合理的近似值。
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
|
set @longitude = 121.5;
set @latitude = 31.5;
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
|
set @longitude = 121.5;
set @latitude = 31.5;
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返回的单位为米。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。
