SQL语句计算经纬度距离

二： SQL语句计算经纬度距离

SELECT id, ( 6371* acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25  ORDER BY id LIMIT 20

SELECT
(
6371 * acos(
cos(radians(29.490295)) * cos(radians(29.615467)) * cos(
radians(106.581515) - radians(106.486654)
) + sin(radians(29.490295)) * sin(radians(29.615467))
)
)*1000 AS distance

 

delimiter //

drop function if exists FUN_JW_DIST;

CREATE FUNCTION `FUN_JW_DIST`(lng1 double(15,9), lat1 double(15, 9), lng2 double(15,9), lat2 double(15,9)) RETURNS int(11)
BEGIN
DECLARE dist int;

SET dist = round((
6371 * acos(
cos(radians(lat1)) * cos(radians(lat2)) * cos(
radians(lng1) - radians(lng2)
) + sin(radians(lat1)) * sin(radians(lat2))
)
)*1000);

RETURN (dist);
END;

SELECT FUN_JW_DIST(106.486654 , 29.490295 , 106.581515 , 29.615467);