MySql 空间索引

发表于 |

Mysql5.7版本之后支持空间索引，试用下实现离我最近的功能。
感觉查询速度还是没有es快，但是小数据量也是一个可用的办法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
create database geo_test default charset = utf8mb4;
use geo_test;

create table t_dealer
(
    id bigint not null auto_increment primary key,
    dealer_name varchar(255) not null,
    city_code varchar(45) null,
    phone varchar(32) null,
    lat decimal(10,7) null,
    lon decimal(10,7) null,
    create_time datetime null
)default charset = utf8mb4;
create index idx_name on t_dealer (dealer_name);
comment on table t_dealer is '基本信息表';
comment on column dealer_name is '@cname:名称';
comment on column address is '@cname:地址';
comment on column city_code is '@cname:城市编码';
comment on column phone is '@cname:电话';
comment on column lat is '@cname:纬度';
comment on column lon is '@cname:经度';
comment on column create_time is '@cname:创建时间';

# 添加geo点
alter table t_dealer
add pnt POINT NULL comment '经纬度的geo点';
# 从经纬度中补全数据
update t_dealer set pnt = point(lat,lon);
# 改为not null之后才能添加索引
alter table t_dealer MODIFY pnt POINT not null;
# 添加索引，这种索引要求字段not null
ALTER TABLE t_dealer ADD SPATIAL INDEX spatIdx(pnt);

 
# 几种空间对象比较方法 
SET @g1 = GEOMFROMTEXT('Polygon((30.7108682140 114.0961681600,30.6890070000 114.5951950000,
30.2507470000 114.5767980000,30.2507470000 114.1030670000,30.7108682140 114.0961681600))');
SET @g2 = pointfromtext('Point(30.476421 114.403866)');
 
# 注意：比较的都是外包络几何类型对象 
# 包含
select * from t_dealer where MBRCONTAINS(@g1,pnt);
SELECT MBRCONTAINS(@g1,@g2), MBRCONTAINS(@g2,@g1), MBRCONTAINS(@g1,@g1);
# 被包含
select * from t_dealer where MBRWITHIN(pnt,@g1);
SELECT MBRWITHIN(@g2,@g1),MBRWITHIN(@g1,@g2);
# 不相交 
SELECT MBRDISJOINT(@g1,@g2);
# 相等 
SELECT MBREQUAL(@g1,@g2);
# 相交 
SELECT MBRINTERSECTS(@g1,@g2);
# 重叠 
SELECT MBROVERLAPS(@g1,@g2);
# 相切 
SELECT MBRTOUCHES(@g1,@g2);
# 距离 单位 m
select id,dealer_name,city_code,phone,lat,lon,
  GLength(LineStringFromWKB(linestring(pnt,pointfromtext('Point(30.476421 114.403866)'))))*100*1000 as distance
from t_dealer
order by distance asc;

离我最近查询实例

数据下载:
dealer_insert.zip