In my last entry we looked at using scopes to dynamically build scopes for searching in your Ruby on Rails application. In Tryst, we needed to let our uses search for other singles that were xxx miles from their location. We require that new users enter their zip code, so that is what we had to go off of.
When I was researching the best way to do this, I did a quick Twitter poll – most of my friend thought I should just use Google’s GeoCoding API, which will let you preform these types of searches. I really didn’t want to go this route for a couple reasons.
- It’s an external dependency. Yes, Google’s uptime is impeccable, but I don’t want to rely on them
- Google’s TOS requires that the site/app/service be free. Tryst may end up with paid features down the road and I didn’t want to have to rewrite a substantial amount of code 1 year from now.
- It’s a challenge.
So, we decided to do our own thing.
Our workflow for a search would work like this – we get a distance and a zip code as search parameters, feed both of these into something that returns all the zip codes within the specified distance, and then find all the uses in those zip codes. Easy enough.
PS. I’m using MySQL here.
First, we need a reference for zip codes. I found this great free CSV that has all zip codes, major cities, longitude, latitude, area codes, and even metropolitan codes. Import it into your database and remove any rows that were not in the US. I actually had a ‘Location’ model in my Rails application that I imported this into.
After some digging around the webs, I found some crazy algorithms for generating distance between different locations based on their longitude and latitude. I’m not going to pretend to understand everything that is happening here, but after some playing around I ended up with this:
SELECT o.zip_code
FROM locations z, locations o, locations a
WHERE z.zip_code = #{zip_code}
AND z.zip_code = a.zip_code
AND (3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.latitude-o.latitude)*0.017453293)/2),2) +
COS(z.latitude*0.017453293) *
COS(o.latitude*0.017453293) *
POWER(SIN(((z.longitude-o.longitude)*0.017453293)/2),2)
)))) <= #{distance}
I’ll let you guess which part does the calculation. :)
zip_code and distance you’ll need to pipe into the SQL, but you’ll get back all the zip codes with in your specification. We just thew the returned array of zip odes into a scope like this:
# models/users.rb
def search(params)
scope = User.scoped({})
...
scope = scope.conditions "users.zip_code in (?)", zip_codes unless zip_codes.blank?
...
end
Now, this query is fairly slow. By slow, I mean about 25-30ms correctly indexed on my development box. For us it’s just fine.
I hope that helped someone out – I had a hell of a time finding a solution I liked and I really think this is the best one.
