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
62
63
| 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;
|
