Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@kinginblue kinginblue commented May 27, 2016

Thanks a lot.

@zafe

This comment has been minimized.

Copy link

@zafe zafe commented Mar 16, 2017

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

@ozhantr

This comment has been minimized.

Copy link

@ozhantr ozhantr commented Mar 20, 2017

Thx.

@richardstuart

This comment has been minimized.

Copy link

@richardstuart richardstuart commented Jul 28, 2017

spot on, cheers

@usamamashkoor

This comment has been minimized.

Copy link

@usamamashkoor usamamashkoor commented Aug 6, 2017

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.

@ZaidBarghouthi

This comment has been minimized.

Copy link

@ZaidBarghouthi ZaidBarghouthi commented Mar 21, 2018

Thank you!

@miking7

This comment has been minimized.

Copy link

@miking7 miking7 commented May 23, 2018

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

@pushpanktugnawat

This comment has been minimized.

Copy link

@pushpanktugnawat pushpanktugnawat commented May 29, 2018

Is this compatible with mysql 5.7 ?

Please confirm.

@cjohnsoninc

This comment has been minimized.

Copy link

@cjohnsoninc cjohnsoninc commented Jul 10, 2018

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

@habedi

This comment has been minimized.

Copy link

@habedi habedi commented Sep 19, 2019

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

@Usse

This comment has been minimized.

Copy link
Owner Author

@Usse 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

This comment has been minimized.

Copy link

@habedi habedi commented Sep 19, 2019

Thanks!

@free-developers

This comment has been minimized.

Copy link

@free-developers free-developers commented Sep 27, 2019

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

@habedi

This comment has been minimized.

Copy link

@habedi 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