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.
@kinginblue
Copy link

Thanks a lot.

@zafe
Copy link

zafe commented Mar 16, 2017

Are you sure it returns a value in kilometers?, I'm getting huge numbers

@ozhantr
Copy link

ozhantr commented Mar 20, 2017

Thx.

@richardstuart
Copy link

spot on, cheers

@usamamashkoor
Copy link

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.
Thanks in advance.

@zjbarg
Copy link

zjbarg commented Mar 21, 2018

Thank you!

@miking7
Copy link

miking7 commented May 23, 2018

I was getting HUGE numbers too - removing the 2x occurrences of abs() around lat2 'fixed' it for me.

@pushpanktugnawat
Copy link

Is this compatible with mysql 5.7 ?

Please confirm.

@cjohnsoninc
Copy link

You don't need that proc with mysql 5.7. Just use ST_Distance_Sphere

@habedi
Copy link

habedi commented Sep 19, 2019

Does this function still work correctly when either of latitude or longitude or both of them are negative?

@Usse
Copy link
Author

Usse commented Sep 19, 2019

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

@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