Andre Lewis on Tech

Sure you do. Here's a sample Rails action which finds the ten closest cities to the latitude/longitude you provide. Obviously, this presumes your City model has the fields latitude and longitude. These examples are for MySQL 5.

def nearby_cities
  lat,lng=params[:ll].split(',').collect{|e|e.to_f}
  
  #convert to radians
  lat_radians=(lat/180) * Math::PI
  lng_radians=(lng/180) * Math::PI

  # 3963 is the earth's radius, more or less, in miles.
  # Which means that the distances you get out of this are in miles.
  # Want a different measure? Kilometers=6378. Nautical miles=3444. Furlongs=29544
  earths_radius = 3963
  table_name=City.table_name 
  distance_sql=<<-SQL_END  (acos(cos(#{lat_radians})*cos(#{lng_radians})* 
 cos(radians(#{table_name}.latitude))*cos(radians(#{table_name}.longitude)) +
cos(#{lat_radians})*sin(#{lng_radians})*cos(radians(#{table_name}.latitude))* 
sin(radians(#{table_name}.longitude)) +
sin(#{lat_radians})*sin(radians(#{table_name}.latitude))) * #{earths_radius})
  SQL_END

  cities = City.find(:all,
        :select=>"*, #{distance_sql} as distance",
        :order => 'distance asc',
        :limit => 10)
        
  render :text=>cities.collect{|c|c.attributes}.to_json
end

The key thing is the distance_sql, which does all the heavy lifting. You can take this basic formula and use it verbatim for any model with latitude/longitude columns -- just make sure you change the table_name to match your model.

Find within Radius

So, what if you want to constrain the results to a given radius? No problem. This finds all cities within a 25 mile radius:

  cities = City.find(:all,
        :select=>"*, #{distance_sql} as distance",
        :conditions=>"#{distance_sql} <= 25",
        :order => 'distance asc')

Note that when you use distance in :conditions, you have to repeat distance_sql, whereas if you use distance in your order clause, you can simply reference the alias ("distance") which you gave the computed column in the select clause.

Utilizing that "distance" column

In the :select clause, you tacked an extra column -- distance -- on there. You can access this field just as you would any other (say from your .rhtml template): <%=city.name%> is <%=city.distance%> miles away.. Note that any of these add-on columns are treated by default as strings. If you want to format it as an actual number, you'll need to do something like <%=city.distance.to_f.ceil%> miles. 

Continue reading "Hey, want to sort your query by geographic distance?"»