Skip to content

Instantly share code, notes, and snippets.

@voratham

voratham/nearby-coordinates.sql

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.
Download ZIP

Revisions

  1. @statickidz statickidz revised this gist Mar 31, 2016. No changes.
  2. @statickidz statickidz revised this gist Mar 31, 2016. No changes.
  3. @statickidz statickidz revised this gist Mar 31, 2016. 1 changed file with 21 additions and 2 deletions.
    23 changes: 21 additions & 2 deletions nearby-coordinates.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,27 @@
    ---
    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
    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;
  4. @statickidz statickidz revised this gist Mar 31, 2016. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions nearby-coordinates.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,8 @@
    ---
    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
  5. @statickidz statickidz created this gist Mar 31, 2016.
    3 changes: 3 additions & 0 deletions nearby-coordinates.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    SELECT *
    FROM table
    ORDER BY ((lat-$user_lat)*(lat-$user_lat)) + ((lng - $user_lng)*(lng - $user_lng)) ASC