Databases
StackOverflow2013
Postsdbo
POGeo searching from zip codes with latitude/longitude in database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGeo searching from zip codes with latitude/longitude in database
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>I don't think code from the controller or model is required for this, however if needed please ask and I will provide.</p> <p>search form:</p> <pre><code>&lt;%= form_tag searches_path, method: :get do %&gt; &lt;p&gt; &lt;%= text_field_tag :search, params[:search] %&gt; &lt;%= button_tag "Search", name: nil %&gt; &lt;/p&gt; &lt;% end %&gt; &lt;P&gt;&lt;%= link_to "Advanced Search", new_search_path %&gt;&lt;p&gt; &lt;%= form_tag users_path, method: :get do %&gt; &lt;%= label :zip_code, "Enter zip code: " %&gt; &lt;%= text_field_tag :zip_code, params[:zip_code] %&gt; &lt;% end %&gt; </code></pre> <p>/indices/user_index.rb:</p> <pre><code> ThinkingSphinx::Index.define :user, :with =&gt; :active_record do # fields indexes name, :as =&gt; :user, :sortable =&gt; 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 =&gt; :zip_city has "RADIANS(zips.lat)", :as =&gt; :latitude, :type =&gt; :float has "RADIANS(zips.lon)", :as =&gt; :longitude, :type =&gt; :float end </code></pre> <p>User model:</p> <pre><code> has_and_belongs_to_many :zips </code></pre> <p>Zip model:</p> <pre><code>class Zip &lt; 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 =&gt; code) end end </code></pre> <p>User table has the following columns: <code>zip_code</code>.</p> <p>The zip codes table has the following columns: <code>code</code>, <code>city</code>, <code>state</code>, <code>lat</code>, <code>lon</code></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCornelius Wilson
    1. USCornelius Wilson
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGeo searching from zip codes with latitude/longitude in database
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POGeo searching from zip codes with latitude/longitude in database
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POGeo searching from zip codes with latitude/longitude in database
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhere are you stuck at? http://stackoverflow.com/questions/1078386/see-if-lat-long-falls-within-a-polygon-using-mysql
      singulars
      1. POGeo searching from zip codes with latitude/longitude in database
      1. USMarcus Adams
    2. COI am stuck at the beginning. So I have the index file setup. As you can see it is pulling from the User table. Should I create another one named zip_index.rb that will be for the zips table that contains all the zip codes with lat/lon in United States? What kind of work do I need to do in the user model (or do I need to create a search model). There's a lot of information on Sphinx, and I have set it up for other parts of the app. There's just no information really on the geo searching aspect.
      singulars
      1. POGeo searching from zip codes with latitude/longitude in database
      1. USCornelius Wilson
    3. COI think you'll need to implement the [Haversive formula](http://en.wikipedia.org/wiki/Haversine_formula) unless Thinking Sphinx has a function which does it for you. The formula calculates the distance between two points on a sphere. The function is just some basic trig; a MySQL version of it would be `(6378.10 * ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(long1) - RADIANS(long2)) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))))`
      singulars
      1. POGeo searching from zip codes with latitude/longitude in database
      1. USmadebydavid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload