Mysql根据经纬度筛选数据
程序员文章站
2022-06-30 19:27:18
创建位置表，并且插入测试数据 /* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 80011 Source Host : localhost:3306 Source Database : t ......
创建位置表，并且插入测试数据
/*
navicat mysql data transfer
source server : localhost
source server version : 80011
source host : localhost:3306
source database : test
target server type : mysql
target server version : 80011
file encoding : 65001
date: 2018-11-07 16:58:27
*/
set foreign_key_checks = 0;
-- ------------------------------ table structure for location-- ----------------------------drop table
if exists `location`;create table `location` (
`id` int (10) unsigned not null auto_increment,
`name` varchar (50) not null,
`longitude` decimal (13, 10) not null,
`latitude` decimal (13, 10) not null,
primary key (`id`),
key `long_lat_index` (`longitude`, `latitude`)
) engine = innodb auto_increment = 5 default charset = utf8;
-- ------------------------------ records of location-- ----------------------------insert into `location`
values (
'1',
'广东省深圳市龙岗区坂田街道五和大道万科四季花城北区',
'22.6265210000',
'114.0606880000'
);
insert into `location`
values (
'2',
'广东省深圳市龙华区民治(地铁站)',
'22.6175280000',
'114.0406460000'
);
insert into `location`
values (
'3',
'广东省深圳市龙华区红山(地铁站)',
'22.6218860000',
'114.0234800000'
);
insert into `location`
values (
'4',
'广东省深圳市南山区西丽街道沙河西路名典商旅酒店(深圳西丽店)',
'22.5801670000',
'113.9543000000'
);
搜索附近50km的数据
#经度:22.626521
#纬度:114.060688#50km范围select *
from (
select
*, sqrt(
(
(
(22.626521 - longitude) * pi() * 12656 * cos(
((114.060688 + latitude) / 2) * pi() / 180
) / 180
) * (
(22.626521 - longitude) * pi() * 12656 * cos(
((114.060688 + latitude) / 2) * pi() / 180
) / 180
)
) + (
(
(114.060688 - latitude) * pi() * 12656 / 180
) * (
(114.060688 - latitude) * pi() * 12656 / 180
)
)
) as lc
from
location
) location
where lc < 50 ##距离小于50km
order by
##距离小于排序
lc
显示结果
上一篇： 目前的人工智能缺失了一个关键环节
下一篇： 就没有父母呗搞笑短信