More work on optimisation the location of nearby users - it turns out
authorTom Hughes <tom@compton.nu>
Fri, 5 Jun 2009 09:30:31 +0000 (09:30 +0000)
committerTom Hughes <tom@compton.nu>
Fri, 5 Jun 2009 09:30:31 +0000 (09:30 +0000)
that getting the database to do the filtering is much better as it
avoids us constructing thousands of user objects only to then throw
most of them away again.

app/models/user.rb
lib/osm.rb

index 5fc5660..241c139 100644 (file)
@@ -98,9 +98,9 @@ class User < ActiveRecord::Base
     if self.home_lon and self.home_lat 
       gc = OSM::GreatCircle.new(self.home_lat, self.home_lon)
       bounds = gc.bounds(radius)
-      nearby = User.find(:all, :conditions => ["visible = ? and home_lat between #{bounds[:minlat]} and #{bounds[:maxlat]} and home_lon between #{bounds[:minlon]} and #{bounds[:maxlon]} and data_public = ? and id != #{self.id}", true, true])
-      nearby = nearby.sort_by { |u| gc.distance(u.home_lat, u.home_lon) }.first(num)
-      nearby.delete_if { |u| gc.distance(u.home_lat, u.home_lon) > radius }
+      sql_for_distance = gc.sql_for_distance("home_lat", "home_lon")
+      nearby = User.find(:all, 
+                         :conditions => ["id != ? AND visible = ? AND data_public = ? AND #{sql_for_distance} <= ?", id, true, true, radius], :order => sql_for_distance, :limit => num)
     else
       nearby = []
     end
index 5be2da5..789a5f4 100644 (file)
@@ -334,6 +334,11 @@ module OSM
       maxlon = (@lon + lonradius) * 180 / PI
       return { :minlat => minlat, :maxlat => maxlat, :minlon => minlon, :maxlon => maxlon }
     end
+
+    # get the SQL to use to calculate distance
+    def sql_for_distance(lat_field, lon_field)
+      "6372.795 * 2 * asin(sqrt(power(sin((radians(#{lat_field}) - #{@lat}) / 2), 2) + cos(#{@lat}) * cos(radians(#{lat_field})) * power(sin((radians(#{lon_field}) - #{@lon})/2), 2)))"
+    end
   end
 
   class GeoRSS