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:创建时间';
alter table t_dealer add pnt POINT NULL comment '经纬度的geo点';
update t_dealer set pnt = point(lat,lon);
alter table t_dealer MODIFY pnt POINT 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);
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;
|