I'm trying to implement a method of scoping by geolocation using the geocoder gem.
My aim is to hit the geocoder api which will give me the ability to filter entries in the databate by distance to the given location, querying the geocoded longitude and latitude data of the model, here's how it works in the console:
Venue.near("Foobar Land")
=> [#<Venue:0x00007fd933bcf430
id: 266,
address: "Foobar Lane",
latitude: XXX.XXX,
longitude: XXX.XXX,
So, I implemented the following scope:
venue.rb
class Venue < ApplicationRecord
scope :location, -> address_search { near(address_search) }
end
But it's throwing this error:
PG::SyntaxError: ERROR: syntax error at or near "AS"
LINE 1: ...- venues.longitude) * PI() / 180 / 2), 2))) AS distanc...
FULL SQL QUERY:
: SELECT COUNT(venues.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((-37.8142176 - venues.latitude) * PI() / 180 / 2), 2) + COS(-37.8142176 * PI() / 180) * COS(venues.latitude * PI() / 180) * POWER(SIN((144.9631608 - venues.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((venues.longitude - 144.9631608) / 57.2957795), ((venues.latitude - -37.8142176) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing) FROM "venues" WHERE (venues.latitude BETWEEN -37.99408192118374 AND -37.634353278816256 AND venues.longitude BETWEEN 144.7354852795205 AND 145.1908363204795 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((-37.8142176 - venues.latitude) * PI() / 180 / 2), 2) + COS(-37.8142176 * PI() / 180) * COS(venues.latitude * PI() / 180) * POWER(SIN((144.9631608 - venues.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 20)
Now, I've done a whole bunch of googling and it seems that there is a know issue with ActiveRecord not formulating the SQL query correctly.
This problem has been noted here: https://github.com/alexreisner/geocoder/issues/630
I can reproduce it by doing this test, running the .count method as explained in that link:
a = Venue.near("paris")
a.class
=> Venue::ActiveRecord_Relation
a.count
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "AS"
LINE 1: ...- venues.longitude) * PI() / 180 / 2), 2))) AS distanc...
However the suggested solution in the article I linked to (adding (:all)), doesn't work for me:
a.count(:all)
(1.9ms) SELECT COUNT(*) FROM "venues" WHERE (venues.latitude BETWEEN -37.99408192118374 AND -37.634353278816256 AND venues.longitude BETWEEN 144.7354852795205 AND 145.1908363204795 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((-37.8142176 - venues.latitude) * PI() / 180 / 2), 2) + COS(-37.8142176 * PI() / 180) * COS(venues.latitude * PI() / 180) * POWER(SIN((144.9631608 - venues.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 20)
ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR: current transaction is aborted, commands ignored until end of transaction block
I don't know why my scopes method isn't working, but the error clearly seems to be thrown when I also run .near("x").count, so I imagine that if I can solve that problem, then I can also solve my scoping issue. However giving the additional argument (:all), which was the only suggested solution I could find online, does not work in my case.
