Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <pre class="lang-ruby prettyprint-override"><code>class User &lt; ActiveRecord::Base belongs_to :location attr_accessible :name end class Location &lt; ActiveRecord::Base attr_accessible :city, :latitude, :longitude, :zipcode end </code></pre> <p>Next, use the association in your index.</p> <p>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:</p> <pre class="lang-ruby prettyprint-override"><code>ThinkingSphinx::Index.define :user, :with =&gt; :active_record do # fields indexes name # attributes has created_at, updated_at has location.city, :as =&gt; :location_city has "RADIANS(locations.latitude)", :as =&gt; :latitude, :type =&gt; :float has "RADIANS(locations.longitude)", :as =&gt; :longitude, :type =&gt; :float end </code></pre> <p>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 <code>:geo</code> .</p> <p>Then for example, to find all users within 200 kilometers of lat / lng parameters in degrees, ordered by nearest first:</p> <pre class="lang-ruby prettyprint-override"><code>class DistanceController &lt; ApplicationController def search @lat = params[:lat].to_f * Math::PI / 180 @lng = params[:lng].to_f * Math::PI / 180 @users = User.search :geo =&gt; [@lat, @lng], :with =&gt; {:geodist =&gt; 0.0..200_000.0}, :order =&gt; "geodist ASC" end end </code></pre> <p>For debugging, it's nice to know that in the view you can refer to the computed distance too:</p> <pre class="lang-html prettyprint-override"><code> &lt;% @users.each do |user| %&gt; &lt;tr&gt; &lt;td&gt;&lt;%= user.name %&gt;&lt;/td&gt; &lt;td&gt;&lt;%= user.location.zipcode %&gt;&lt;/td&gt; &lt;td&gt;&lt;%= user.location.city %&gt;&lt;/td&gt; &lt;td&gt;&lt;%= user.distance %&gt;&lt;/td&gt; &lt;/tr&gt; </code></pre> <p>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):</p> <pre class="lang-sql prettyprint-override"><code>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; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POGeo searching from zip codes with latitude/longitude in database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USflup
    1. USflup
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POGeo searching from zip codes with latitude/longitude in database
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI get a `undefined method `active_record'` when creating the association unless I need a foreign key. The user model I added `has_many :locations`, though belongs_to sounds better since the user has one location. I created a Location model and added `has_many :users`. I am not referencing the city and state atm. Users will only be able to search by zip code. I am going to use city/state for the users profile location. Ex as shown on profile: User A from Las Vegas, NV
      singulars
      1. PO
      1. USCornelius Wilson
    2. COYes, I'd expect you need a foreign key on the zip code in the user table. The user belongs to exactly one location, no? So I agree that `belongs_to` sounds best.
      singulars
      1. PO
      1. USflup
    3. COI think I am setting foreign key wrong. I get the following error when rebuilding ts `index 'user_core': sql_range_query: Unknown column 'user.location.latitude' in 'field list'` I have updated my code in posting
      singulars
      1. PO
      1. USCornelius Wilson
 

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