MySQL calculate distance between two latitude/longitude coordinates

distance.sql

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.

Thanks a lot.

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

Thx.

spot on, cheers

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.

Thank you!

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

Is this compatible with mysql 5.7 ?

Please confirm.

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

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

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

Thanks!

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

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