CREATE FUNCTION `lat_lng_distance` (lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT) | |
RETURNS FLOAT | |
DETERMINISTIC | |
BEGIN | |
RETURN 6371 * 2 * ASIN(SQRT( | |
POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2), | |
2) + COS(lat1 * pi()/180 ) * COS(abs(lat2) * | |
pi()/180) * POWER(SIN((lng1 - lng2) * | |
pi()/180 / 2), 2) )); | |
END | |
--Returns the distance in kilometers, assuming a earth radius of 6,371 km. |
This comment has been minimized.
This comment has been minimized.
Are you sure it returns a value in kilometers?, I'm getting huge numbers |
This comment has been minimized.
This comment has been minimized.
Thx. |
This comment has been minimized.
This comment has been minimized.
spot on, cheers |
This comment has been minimized.
This comment has been minimized.
Thanks i have latitude/longitude coordinates i want to get the users who are within 50km of these latitude/longitude coordinates can some one please help me on this. |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
I was getting HUGE numbers too - removing the 2x occurrences of abs() around lat2 'fixed' it for me. |
This comment has been minimized.
This comment has been minimized.
Is this compatible with mysql 5.7 ? Please confirm. |
This comment has been minimized.
This comment has been minimized.
You don't need that proc with mysql 5.7. Just use ST_Distance_Sphere |
This comment has been minimized.
This comment has been minimized.
Does this function still work correctly when either of latitude or longitude or both of them are negative? |
This comment has been minimized.
This comment has been minimized.
I think you should use a more 'modern' solution select ST_Distance_Sphere(
point(-11.11, 12.12),
point(-13.13, 14.14)
) This will give you an answer in meters If you need kilometres you can use: select ST_Distance_Sphere(
point(-11.11, 12.12),
point(-13.13, 14.14)
) * .001 reference: Mysql docs |
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
Hi |
This comment has been minimized.
This comment has been minimized.
You can use, CREATE FUNCTION `st_distance_sphere`(`pt1` POINT, `pt2` POINT) RETURNS
decimal(10,2)
BEGIN
return 6371 * 2 * ASIN(SQRT(
POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * pi()/180 / 2),
2) + COS(ST_Y(pt1) * pi()/180 ) * COS(ST_Y(pt2) *
pi()/180) * POWER(SIN((ST_X(pt2) - ST_X(pt1)) *
pi()/180 / 2), 2) ));
END |
This comment has been minimized.
Thanks a lot.