mysql 计算经纬度的距离

 longitude 经度

latitude 纬度

CREATE TABLE `shoplist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `longitude` varchar(20) DEFAULT '',
  `latitude` varchar(20) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1272 DEFAULT CHARSET=utf8

INSERT INTO `shoplist`(`longitude`, `latitude`) VALUES ('113.078188997', '22.598010425');
INSERT INTO `shoplist`(`longitude`, `latitude`) VALUES ('113.07707', '22.59955');
INSERT INTO `shoplist`(`longitude`, `latitude`) VALUES ('113.076260427', '22.600910854');

 

	$sql = 'SELECT  
id, `address`,`shopname`,(  
  6371 * acos (  
    cos ( radians(' . $latitude . ') )  
    * cos( radians( latitude ) )  
    * cos( radians(longitude ) - radians(' . $longitude . ') )  
    + sin ( radians(' . $latitude . ') )  
    * sin( radians( latitude ) )  
  )  
) AS distance  
FROM shoplist
HAVING distance < 11  
ORDER BY distance ';

SELECT  
id,(  
  6371 * acos (  
    cos ( radians(22.680591) )  
    * cos( radians( latitude ) )  
    * cos( radians(longitude ) - radians(113.030610) )  
    + sin ( radians(22.680591) )  
    * sin( radians( latitude ) )  
  )  
) AS distance  
FROM shoplist
HAVING distance < 11  
ORDER BY distance ;

计算出来的距离单位是km