17

Possible Duplicate:
MySQL latitude and Longitude table setup

I know this question has probably been asked many times, I've researched a lot and I need help with specific thing.

Lets say I have a form and user enters longitude and latitude, and I have a database which has table containing longitudes and latitudes, how would I search for a point or points in that table that are within 15 miles of radius?

Community
  • 1
  • 1
Grigor
  • 4,139
  • 10
  • 41
  • 79

2 Answers2

45

You can use the formula that calculates distances between two points. For example:

function get_distance($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') { 
    $theta = $longitude1 - $longitude2; 
    $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + 
                (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * 
                cos(deg2rad($theta))); 
    $distance = acos($distance); 
    $distance = rad2deg($distance); 
    $distance = $distance * 60 * 1.1515; 
    switch($unit) { 
        case 'Mi': 
            break; 
        case 'Km' : 
            $distance = $distance * 1.609344; 
    } 
    return (round($distance,2)); 
}

You can also do something like:

$query = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * 
            sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * 
            cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)* 
            pi()/180))))*180/pi())*60*1.1515
        ) as distance 
        FROM `MyTable` 
        HAVING distance >= ".$distance.";
claire
  • 813
  • 11
  • 16
  • gives me an error Unknown column 'distance' in 'where clause' – Grigor Dec 22 '11 at 04:29
  • 1
    what version of mysql you have? Try changing WHERE to HAVING. mysql 5.x has some problems with where clause. http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html – claire Dec 22 '11 at 06:09
  • 3
    For your all information, the MySQL-Query calculates the distance in miles. To determine the distance in km you have to multiply it by 1.609344 (like in the PHP example). – cldrr Dec 18 '13 at 21:06
  • @claire is this the distance in miles? How can I alter this to metres? – user2363025 May 29 '15 at 10:24
  • @claire would I replace *60*1.1515 with *(60*1.1515*1.609344)/1000)? – user2363025 May 29 '15 at 10:28
  • I think you have omitted the closing double quote at the end of the query. – David Addoteye Aug 25 '15 at 08:28
  • @user2363025 to convert into meters, you need to replace 1.609344 with 1609.344 – Maulik Vora Oct 16 '17 at 07:47
  • Hey @claire, can you tell what is the name of the formula? I find your response very useful, but would be nice to know why it works :) – Azula Nov 05 '20 at 12:55
3

If you have the Lat/Lon of two points, you can get delta Lat and delta Lon and convert this to a distance along latitude and a distance along longitude, and use Pythagorean theorem.

Have you looked at pages like http://www.movable-type.co.uk/scripts/latlong.html? This has several ways of computing distance. So as you go through your list of points, you use the formula to calculate the distance from each point to the point of interest and keep only those that satisfy R<15 miles.

Oliver
  • 27,510
  • 9
  • 72
  • 103