I want to know which thingy is centered on a given city. Both thingy
and city have a lat and a lng attribute, ad lat and lng are both
floats in the mysql database.
knowing how clever ActiveRecord::Base is I wanted to do:
City.find_by_lat_and_lng(self.lat, self.lng)
but that interprets lat and lng as
SELECT * FROM cities WHERE (cities.lat = ‘50.12’ AND cities.lng = ‘-5.7’) LIMIT 1
SELECT * FROM cities WHERE (lat = 50.13 AND lng = -5.56) LIMIT 1
would work but it returns nothing.
what I had to do was
City.find(:first, :conditions => [ “lat LIKE ? AND lng LIKE ?”,
self.lat, self.lng ])
which becomes
SELECT * FROM cities WHERE (lat LIKE 50.13 AND lng LIKE -5.56)
LIMIT 1
and works.
Surely the first case is an ActiveRecord::Base problem in that it
ought to interpret a float as a float and not a string (LIKE), the
second is a mysql problem in that the query is good SQL and the third
is just wrong, although it does work.
float just does not play well with ‘==’, however represented. You
nearly always need to provide some form of tolerance to allow for the
inherent imprecision of
the internal representation. Given the application, you probably need
to provide some circular probability of error regarding fixing the
spherical co-ordinate centre in any case.
Not sure if it applied to what you’re doing, but it seems like you’d
have a tolerance issue even without the imprecision of the float. My
city is roughly 40N 86W, but that wouldn’t find it in an ‘=’ query.
Maybe a between query with a fudge factor, like:
:conditions => [“lat between ? and ? and lon between ? and ?”,lat-
fudge,lat+fudge,lon-fudge,lon+fudge]
To keep it DRY, you could build your own finder method on city.