时间：2021-07-01 10:21:17 帮助过：60人阅读
创建位置表，并且插入测试数据
/*
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
显示结果
Mysql根据经纬度筛选数据
标签：targe set get dex mysql not local type 酒店