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 commented May 27, 2016

Thanks a lot.

@zafe

This comment has been minimized.

Copy link

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 commented Mar 20, 2017

Thx.

@richardstuart

This comment has been minimized.

Copy link

richardstuart commented Jul 28, 2017

spot on, cheers

@usamamashkoor

This comment has been minimized.

Copy link

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 commented Mar 21, 2018

Thank you!

@miking7

This comment has been minimized.

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

This comment has been minimized.

Copy link

pushpanktugnawat commented May 29, 2018

Is this compatible with mysql 5.7 ?

Please confirm.

@cjohnsoninc

This comment has been minimized.

Copy link

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 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 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 commented Sep 19, 2019

Thanks!

@free-developers

This comment has been minimized.

Copy link

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 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
You can’t perform that action at this time.