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

5
votes

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?

5
phpmysqllocationcoordinates

5 Answers

3
votes

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

2
votes
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. ;)

0
votes

There is a formula to compute the distance between two lat/lon coordinates. Beware though -- it's rather computationally expensive, so if you have lots of incidents, you'll want to be smart about it. First up, read about the maths involved.

As for PHP code, a quick google turned up this link, which looks like it probably works.

Now, you probably want to use some more efficient method to divide your incident points into two sets: those points that might be within range (hopefully a smallish set), and those that you can discount entirely. Checking more than a few dozen incident coordinates is likely to be a performance issue.

I don't have any particular insight into that, but if nobody else comes along with something clever, I'll try to come up with something myself later, time permitting.

0
votes

I did a quick search and turned up this blog post which gives a good explanation and SQL to select records in a given radius.

In the comments, he suggests "For speed on large datasets you probably want to grab a square block around the origin point first by adding a mile or so to and from both lat/lon for origin and then using the above as a subselect to work from the middle out" which sounds to me like the way to go.

0
votes
 SELECT 3963 * ACOS(
    SIN(RADIANS($pointAlat)) * SIN(RADIANS($pointAlat)) + COS(RADIANS($pointAlat))  * COS(RADIANS($pointBlat)) * COS(RADIANS($pointAlong) - RADIANS($pointBlong)))
 AS
 distance;

Also, if you're looking for a good read/tutorial on this.. Check here http://www.phpfreaks.com/forums/index.php/topic,208965.0.html