Skip to content

Instantly share code, notes, and snippets.

@voratham
Forked from statickidz/nearby-coordinates.sql
Created October 27, 2022 16:07
Show Gist options
  • Save voratham/bd3ab3f62b04dc63bc1be64aa0b20c91 to your computer and use it in GitHub Desktop.
Save voratham/bd3ab3f62b04dc63bc1be64aa0b20c91 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment