Skip to content

Instantly share code, notes, and snippets.

@Usse
Created November 16, 2012 10:45
Show Gist options
  • Save Usse/4086343 to your computer and use it in GitHub Desktop.
Save Usse/4086343 to your computer and use it in GitHub Desktop.
MySQL calculate distance between two latitude/longitude coordinates
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.
@habedi
Copy link

habedi commented Sep 19, 2019

Thanks!

@free-developers
Copy link

Hi
I have many locations in Mysql
I need calculate distance between there locations
How do i can do it?
Thanks

@habedi
Copy link

habedi commented Sep 27, 2019

You can use, ST_Distance_Sphere as @Usse mentioned, but for some mysterious reason MariaDB (Version 10.4.7) that I use does not have this function so you could define a function to calculate the spherical distance between two points on the surface of the earth yourself.
This is the code for creating the procedure that I'm talking about:

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment