CODEWITHSUNDEEP
phpmysqlgeolocation
roxrook
roxrook

Reputation: 13853

How to retrieve Longitude and Latitude from database within a given radius?

This is my database table (spots).

enter image description here

Given a point on map P(longitude, latitude), what I want to retrieve from the database is the set of points within a given radius where P is the center. I tried a simple query using the simple formula of Longitude and Latitude, unfortunately the result is always null :( . What am I doing wrong here? Any idea? 

$sql = "SELECT * FROM $tbl_name 
WHERE ACOS(SIN('".$latitude."') * SIN(spots.latitude) + COS('".$latitude."') * COS(spots.latitude) * COS(spots.longitude - '".$longitude    ."')) * 6371 <= '".$radius."'";

Upvotes: 0

Views: 3150

Answers (3)

roxrook
roxrook

Reputation: 13853

Based on Cameron S's & MrGlass's ideas, and a bit searching over the Internet, I found one working solution:

$R = 6371;
$max_lat = $lat + rad2deg($r/$R);
$min_lat = $lat - rad2deg($r/$R);

$max_lon = $lon + rad2deg($r/$R/cos(deg2rad($lat)));
$min_lon = $lon - rad2deg($r/$R/cos(deg2rad($lat)));
$lat = deg2rad($lat);
$lon = deg2rad($lon);

$sql = "
select id, name, description, latitude, longitude, 
       acos(sin($lat)*sin(radians(latitude)) + cos($lat)*cos(radians(latitude))*cos(radians(longitude)-$lon)) * $R as D
from (
  select id, name, description, latitude, longitude 
  from $tbl_name 
  where latitude > $min_lat and latitude < $max_lat
    and longitude > $min_lon and longitude < $max_lon
  ) as first_cut 
where acos(sin($lat)*sin(radians(latitude)) + cos($lat)*cos(radians(latitude))*cos(radians(longitude) - $lon)) * $R < $r
order by D";

Upvotes: 2

MrGlass
MrGlass

Reputation: 9262

I would calculate the max/min possible values for latitude and longitude in PHP. Then, you can select all the locations within that range with pretty simple SQL:

$sql = "SELECT * from locations where latitude >= $minLat and latitude <= $maxLat and longitude >= $minLong and longitude <= $maxLong

Upvotes: 0

Cameron S
Cameron S

Reputation: 2301

The way I would solve this problem is create a geographic "box", simply by choosing lat between lat + max lat distance using radius and lat - max lat distance (the same for longitude).

Then, I would calculate in PHP which of those points actually satisfy your criteria (the circle inscribed in the box) rather than perform a complex calculation on every row. This should make the calculation easier and increase performance.

Here is a C# example of the query (lon1 and lat1 being the center lat and lon). I am not proficient enough in PHP to create a PHP example and if someone would like to add one that would be great:

        double minLon = lon1 - maxDistance / Math.Abs(Math.Cos((lat1 / 180) * Math.PI) * 69);
        double maxLon = lon1 + maxDistance / Math.Abs(Math.Cos((lat1 / 180) * Math.PI) * 69);
        double minLat = lat1 - (maxDistance / 69);
        double maxLat = lat1 + (maxDistance / 69);
        String query = "SELECT [idColumn], [latitude], [longitude] FROM [db].[dbo].[table] WHERE [latitude] BETWEEN " + minLat + " AND " + maxLat + " AND [longitude] BETWEEN " + minLon + " AND " + maxLon;

And here is how I calculated the distance from these points:

        double r = 3956.087107103049;
        return (r * 2) * Math.Asin(Math.Sqrt(Math.Pow(Math.Sin((lat1 - lat2) / 2), 2) + Math.Cos(lat1) * Math.Cos(lat2) * Math.Pow(Math.Sin((lon1 - lon2) / 2), 2)));

Upvotes: 4

Related Questions