Hi Liam I can't spot anything wrong with your maths. I did find what appears to be the formula Sphinx uses, and while it seems to be similar, it's not quite the same: http://sphinxsearch.com/forum/view.html?id=2060 (scroll down towards the end)
Can someone with a better maths brain than I compare the two formulas? Also, on a completely unrelated note, you could do the following has geocoding.geocode.latitude, :as => :lat :type => :float is only needed if the data type of the column is a decimal, I think. -- Pat On 06/10/2008, at 1:32 AM, Liam Morley wrote: > > A note for the next person that comes along who's using > acts_as_geocodable/Graticule: the values are already in radians, so > you won't need to do "has 'RADIANS(geocodes.latitude)', :as > => :lat, :type => :float"; a simple "has 'geocodes.latitude', :as > => :lat, :type => :float" will do, obviously same with longitude. > > I spent an embarrassingly long time figuring this out, delving into > Riddle. My debugging is here for posterity (mostly so I know where to > find it should I need to find it again): http://pastie.org/285306 Note > to self, actually look at the values and see if they make sense next > time. > > One question, though. Now that (I think) I've fixed my problem, my > distances are in the right order, but are still coming out very > different than expected. It doesn't seem to be a meters/miles > conversion issue, as the ratio is different for different results. I > think it has something to do with how the distance between two points > is calculated. These are the distances (in miles) I get from > Graticule, which seem accurate, as I just used a series of addresses > all on the same street: > >>> expected = Store.search('test', :sort_mode => :extended, :geo => >>> [user.geocode.latitude, user.geocode.longitude], :sort_by => >>> '@geodist asc').map{|s| s.distance_to(user).round(4) } > => [0.0, 0.0662, 0.0662, 0.0662, 0.0662, 0.0662, 0.1651, 0.1725, > 0.1808, 0.2017, 0.1994, 0.2006, 0.1982, 0.172, 0.1674, 0.163, 0.1635, > 0.1642, 0.1612, 0.1618] > > This is what I actually get from Riddle, using Google's conversion > rate of 1 meter = 0.000621371192 miles: > >>> actual = client.query('test')[:matches].map{|r| (r[:attributes] >>> ['@geodist'] * 0.000621371192).round(4) } > => [0.0, 1.5533, 1.5533, 1.5533, 1.5533, 1.5533, 5.8351, 5.8605, > 6.0018, 6.6371, 6.6426, 6.6471, 6.6527, 6.7824, 6.8121, 6.8421, > 6.8443, 6.8458, 6.8518, 6.854] > > (Setting up Riddle to run this query was done the same way as in > http://pastie.org/285306 ) > > You can tell that the ratio of actual to expected value is different > for different values, so it's not a unit issue. > >>> [expected, actual].transpose.map{|e,a| a/e} > => [NaN, 23.448294903365, 23.448294903365, 23.448294903365, > 23.448294903365, 23.448294903365, 35.3492506771287, 33.9650314133358, > 33.1955965180949, 32.9038992782333, 33.3137166718008, > 33.1375516600513, 33.5584504361732, 39.4256633953044, > 40.6907458037367, 41.988478809478, 41.8544923668007, 41.7034066859609, > 42.5097457523863, 42.3618241718312] > > (I put all of this in another pastie http://pastie.org/285345, again > mostly for posterity) > > My first guess is that there's a difference between the way that > Graticule determines distance and the way that Sphinx determines > distance (and based on these results, it seems as though Graticule > might be more correct, though they're not very conclusive), but it's > also perfectly possible that I missed something. I verified this using > http://www.movable-type.co.uk/scripts/latlong.html (using the full > values, not rounded values), and Graticule seems pretty much on the > mark. If my config is correct, I can try to take this up on the Sphinx > forum, but I wanted to see if anyone could spot any configuration > problems before I go and say that there might be a problem with > Sphinx. > > > > On Oct 2, 11:26 pm, Pat Allan <[EMAIL PROTECTED]> wrote: >> Yeah, that was one of the first features I aimed for - chaining >> associations. And AR's SQL-generating code for the joins makes my >> life >> so much easier, too :) >> >> Cheers >> >> -- >> Pat >> >> On 02/10/2008, at 1:02 PM, Liam Morley wrote: >> >> >> >>> Pat, many thanks again. This is pretty interesting- I was originally >>> under the impression that relationships in ThinkingSphinx were SQL/ >>> string-bound, and you'd have to do extra legwork to specify things >>> like "geocodings.geocodable_type = 'Store'". But from your answer, I >>> get the sense that ThinkingSphinx is able to piggyback on top of AR >>> associations. Very DRY. Pretty smart stuff. Thanks again for some >>> great code. >> >>> liam >> >>> On Oct 1, 12:53 am, Pat Allan <[EMAIL PROTECTED]> wrote: >>>> Hi Liam >> >>>> The manual SQL in fields and attributes appear within the SELECT >>>> part >>>> of the SQL statement - so you really don't want to be throwing a >>>> WHERE >>>> clause in as well. >> >>>> The best approach would be to get the associations set out in your >>>> models that links the Store to the geocodes table, and then use >>>> those >>>> values within the index (I *think* I've got the polymorphic >>>> associations set up right): >> >>>> class Geocode < ActiveRecord::Base >>>> # ... >>>> has_many :geocodings >>>> end >> >>>> class Geocodings < ActiveRecord::Base >>>> # ... >>>> belongs_to :geocode >>>> belongs_to :geocodeable, :polymorphic => true >>>> end >> >>>> class Store < ActiveRecord::Base >>>> # ... >>>> has_one :geocoding, :polymorphic => true >> >>>> define_index do >>>> # ... >> >>>> has geocoding.geocode(:id) # this is to force the join in the >>>> generated SQL >>>> has "RADIANS(geocodes.latitude)", :as => :lat, :type >>>> => :float >>>> has "RADIANS(geocodes.longitude)", :as => :lon, :type >>>> => :float >>>> end >>>> end >> >>>> Caveat: I've never used acts_as_geocodable or graticule. Still, I >>>> hope >>>> it's useful. >> >>>> Cheers >> >>>> -- >>>> Pat >> >>>> On 01/10/2008, at 5:24 AM, Liam Morley wrote: >> >>>>> I'm using acts_as_geocodable, which uses the graticule gem. This >>>>> creates two tables: geocodes, which is the table that has >>>>> latitude, >>>>> longitude, and other location data; and geocodings, which is an >>>>> STI- >>>>> type table that connects my models to geocodes through >>>>> geocodable_type/ >>>>> geocodable_id (to point to the model), and geocode_id (to point to >>>>> the >>>>> geocode). >> >>>>> So, as far as SQL is concerned, I can get the latitude and >>>>> longitude >>>>> for a Store model object with the following query: >> >>>>> select geocodes.latitude, geocodes.longitude from geocodes inner >>>>> join >>>>> geocodings on geocodes.id = geocodings.geocode_id and >>>>> geocodings.geocodable_type = 'Store' and >>>>> geocodings.geocodable_id = >>>>> #{my_store.id} group by geocodes.id >> >>>>> where my_store.id is the ID for my Store model object. >> >>>>> Looking through the discussions, I've seen a lot of questions >>>>> concerning how to retrieve geo information with a belongs_to/ >>>>> has_many >>>>> relationship, but is it possible to do geo search with something >>>>> like >>>>> acts_as_geocodable? And what on earth would your 'has' statements >>>>> look >>>>> like? >> >>>>> has "RADIANS(geocodes.latitude) where geocodes.id = >>>>> geocodings.geocode_id and geocodings.geocodable_type = 'Store' and >>>>> geocodings.geocodable_id = ??? group by geocodes.id", :as >>>>> => :lat, :type => :float >> >>>>> something like that? I really don't know. Any ideas? > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en -~----------~----~----~----~------~----~------~--~---
