order by distance doesn't seem to work

[jmeiss]

I've tryed to order my request by distance but I get an exception:

ActiveRecord::StatementInvalid in PagesController#index 
PGError: ERROR:  syntax error at or near "AS" 
LINE 1: ...S(users.latitude))*COS(RADIANS(users.longitude))+ AS 
alias_2... 
                                                             ^ 
: SELECT * FROM ( SELECT     DISTINCT ON ("users".id) "users".id, 
users.updated_at AS alias_0, users. AS alias_1, 
COS(0.852012144638921)*COS(0.0415964332145236)*COS(RADIANS(users.latitude)) *COS(RADIANS(users.longitude)) 
+ AS alias_2 FROM       "users" LEFT OUTER JOIN "pictures" ON 
"pictures"."user_id" = "users"."id" WHERE 
(((pictures.picture_file_name IS NOT NULL) AND 
(users.latitude>47.9181925059163 AND users.latitude<49.7152074574837 
AND users.longitude>1.01890820654452 AND 
users.longitude<3.74769192543548)) AND ( 
(ACOS(least(1,COS(0.852012144638921)*COS(0.0415964332145236)*COS(RADIANS(us ers.latitude))*COS(RADIANS(users.longitude)) 
+ 
COS(0.852012144638921)*SIN(0.0415964332145236)*COS(RADIANS(users.latitude)) *SIN(RADIANS(users.longitude)) 
+ 
SIN(0.852012144638921)*SIN(RADIANS(users.latitude))))*6376.77271) 
          <= 100))) AS id_list ORDER BY id_list.alias_0 DESC, 
id_list.alias_1 , id_list.alias_2  LIMIT 15

I've well added this lines to my User model: acts_as_mappable :distance_field_name => :distance, :lat_column_name => :latitude, :lng_column_name => :longitude

And this is my AR query:

@users_carousel = User.find(:all, 
                            :order => 'users.updated_at desc, users.distance', 
                            :limit => User::MAX_USERS_IN_CAROUSEL,
                            :origin => visitor_ip, 
                            :include => :pictures,
                            :conditions => 'pictures.picture_file_name IS NOT NULL')

I'me using rails3, with pgsql.

Thank you very much for you work!!! :)

[polmiro]

There is no distance attribute anymore, checkout the by_distance method in the code, might help you out a bit. I had similar problems when upgrading the geokit / geokit-rails version.

[kidlab]

I got this problem too with geokit-rails 2.0.1. Why did they remove that field??

[heebj]

I also want to use the following query as described in the Readme docu, but it does not work:

Location.within(5, :origin => @somewhere).order('distance DESC, nbr_seats ASC')

In #39 and #54 is suggested to use sort_by on a DistanceCollection but this way the queries are not chainable anymore (e.g. for paging). by_distance isn't an alternative either because it does not allow to limit the radius anymore.

Any ideas how to solve this? At least the Readme should be updated...

[heebj]

Finally I used the following workaround to solve the problem:

distance_column_name = distance_sql(normalize_point_to_lat_lng(origin), default_units, default_formula)
within(within, :origin => origin).order("#{distance_column_name} ASC")

[heebj]

At last I found the patch in #56 which works perfectly!