1

I am using this SQL query to get data that is nearest to GPS coordinates.

SELECT geo_latitude, geo_longitude, geo_name, geo_country_code AS country, 
  (DEGREES(
    ACOS(
      SIN(RADIANS(47.470779)) * SIN(RADIANS(geo_latitude)) + 
      COS(RADIANS(47.470779)) * COS(RADIANS(geo_latitude)) * 
      COS(RADIANS(-87.890699 - geo_longitude))
    ) 
  ) * 60 * 1.1515)
  AS distance FROM `MyDatabase`.`allCountries` ORDER BY distance ASC LIMIT 20 ;

This query retrieves all the data irrespective of the distance. I would like it to display the data only within a radius of 10 miles. How can i achieve this?

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Vicky V
  • 11
  • 1
  • 4

1 Answers1

2

There's two ways I've found. One way is to do a direct SQL query with a whole lot of math in the 'where' section, much like you've tagged for your AS distance section of the SQL. Essentially - conceptually - 'where distance < 10'

However, I've found that slows down my request ... so what I've done in addition is to pull out the square instead of the circle from the database. Simply put: figure out the 'ten miles west/north/east/south' coords, then put in WHERE geo_latitude < X1 AND geo_latitude > x2 AND geo_longitude < x3 AND geo_longitude > x4 to put a hard limit on what I get back. Then, if you really need to be that precise, cut off the corners by only displaying if( result.distance < 10 ){

Or to put it another way:

x1 = Coords + 10 miles north  
x2 = Coords + 10 miles south  
x3 = Coords + 10 miles east  
x4 = Coords + 10 miles west

by whatever means you're using to indicate '10 miles north', and then use that to pull out a 'square' of 10-miles north, south, west, and east from the center point. As long as your geo_latitude and geo_longitude columns are indexed, this should be a fast database call.

Wolfman Joe
  • 799
  • 1
  • 8
  • 23
  • i could understand the first idea !!! but wat do u mean by that x1,x2,x3,x4 in that second ? : wolfman joe – Vicky V Mar 27 '13 at 15:42
  • I've modified the answer. I hope that helps clarify things? – Wolfman Joe Mar 27 '13 at 16:04
  • And how do you compute x1 = Coords + 10 miles north – Tomas Kubes Dec 29 '13 at 21:28
  • @qub1n The original post has the equation one can use to calculate distance based on lat / lon. – Wolfman Joe Jan 03 '14 at 14:19
  • @WolfmanJoe: The original equation compares two/several gps points without any knowledge, how many miles is the resulted number. I just ask, how to implement miles/km to that equation. – Tomas Kubes Jan 03 '14 at 15:08
  • You're feeding in lat/lon and getting out an m on the other side of the equation. Well, the lat or lon remains constant, so that variable becomes a constant. Then you rebalance the equation so that it's based on m and finds the other lat/lon variable - and for how to do that I reference you to high school math classes. – Wolfman Joe Jan 03 '14 at 17:05