Geo searching from zip codes with latitude/longitude in database

Question

I have struggle with understanding how to accomplish this, and there seems to be a lot of people asking this question with no answers. I have a users table with their zip code. I created a zips table with every zip code with latitude/longitude in the United States.

What I would like to do is connect the two so that users can search for other users. I have Thinking Sphinx and I would prefer to continue using it. I want to provide users a checkbox for the distance to search (5, 10, 25, 50, 100, 500 miles). The results should always return the closest users.

I don't think code from the controller or model is required for this, however if needed please ask and I will provide.

search form:

<%= form_tag searches_path, method: :get do %>
<p>
        <%= text_field_tag :search, params[:search] %>
        <%= button_tag "Search", name: nil %>
        </p>
<% end %>

<P><%= link_to "Advanced Search", new_search_path %><p>

<%= form_tag users_path, method: :get do %>
<%= label :zip_code, "Enter zip code: " %>
<%= text_field_tag :zip_code, params[:zip_code] %>
<% end %>

/indices/user_index.rb:

     ThinkingSphinx::Index.define :user, :with => :active_record do
  # fields
  indexes name, :as => :user, :sortable => true
  indexes religion, zip_code, about_me, career, sexuality, children, user_smoke, user_drink, gender, ethnicity, education


  # attributes
  has id, created_at, updated_at
  has zips.city, :as => :zip_city

  has "RADIANS(zips.lat)",  :as => :latitude,  :type => :float
  has "RADIANS(zips.lon)", :as => :longitude, :type => :float


end

User model:

  has_and_belongs_to_many :zips

Zip model:

class Zip < ActiveRecord::Base
  attr_accessible :city, :lat, :lon, :code, :zipcode
  has_and_belongs_to_many :users

  validates :code, uniqueness: true

    self.primary_key = 'code'      

  def self.code(code)
    find_by(:code => code)
  end


end

User table has the following columns: zip_code.

The zip codes table has the following columns: code, city, state, lat, lon

Answer 1

The first step is to create an association between the User and its Location, so that the ActiveRecord for the Location can be referenced from that of the User.

class User < ActiveRecord::Base
  belongs_to :location
  attr_accessible :name
end

class Location < ActiveRecord::Base
  attr_accessible :city, :latitude, :longitude, :zipcode
end

Next, use the association in your index.

You have to create an alias for a field on the Location model, to make sure the location table gets joined. And you must add attributes for the location's latitude and longitude:

ThinkingSphinx::Index.define :user, :with => :active_record do
  # fields
  indexes name

  # attributes
  has created_at, updated_at
  has location.city, :as => :location_city
  has "RADIANS(locations.latitude)",  :as => :latitude,  :type => :float
  has "RADIANS(locations.longitude)", :as => :longitude, :type => :float

end

As others have already mentioned, since the earth is not flat, you'll need to account for that when you compute the distance between locations. The Haversine function is good for that. Thinking Sphinx has it built-in and you can filter and sort on it using :geo .

Then for example, to find all users within 200 kilometers of lat / lng parameters in degrees, ordered by nearest first:

class DistanceController < ApplicationController
  def search
    @lat = params[:lat].to_f * Math::PI / 180
    @lng = params[:lng].to_f * Math::PI / 180
    @users = User.search :geo => [@lat, @lng], :with => {:geodist => 0.0..200_000.0}, :order => "geodist ASC"
  end
end

For debugging, it's nice to know that in the view you can refer to the computed distance too:

  <% @users.each do |user| %>
    <tr>
      <td><%= user.name %></td>
      <td><%= user.location.zipcode %></td>
      <td><%= user.location.city %></td>
      <td><%= user.distance %></td>
    </tr>

EDIT: added more detail about my working version, for completeness' sake also adding the table definitions. (MySQL, generated using db:migrate, these are the create scripts as MySQL Workbench generates them):

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_users_on_location_id` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zipcode` varchar(255) DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;