mysql查附近500米商户_mysql 根据已知的经纬度查找附近500米范围内的其他点

慕丝7291255

如果数据不是很多，可以用函数算

#定义函数的sql

DELIMITER $$

CREATE DEFINER = CURRENT_USER FUNCTION `get_distance`(`lon1` float,`lat1` float,`lon2` float,`lat2` float)

RETURNS double

begin

declare d double;

declare radius int;

set radius = 6378140; #假设地球为正球形，直径为6378140米

set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2)

*SIN((lat1-lat2)*PI()/180/2)+

COS(lat2*PI()/180)*COS(lat1*PI()/180)

*SIN((lon1-lon2)*PI()/180/2)

*SIN((lon1-lon2)*PI()/180/2)),

SQRT(1-SIN((lat1-lat2)*PI()/180/2)

*SIN((lat1-lat2)*PI()/180/2)

+COS(lat2*PI()/180)*COS(lat1*PI()/180)

*SIN((lon1-lon2)*PI()/180/2)

*SIN((lon1-lon2)*PI()/180/2))))*radius;

return d;

END $$

DELIMITER $$;

使用的话直接传参数就行了

select ... from ... where get_distance(114.06667,22.61667,longitude,latitude)<500;