Retrieving items matching a certain GPS coords + distance

Question

I currently have a MySQL database structure like this (simplified):

Entry Title

Entry meta Latitude Longitude

I want my users to fetch all the records that have a lat/lng inside a circle defined by a coord + distance (may also be a square, if thats easier). I'm just not sure where to start. I've been told there is a mathematical equation, but not sure where to find that.

I'm using PHP/MySQL combo and would like to query the database. The database contains about 100.000 rows (which will be doubled by the end of this month)

Look at [this](http://stackoverflow.com/questions/27928/how-do-i-calculate-distance-between-two-latitude-longitude-points). — user2959229, Nov 11 '13 at 12:05

score 0 · Answer 1 · answered Nov 11 '13 at 12:11

0

I currently have this query which is working for this case (a WordPress database). Good luck ;)

https://gist.github.com/nielsvr/7412291

answered Nov 11 '13 at 12:11

Niels van Renselaar

1,512
1
9
22

score 0 · Answer 2 · answered Nov 11 '13 at 12:50

The following SQL query uses Spherical Law of Cosines to calculate the distance between a coordinate and coordinates in a table.

d = acos( sin(φ1).sin(φ2) + cos(φ1).cos(φ2).cos(Δλ) ).R

The query uses SQL Math functions

"SELECT Id,Lat,Long,(6367 * acos( cos( radians(center_lat) )
  * cos( radians( Lat ) ) * cos( radians( Long ) - radians(center_lng) )
  + sin( radians(center_lat) ) * sin( radians( Lat ) ) ) ) AS distance FROM table 
  HAVING distance < radius ORDER BY distance ASC LIMIT 0 , 20"

Using PDO instead of deprecated mysql_ functions try

// Prepare statement
    $stmt = $dbh->prepare("SELECT  name, lat, lng, (6367 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance FROM gbarea HAVING distance < ? ORDER BY distance ASC LIMIT 0 , 20");
    // Assign parameters
    $stmt->bindParam(1,$center_lat);
    $stmt->bindParam(2,$center_lng);
    $stmt->bindParam(3,$center_lat);
    $stmt->bindParam(4,$radius);
    //Execute query
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    while($row = $stmt->fetch()) {
        echo "Name : ".$row['name'].", ";
            echo "Lat : ".$row['lat'].", " ;
            echo "Lng : ".$row['lng'].", ";
            echo "Distance : ".$row['distance'];
        echo "<br>";
        }

Retrieving items matching a certain GPS coords + distance

2 Answers2