From: Tom Hughes <tom@compton.nu>
Date: Fri, 5 Jun 2009 09:30:31 +0000 (+0000)
Subject: More work on optimisation the location of nearby users - it turns out
X-Git-Tag: live~7178
X-Git-Url: https://git.openstreetmap.org/rails.git/commitdiff_plain/5de81dc4b0f514695fe9f24c7c0cf1437137ee1a

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 5fc566064..241c1396f 100644
--- 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)
-      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
diff --git a/lib/osm.rb b/lib/osm.rb
index 5be2da56a..789a5f439 100644
--- 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
+
+    # 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