Instantly share code, notes, and snippets.

# statickidz/nearby-coordinates.sql

Last active January 31, 2024 20:31
Show Gist options
• Save statickidz/8a2f0ce3bca9badbf34970b958ef8479 to your computer and use it in GitHub Desktop.
Ordering with SQL by nearest latitude & longitude coordinates (MySQL & SQLite)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
 --- 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 commented May 29, 2018 • edited

Is this distance in meters?

### dinhlamvn commented Jun 5, 2018

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

### 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 commented Jun 6, 2019

Has it been confirmed the distance is km ?

### hanuor commented Jan 16, 2020

This is really very helpful. Thanks a tonne!

### 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 commented Mar 17, 2020

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

### 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.

### zoren commented Mar 27, 2021 • edited

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

### mark-baumann commented Dec 7, 2021

Thank you!!! You made my day xD

Thanks!