CREATE DEFINER = `root`@`localhost` FUNCTION `getDistance`(lng1 FLOAT,lat1,lng2 FLOAT,lat2 FLOAT) RETURNS DOUBLE
BEGIN
DECLARE result DOUBLE;
DECLARE radLng1 DOUBLE;
DECLARE radLat1 DOUBLE;
DECLARE radLng2 DOUBLE;
DECLARE radLat2 DOUBLE;
SET radLng1 = lng1 * pi() / 180.0;
SET radLat1 = lat1 * pi() / 180.0;
SET radLng2 = lng2 * pi() / 180.0;
SET radLat2 = lat2 * pi() / 180.0;
SET result = 2 * asin(sqrt(pow(sin((radLat1 - radLat2) / 2), 2) + cos(radLat1) * cos(radLat2) * pow(sin((radLng1 - radLng2) / 2), 2)));
SET result = result * 6378.137;
SET result = ROUND(result * 100) / 100;
RETURN result;
END
计算方圆5公里的店铺:
SELECT * FROM 表名 WHERE getDistance(客户经度,客户纬度,longitude,latitude) < 5