SET FOREIGN_KEY_CHECKS=;
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`id` int() unsigned NOT NULL auto_increment COMMENT '自增主鍵',
`name` varchar() NOT NULL COMMENT '名稱',
`lon` double(,) NOT NULL COMMENT '經度',
`lat` double(,) NOT NULL COMMENT '緯度',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8 COMMENT='商戶表';
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('', '天津市區', '117.315575', '39.133462');
INSERT INTO `customer` VALUES ('', '北京市區', '116.407999', '39.894073');
INSERT INTO `customer` VALUES ('', '保定', '115.557124', '38.853490');
INSERT INTO `customer` VALUES ('', '石家莊', '114.646458', '38.072369');
INSERT INTO `customer` VALUES ('', '昌平區1', '116.367180', '40.009561');
INSERT INTO `customer` VALUES ('', '海澱區2', '116.313425', '39.973078');
INSERT INTO `customer` VALUES ('', '海澱區1', '116.329236', '39.987231');
然後我們開始用mysql自帶的函數，計算customer表中，每個地方具體。
傳入參數 緯度 40.0497810000 經度 116.3424590000
SELECT
*,
ROUND(
6378.138 * * ASIN(
SQRT(
POW(
SIN(
(
40.0497810000 * PI() / - lat * PI() /
) /
),
) + COS(40.0497810000 * PI() / ) * COS(lat * PI() / ) * POW(
SIN(
(
116.3424590000 * PI() / - lon * PI() /
) /
),
)
)
) *
) AS juli
FROM
customer
ORDER BY
juli ASC
至此，我們就能清楚的檢視到緯度 40.0497810000 經度 116.3424590000 距離customer表中的每個地區的距離(機關 m)
PS 用到的經緯度查詢工具 http://www.gpsspg.com/maps.htm
原文連結：http://www.cnblogs.com/jiafuwei/p/5699091.html