PHP MySQL get locations in radius user's location from GPS

Question

I have in my database car incidents for example. These incidents have a latitude and longitude. On a mobile using the GPS, I get the user's location with his coordinates. The user can select a radius that he wants to know if there are incidents around him. So let's say he want to know incidents 2 miles around him.

So I send from the phone to a web service the user's latitude, longitude and the radius he selected. I need to make a SQL query to get the incidents 2 miles around the user.

Do you have any idea how to do that?

Answer 1

Calculating the distance is pretty computationally expensive, as others have said. Returning huge datasets is also not a very good idea - specially considering PHP isn't that great in performance.

I would use a heuristic, like approximating the distance with simple addition and subtraction.

1 minute = 1.86 kilometers = 1.15 miles

Just search the db with incidents within that range (effectively a square, rather than a circle), and then you can work on those with PHP.

---

EDIT: Here's an alternative; an approximation that's way less computationally expensive:

Approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1) 
and y = 53.0 * (lon2 - lon1) 

You can improve the accuracy of this approximate distance calculation by adding the cosine math function:

Improved approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1) 
and y = 69.1 * (lon2 - lon1) * cos(lat1/57.3) 

Source: http://www.meridianworlddata.com/Distance-Calculation.asp

---

EDIT 2: I ran a bunch of tests with randomly generated datasets.

• The difference in accuracy for the 3 algorithms is minimal, especially at short distances
• The slowest algorithm (the one with the whole bunch of trig functions) is 4x slower than the other two.

Definitely not worth it. Just go with an approximation.

Code is here: http://pastebin.org/424186

Answer 2

function distance($lat1,$lon1,$lat2,$lon2,$unit)
  {
  $theta=$lon1-$lon2;
  $dist=sin(deg2rad($lat1))*sin(deg2rad($lat2))+cos(deg2rad($lat1))*cos(deg2rad($lat2))*cos(deg2rad($theta));
  $dist=acos($dist);
  $dist=rad2deg($dist);
  $miles=$dist*60*1.1515;
  $unit=strtoupper($unit);
  if ($unit=="K")
    {
    return ($miles*1.609344);
    }
    else if ($unit=="N")
    {
    return ($miles*0.8684);
    }
    else
    {
    return $miles;
    }
  } // end function

$x_lat=center_of_serach;
$x_lon=center_of_serach;
$_distance=some_distance_in_miles;
$query1 = "SELECT * FROM `location_table` WHERE somefield=somefilter";
$result=mysql_db_query($db_conn, $query1);
$max_rows=mysql_num_rows($result); 
if ($max_rows>0)
  {
while ( $data1=mysql_fetch_assoc($result) )
  {
  if ( distance($x_lat,$x_lon,$data1['lat'],$data1['lng'],'m')<$_distance )
    {
    //do stuff
    }
  }

Its faster to fetch all the data and run it through a function, rather than use a query if your database isn't too big.

It works for Kilos and Nautical miles too. ;)