ar3_and_arel_is_the_business.rb

# this is why AR3 and arel is awesome.
# bus route data, loaded from google transit data

# the usage
class StopsController < ApplicationController
  def nearest
    @stops = Stop.nearest_to(lon, lat).serving_route("SKIP").limit(5).all
  end
end

# the implementation. multi-table joins and subselects!
class Stop < ActiveRecord::Base
  # relationships... etc.

  scope :serving_route, lambda { |route_identifier|
    sql = select("distinct stops.id").joins(:stop_times => {:trip => :route}).where("routes.identifier = ?", route_identifier).to_sql
    where("stops.id in (#{sql})")
  }

  scope :nearest_to, lambda { |lon, lat|
    select(
      "*, ST_Distance(stops.location, ST_SetSRID(ST_Point(#{lon.to_f}, #{lat.to_f}), 4326)) as dist"
    ).order("dist asc")
  }
end