Get nearby locations on Google Maps using spatial MySQL data

Question

I have a database with a list of stores with latitudes and longitudes of each. So, based on the current (lat, lng) place that I entered, I would like to get a list of objects from those that are in a radius, for example, 1 km, 5 km, etc.

What should be the algorithm? I need PHP code for the algorithm itself.

Answer 1

You just need to use the following query.

For example, you have the latitude and longitude of entering 37 and -122 in degrees. And you want to search for users within a radius of 25 miles from the current given latitude and longitude.

SELECT item1, item2, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM geocodeTable HAVING distance < 25 ORDER BY distance LIMIT 0 , 20; 

If you want the search distance in km, replace 3959 with 6371 in the query above.

You can also do this, for example:

• Select all latitude and longitude

• Then calculate the distance for each record.

• The above process may be performed with multiple redirection.

You can use a stored procedure to optimize the request.

And this can also help you.

Answer 2

You must select a database that is spatially included, such as mysql or postgresql , and then you can use some of the predefined functions that they provide. If you want to do it manually, check this one for heads-ups.

Answer 3

If you are looking for a PHP code to calculate the distance between two sets of coordinates, here the class that I adapted will calculate the distance in kilometers. However, if you are using a database, I would suggest that you study whether your database can work with spatial computing (I know that SQL Server and MySQL, from my point of view).

Here is an interesting link for a SQL solution that you can check. Optimizing haversine SQL query formula in PHP

 class Distance { /** * Mean raidus of the earth in kilometers. * @var double */ const RADIUS = 6372.797; /** * Pi divided by 180 degrees. Calculated with PHP Pi constant. * @var double */ const PI180 = 0.017453293; /** * Constant for converting kilometers into miles. * @var double */ const MILES = 0.621371192; /** * Calculate distance between two points of latitude and longitude. * @param double $lat1 The first point of latitude. * @param double $long1 The first point of longitude. * @param double $lat2 The second point of latitude. * @param double $long2 The second point of longitude. * @param bool $kilometers Set to false to return in miles. * @return double The distance in kilometers or miles, whichever selected. */ public static function getDistance($lat1, $long1, $lat2, $long2, $kilometers = true) { $lat1 *= self::PI180; $long1 *= self::PI180; $lat2 *= self::PI180; $long2 *= self::PI180; $dlat = $lat2 - $lat1; $dlong = $long2 - $long1; $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlong / 2) * sin($dlong / 2); $c = 2 * atan2(sqrt($a), sqrt(1 - $a)); $km = self::RADIUS * $c; if($kilometers) { return $km; } else { return $km * self::MILES; } } } //example echo Distance::getDistance(40.686748, -89.555054, 40.453078, -88.939819);