0

I have a mysql table which contain GPS coordinate latitude and longitude.

I would like to make a request to get records near a given position.

I've tried 2 requests and merged the results but it's not correct...

//Req 1 - bigger than my position 
$req1 = mysql_query('SELECT * FROM table WHERE latitude >= 47.6621549 and longitude >= 2.3547128 ORDER BY latitude, longitude');

 //Req 2 - samller than my position 
$req2 = mysql_query('SELECT * FROM table WHERE latitude <= 47.6621549 and longitude <= 2.3547128 ORDER BY latitude, longitude');

Is it possible to make it with a single request ? With this, i would like to retrieve the distance to the given position with gmap distance API, i think it's not a problem...

Thanks for your answer.

jlafforgue
  • 287
  • 2
  • 5
  • 15
  • 1
    This answer will likely help you: http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points – shannonman Apr 02 '13 at 08:40
  • This is more likely related to your question http://dba.stackexchange.com/questions/4214/how-to-best-implement-nearest-neighbour-search-in-mysql – hodl Apr 02 '13 at 09:13

1 Answers1

2

try this.

$range = 2;/* 2 mi */   
mysql_query("SELECT t1.*,
    (
        3956 * 2 * 
        ASIN(
            SQRT(
                POWER(SIN(($lat - t1.latitude) * pi()/180 / 2), 2) 
                + COS($lat * pi()/180) * COS(t1.latitude * pi()/180) 
                * POWER(SIN(($lng - t1.longitude) * pi()/180 / 2), 2)
            )
        )
    ) AS distance 
    FROM table
    HAVING distance < $range
    ORDER BY distance ASC");
hodl
  • 1,420
  • 12
  • 21