More work on optimisation the location of nearby users - it turns out

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.

diff --git a/app/models/user.rb b/app/models/user.rb
index 5fc5660641269f13f543d55d190f185431f82c8a..241c1396ffeca73a51e25a40a72a779ee41a67f7 100644 (file)
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -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)
     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
     else
       nearby = []
     end

diff --git a/lib/osm.rb b/lib/osm.rb
index 5be2da56a13c050be1d395b18ecae458ed9a0d4d..789a5f4398b72dadb1a186e39f352155766638eb 100644 (file)
--- a/lib/osm.rb
+++ b/lib/osm.rb
@@ -334,6 +334,11 @@ module OSM
       maxlon = (@lon + lonradius) * 180 / PI
       return { :minlat => minlat, :maxlat => maxlat, :minlon => minlon, :maxlon => maxlon }
     end
       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
   end
 
   class GeoRSS