SQL to find quickly find nearby addresses in Rails
If you want to find nearby restaurants, gas stations, or hotels, in a Rails app, and if you want to sort by distance, you can use a custom sql query in Rails. This will give better performance that trying to do it through ActiveRecord methods.
I profiled Active Record & Rails last year, and found that most of the application’s time was spent loading in *all* columns from the database, and parsing the values. By only grabbing the necessary columns, and by pre-filtering with sql, this boosted performance by an order of magnitude. As of early 2008, it was also faster to push the math calculations into sql. With the new Ruby, Rails, and Merb combo coming out soon, these performance values will change.
The code below will create an array of Location objects. First, it creates a bounding box, and limits record that fall within that rectangle. Then, for remaining records, it calculates the sum of the squared differences to sort by distance. The Sqrt isn’t necessary for sorting purposes. If you want the distance, you can just take the sqrt of that value as needed.
The performance of this query will vary by database type, and it can be optimized further. But this was sufficient for the app we were building.
This code expects variables called lat, lon, and offset_degrees (which is the search radius to limit results)
search_results=Location.find_by_sql(
[
"SELECT
loc.id, loc.name, loc.address, loc.city, loc.postalcode, loc.lat, loc.lon,
( pow((lat - :testlat),2) + pow((lon - :testlon),2)) AS dist
FROM locations AS loc
WHERE (lat< :maxlat AND lat > :minlat AND lon < :maxlon AND lon > :minlon #{kwclause})
HAVING dist<:maxdist
ORDER BY dist
LIMIT 15;",
{ :maxlat=>(lat+offset_degrees), :minlat=>(lat-offset_degrees), :maxlon=>(lon+offset_degrees),
:minlon=>(lon-offset_degrees), :testlat=>lat, :testlon=>lon, :maxdist=>(offset_degrees**2)
}
])