mysql计算两点经纬度之间的距离

一、公式如下，单位米：

第一点经纬度：lng1 lat1

第二点经纬度：lng2 lat2

round(6378.138*2*asin(sqrt(pow(sin( (lat1*pi()/180-lat2*pi()/180)/2),2)+cos(lat1*pi()/180)*cos(lat2*pi()/180)* pow(sin( (lng1*pi()/180-lng2*pi()/180)/2),2)))*1000)

例如：

SELECT store_id,lng,lat, ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-lat*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(lat*PI()/180)*POW(SIN((114.173881*PI()/180-lng*PI()/180)/2),2)))*1000) AS juli
FROM store_info
ORDER BY juli DESC

LIMIT 316

二、也可以写成mysql函数的形式：

DELIMITER $$
USE `数据库名称`$$
DROP FUNCTION IF EXISTS `fun_distance`$$
CREATE DEFINER=`root`@`%` FUNCTION `fun_distance`(lat1 FLOAT,lng1 FLOAT,lat2 FLOAT,lng2 FLOAT) RETURNS DECIMAL(10,1)
BEGIN
SET @num=6378.138*2*ASIN(SQRT(POW(SIN((lat1*PI()/180-lat2*PI()/180)/2),2)+COS(lat1*PI()/180)*COS(lat2*PI()/180)*POW(SIN((lng1*PI()/180-lng2*PI()/180)/2),2)));
RETURN ROUND(@num,1);
END$$
DELIMITER ;

sql语句中调用：

SELECT * FROM (SELECT a.user_id,a.nickname,a.sex,
IFNULL(a.head_img,'') AS head_img,
IFNULL(fun_distance(?,?,b.lat,b.lng),0) AS distance 
FROM user_info a INNER JOIN POSITION b ON a.user_id = b.user_id
WHERE b.lat IS NOT NULL AND b.lng IS NOT NULL AND a.user_id != ? 
AND a.allow_search = 0 
AND a.user_id NOT IN ( SELECT c.friend_id FROM user_friend c WHERE c.user_id=? AND c.status=2)) t WHERE t.distance <= 10000
ORDER BY t.distance,t.user_id