插入模拟测试数据
/* Navicat MySQL Data Transfer Source Server : aliyun1 Source Server Version : 50634 Source Host : rm-wz94q8fo4vvc312i4o.mysql.rds.aliyuncs.com:3306 Source Database : bcsc Target Server Type : MYSQL Target Server Version : 50634 File Encoding : 65001 Date: 2017-06-23 09:26:04 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for address -- ---------------------------- DROP TABLE IF EXISTS `address`; CREATE TABLE `address` ( `address` char(80) NOT NULL, `longitude` varchar(255) DEFAULT NULL, `latitude` varchar(255) DEFAULT NULL, PRIMARY KEY (`address`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of address -- ---------------------------- INSERT INTO `address` VALUES ('1', '115.966291', '28.68443'); INSERT INTO `address` VALUES ('2', '115.959871', '28.66931'); INSERT INTO `address` VALUES ('3', '115.930901', '28.70084'); INSERT INTO `address` VALUES ('4', '120.251551', '30.16567'); INSERT INTO `address` VALUES ('5', '115.906261', '28.66685'); INSERT INTO `address` VALUES ('6', '115.873132', '28.69738'); INSERT INTO `address` VALUES ('7', '115.839282', '28.68628'); INSERT INTO `address` VALUES ('8', '115.934431', '28.712031'); INSERT INTO `address` VALUES ('9', '115.947223', '28.717987');
address longitude latitude
1 115.966291 28.68443
2 115.959871 28.66931
3 115.930901 28.70084
4 120.251551 30.16567
5 115.906261 28.66685
6 115.873132 28.69738
7 115.839282 28.68628
8 115.934431 28.712031
9 115.947223 28.717987
传入经纬度查询距离坐标距离N千米的人员
SELECT
*
FROM
address
WHERE
sqrt(
(
(
(115.934144 - longitude) * PI() * 12656 * cos(
((28.715896 + latitude) / 2) * PI() / 180
) / 180
) * (
(115.934144 - longitude) * PI() * 12656 * cos(
((28.715896 + latitude) / 2) * PI() / 180
) / 180
)
) + (
(
(28.715896 - latitude) * PI() * 12656 / 180
) * (
(28.715896 - latitude) * PI() * 12656 / 180
)
)
) < 3(小于3KM)