8

I'm using the geocoder gem for Ruby that Ryan Bates introduces in episode 273 http://railscasts.com/episodes/273-geocoder?view=asciicast. In that episode, he uses the following to search objects within a 50 km radius (although he uses a Location model, where I use a User model). 

 @users = User.near(params[:search], 50, :order => :distance)

when I run this code on a Rails 4 app with postgres, it says

PG::Error: ERROR:  column users.distance does not exist

In Ryan's demo app (code here https://github.com/railscasts/273-geocoder/tree/master/siteseer-after) he doesn't have a distance column on his Location model (as I don't have it on my user model) so I assumed that distance was something supplied by the Geocoder gem. However, looking at the docs for geocoder http://rdoc.info/github/alexreisner/geocoder/master/frames, there is no distance method and all of the other distance-like methods produce the same error.

Does anyone know how I'd order results in that query by distance?

Update On the geocoder homepage http://www.rubygeocoder.com/ it gives this example of the use of distance, however when I try it it says that it doesn't exist for user. 

nearbys = Place.near("Omaha, NE", 50,
  :order => "distance")
bearing = nearbys.first.bearing # => 46.12
Geocoder::Calculations.compass_point(
  bearing) # => "NE"

Update

This is the sql generated

ActionView::Template::Error (PG::Error: ERROR:  column users.distance does not exist
LINE 1: ...ngitude) * PI() / 180 / 2), 2))) <= 50)  ORDER BY "users".di...
                                                             ^
: SELECT users.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((43.7058645 - users.latitude) * PI() / 180 / 2), 2) + COS(43.7058645 * PI() / 180) * COS(users.latitude * PI() / 180) * POWER(SIN((-79.3687137 - users.longitude) * PI() / 180 / 2), 2))) AS distance, CAST(DEGREES(ATAN2( RADIANS(users.longitude - -79.3687137), RADIANS(users.latitude - 43.7058645))) + 360 AS decimal) % 360 AS bearing FROM "users"  WHERE (users.latitude BETWEEN 42.98220558444576 AND 44.429523415554236 AND users.longitude BETWEEN -80.36976846878524 AND -78.36765893121476 AND 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((43.7058645 - users.latitude) * PI() / 180 / 2), 2) + COS(43.7058645 * PI() / 180) * COS(users.latitude * PI() / 180) * POWER(SIN((-79.3687137 - users.longitude) * PI() / 180 / 2), 2))) <= 50)  ORDER BY "users".distance ASC):
Leahcim
  • 40,649
  • 59
  • 195
  • 334
  • What is the SQL that is generated in the log when you run that query? The SQL should have a 'distance' column that is the result of a calculation. It's not a method, but you should be able to order by it. – Philip Hallstrom Jul 02 '13 at 16:18
  • I updated the OP with the sql. thanks for your help. – Leahcim Jul 02 '13 at 16:33
  • There is something wrong elsewhere. The SQL shouldn't be doing "ORDER BY 'users'.distance". It should just be "ORDER BY distance". I just tried it with rails 3.2.13, geocoder 1.1.8, and pg 0.15.0 (postgres version 9.1.1). Something else is adding the table name which is screwing you up I think. – Philip Hallstrom Jul 02 '13 at 17:54
  • hmm, Thanks for confirming. I wonder if it's a Rails 4 issue. – Leahcim Jul 02 '13 at 18:14
  • Could be. If so, that's a pretty serious bug. It's perfectly valid to order by something other than a table's columns. If Rails is tweaking the order by value you pass in, that's not good. – Philip Hallstrom Jul 02 '13 at 20:52
  • using Rails 3, I confirm I only have `ORDER BY distance` – apneadiving Jul 03 '13 at 07:23
  • 1
    I think it's a rails 4 Issue, the same thing is happening to me. At the end I just removed the sort. In the documentation says: "Results are automatically sorted by distance from the search point, closest to farthest" – goma Oct 07 '13 at 03:05

3 Answers3

1

You don't have to specify order: :distance, the results will be ordered by distance by default.

Damien
  • 26,933
  • 7
  • 39
  • 40
-1

When using Postgres along with the Geocoder gem, your queries cannot use symbolized keys (in this case, :distance) in the query. Using the string 'distance' in this case avoids the problem.

@users = User.near(params[:search], 50, :order => 'distance')
Ecnalyr
  • 5,792
  • 5
  • 43
  • 89
-1

try this:

    User.near(params[:search], 50).reorder("distance asc")

Put orderding in quotes so it doesn't look at it as a table column.

reorder(..) redefines the order.

Max Ivak
  • 1,529
  • 17
  • 39