MySQL - Find points within radius from database

Question

I have a table which has a POINT column containing the latitude and longitude of various locations.

I then also have a users location from geo-location in the browser.

What I need to be able to do is find all records from the table where the POINT value in the is within a 10 km radius (or X km radius), ordered by distance with the closest first.

My table has a SPATIAL index on the POINT column.

Answer 1

I'm currently working on a project where I'm calculating distances between multiple locations. I'm using the following query for selecting object_id's which are within a given radius.

SELECT id, 
( 6371 * 
    ACOS( 
        COS( RADIANS( db_latitude ) ) * 
        COS( RADIANS( $user_latitude ) ) * 
        COS( RADIANS( $user_longitude ) - 
        RADIANS( db_longitude ) ) + 
        SIN( RADIANS( db_latitude ) ) * 
        SIN( RADIANS( $user_latitude) ) 
    ) 
) 
AS distance FROM the_table HAVING distance <= $the_radius ORDER BY distance ASC"

I can't explain the ACOS formula itself because I got it from research.

db_latitude = database latitude field
db_longitude = database longitude field
$user_latitude = browser latitude coördinate
$user_longitude = browser longitude coördinate
$the_radius = the radius that you want to search in

This is in kilometers.

Answer 2

The query below actually worked for me :

$query = "SELECT *,
    ( 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 parkings 
          HAVING distance <= ".$radius." ORDER BY distance ASC";

  $stmt = $conn->execute($query);

  $rows = $stmt->fetchAll('assoc');

where: $user_lat and $user_lng is browser's lat and lng, $radius = 10, table name is parkings