8

I have a database with a list of stores with latitudes and longitudes of each. So based on the current (lat, lng) location that I input, I would like to get a list of items from those within some radius like 1 km, 5km etc?

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

halfer
  • 19,824
  • 17
  • 99
  • 186
Atif
  • 10,623
  • 20
  • 63
  • 96

3 Answers3

32

You just need use following query.

For example, you have input latitude and longitude 37 and -122 in degrees. And you want to search for users within 25 miles from 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 search distance in kms, then replace 3959 with 6371 in above query.

You can also do this like:

  1. Select all Latitude and longitude

  2. Then calculate the distance for each record.

  3. The above process can be done with multiple redirection.

For optimizing query you can use Stored Procedure.

And this can also help you.

halfer
  • 19,824
  • 17
  • 99
  • 186
Gaurav
  • 28,447
  • 8
  • 50
  • 80
  • A little warning as this will get out of hand if the table with points increase in size. You will end up doing the calculations for each and every point in the table for every query. – Niklas Wulff Feb 15 '11 at 16:24
  • Stored procedure can be used for this. – Gaurav Feb 15 '11 at 17:00
  • you are the nearest gaurav, but any help on optimizing this would be really helpful :) – Atif Feb 17 '11 at 11:32
  • check this http://www.arubin.org/files/geo_search.pdf Procedure can be helpful. Let me know if you can do it or not. – Gaurav Feb 17 '11 at 12:02
  • This is a great tut on doing it... http://code.google.com/apis/maps/articles/phpsqlsearch.html#findnearsql – Peter Feb 18 '11 at 01:44
6

You should choose a database that is spatially enabled like mysql or postgresql and then you can use some of the ready functions they providing. Else if you want to do it manually check this for heads up.

Community
  • 1
  • 1
Argiropoulos Stavros
  • 9,436
  • 11
  • 61
  • 79
6

If you're looking for PHP code for calculating distance between two sets of coordinates here's a class that I adapted that will calculate the distance in kilometers. However, if you're using a database I would suggest that you look into whether or not your database is capable of spacial computations (I know that SQL Server and MySQL are, off the top of my head).

Here's an interesting link for a SQL solution that you might want to check out. Optimising a haversine formula SQL call 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);
Community
  • 1
  • 1
Jarrod Nettles
  • 6,193
  • 6
  • 28
  • 46
  • so do i need the databse to support some special functions? Cant it directly be done using PHP alone? if I save lat/lng coordinates as 2 fields in DB – Atif Feb 12 '11 at 18:54
  • The code I posted will give you the distance between two coordinates. – Jarrod Nettles Feb 14 '11 at 13:46