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;

Answer 2

Sounds like your having an issue mapping users to locations, the original question seems to be misleading.

Instead of mapping locations to users the locations table should probably be a reference table of all locations based on zip's. Then you should add a scope to the Location to enable you to search for locations by zip code.

Something like this:

# Add scope to /models/location.rb
scope :by_zip_code, ->(code) { where('code = ?', code) }

Then in the user class, instead of saying a user has_many location, add a method, to retrieve all locations based on the users zip code.

Example:

# in /models/user.rb

 def location

    if Location.by_zip_code(self.zip_code.to_s).any?
        # you can return all here if you want more than one
        # for testing just returning the first one
        return Location.by_zip_code(self.zip_code.to_s).first
    else
        return nil
    end
end

Then when you have a user object, you can use the new instance method:

user = User.first
user.location

Answer 3

You can do all this (and much more) with the geocoder gem. The gem is customizable, you can specify whatever maps API you want (default is google maps). Here's a railscasts episode on geocoder with a detailed explanation on usage.

Answer 4

You can compute the distance between two points utilizing the Haversine formula.

Landon Cox has gone through the trouble of creating the Haversine class for you already at this location: http://www.esawdust.com/blog/gps/files/HaversineFormulaInRuby.html

I have used this class in a previous project and it works pretty nicely

All you need to do is put in the logic to compare the distance of each user from your current location or selected user. Since you already have the lat and long of everything this should be pretty simple.

Hope this helps.