{{ message }}

Instantly share code, notes, and snippets.

# statickidz/nearby-coordinates.sql

Last active Nov 30, 2020
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 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.