Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Ordering with SQL by nearest latitude & longitude coordinates (MySQL & SQLite)
---
METHOD 1
This should roughly sort the items on distance in MySQL, and should work in SQLite.
If you need to sort them preciser, you could try using the Pythagorean theorem (a^2 + b^2 = c^2) to get the exact distance.
---
SELECT *
FROM table
ORDER BY ((lat-$user_lat)*(lat-$user_lat)) + ((lng - $user_lng)*(lng - $user_lng)) ASC
---
METHOD 2
---
SELECT
id, (
6371 * acos (
cos ( radians($user_lat) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians($user_lng) )
+ sin ( radians($user_lat) )
* sin( radians( lat ) )
)
) AS distance
FROM table
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;
@MessiasLima

This comment has been minimized.

Copy link

@MessiasLima MessiasLima commented May 29, 2018

Is this distance in meters?

@dinhlamvn

This comment has been minimized.

Copy link

@dinhlamvn dinhlamvn commented Jun 5, 2018

I think this is distance in km, because it square with 6371, which radius of earth in km

@MassivDash

This comment has been minimized.

Copy link

@MassivDash MassivDash commented Jun 5, 2019

You can you ABS (absolute value) instead of multiplying the values to get rid of any negative values.

ORDER BY ABS(lat-$user_lat) + ABS(lng - $user_lng) ASC

@Dillonsmart

This comment has been minimized.

Copy link

@Dillonsmart Dillonsmart commented Jun 6, 2019

Has it been confirmed the distance is km ?

@hanuor

This comment has been minimized.

Copy link

@hanuor hanuor commented Jan 16, 2020

This is really very helpful. Thanks a tonne!

@ashleedawg

This comment has been minimized.

Copy link

@ashleedawg ashleedawg commented Feb 19, 2020

Has it been confirmed the distance is km ?

Yes, "Method 2" is in km. You can tell since it uses the value 6371, which is the approx radius of the Earth in kilometres.

To instead get the result in miles, just substitute the value 3959, which is the radius of the Earth in miles.

"Method 1" is in neither unit; it is just an approximate/fast method of sorting by shortest distance, but it doesn't actually return a distance. It merely does a rough calculation as if the planet was a 2-dimensional grid (and therefore should be the preferred method of "Flat Earthers".) 😜

Both methods are "straight line" distance and neither one accounts for elevation changes or the fact that the planet is not a perfect sphere.
For example, Method 2 calculates the distance between Toronto and Vancouver as 3354km, which is about 1000km less than actual driving distance, or 900km less than walking distance. "Actual travel distance" can only be calculated with the use of detailed map data, such as Google Maps Distance Matrix API.

@concat1911

This comment has been minimized.

Copy link

@concat1911 concat1911 commented Mar 17, 2020

SQLite doesn't have acos function so Method 2 only works on Mysql

@ashleedawg

This comment has been minimized.

Copy link

@ashleedawg ashleedawg commented Mar 18, 2020

SQLite doesn't have acos function

You can implement your own functions in SQLite. For example, math functions can [apparently] be added with extension-functions.c, downloadable from sqlite.org, or else a search of Stack Overflow has other options such as this one.

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.