Ordering with SQL by nearest latitude & longitude coordinates (MySQL & SQLite)

nearby-coordinates.sql

---
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;

Is this distance in meters?

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

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

Has it been confirmed the distance is km ?

This is really very helpful. Thanks a tonne!

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.

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

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.

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

FYI: sqlite 3.35.0 has acos and many other functions. https://sqlite.org/releaselog/3_35_0.html

Thank you!!! You made my day xD

Thanks!