SET FOREIGN_KEY_CHECKS=0;-------------------------------- Table structure for customer
------------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`id` int(11) unsigned NOT NULL auto_increment COMMENT '自增主键',
`name` varchar(50) NOT NULL COMMENT '名称',
`lon` double(9,6) NOT NULL COMMENT '经度',
`lat` double(8,6) NOT NULL COMMENT '纬度',
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='商户表';-------------------------------- Records of customer
------------------------------
INSERT INTO `customer` VALUES ('1','天津市区','117.315575','39.133462');
INSERT INTO `customer` VALUES ('2','北京市区','116.407999','39.894073');
INSERT INTO `customer` VALUES ('3','保定','115.557124','38.853490');
INSERT INTO `customer` VALUES ('4','石家庄','114.646458','38.072369');
INSERT INTO `customer` VALUES ('5','昌平区1','116.367180','40.009561');
INSERT INTO `customer` VALUES ('6','海淀区2','116.313425','39.973078');
INSERT INTO `customer` VALUES ('7','海淀区1','116.329236','39.987231');
然后我们开始用mysql自带的函数,计算customer表中,每个地方具体。
传入参数 纬度 40.0497810000 经度 116.3424590000
/*传入的参数为 纬度 纬度 经度 ASC升序由近至远 DESC 降序 由远到近 */
SELECT
*,ROUND(6378.138*2*ASIN(SQRT(POW(SIN((40.0497810000*PI()/180- lat *PI()/180)/2),2)+COS(40.0497810000*PI()/180)*COS(lat *PI()/180)*POW(SIN((116.3424590000*PI()/180- lon *PI()/180)/2),2)))*1000) AS juli
FROM
customer
ORDER BY
juli ASC