Query to find all locations within the radius for each location at another coordinate

Question

We're using the geokit-rails gem to find all locations within a certain distance from another location.

class CreateLocations < ActiveRecord::Migration[7.2]
  def change
    create_table :locations do |t|
      t.decimal :lat
      t.decimal :lng
      t.float   :radius
    end
  end
end

class Location < ApplicationRecord
  acts_as_mappable
end

The following works great and returns all locations within 5 miles of (37,-122)

Location.within(5, origin: [37,-122])

We next need to find all locations within the distance stored in the radius column in Location. The following query does not work.

Location.within('locations.radius', origin: [37,-122])

Can someone help me figure out what I'm doing wrong?

[Update] The solution below by @max and @engineersmnky worked great. I also discovered the "merge" function which lets you use .within across a join table. For example if a location has many products then you can search for all products sold by locations within their delivery radius to you by this. Hopefully this saves someone else a lot of time.

Product.joins(:location).merge(Location.within(Location.arel_table[:radius], origin: [37, -122]))

Answer 1

After running through the source code a bit as @max pointed out you can use

Location.within( Location.arel_table[:radius], origin: [37,-122])

This will result in <= but it is a lot cleaner than the alternatives

The formula differs from yours though.

(ACOS(least(1,COS(#{lat})*COS(#{lng})*COS(RADIANS(#{qualified_lat_column_name}))*COS(RADIANS(#{qualified_lng_column_name}))+
COS(#{lat})*SIN(#{lng})*COS(RADIANS(#{qualified_lat_column_name}))*SIN(RADIANS(#{qualified_lng_column_name}))+
SIN(#{lat})*SIN(RADIANS(#{qualified_lat_column_name}))))*#{multiplier})

Alternatively we can use your current concept (shown in a comment) but leverage the [geokit-rails] implementation:

origin = Geokit::LatLng(37,-122)
Location.where(Location.distance_sql(origin).lt(Location.arel_table[:radius]))

If you really want to use your current formula instead (which is different than what within uses). We can create your formula as follows:

# Including Math because otherwise I would need to create new instances of 
# Addition, Division, and Multiplication for each operation  

Arel::Nodes::NamedFunction.include(Arel::Math) 

class Location < ApplicationRecord
  scope :inside_radius, ->(lat: , lng:) {
    location_table = Arel::Table.new('locations')
    q = Arel::Nodes::NamedFunction.new('ASIN',[
      Arel::Nodes::NamedFunction.new('SQRT',[ 
        Arel::Nodes::NamedFunction.new('POWER',
          [
            Arel::Nodes::NamedFunction.new("SIN",
             [ Arel::Nodes::NamedFunction.new("RADIANS",[location_table[:lat] - lat])]
            ) / Arel.sql('2.0'),
            2
          ]
        ) + 
        Arel::Nodes::NamedFunction.new('COS',[Arel::Nodes::NamedFunction.new('RADIANS',[lat])]) *
        Arel::Nodes::NamedFunction.new('COS',[Arel::Nodes::NamedFunction.new('RADIANS',[location_table[:lat]])]) * 
        Arel::Nodes::NamedFunction.new('POWER',
          [
            Arel::Nodes::NamedFunction.new("SIN",
             [ Arel::Nodes::NamedFunction.new("RADIANS",[location_table[:lng] - lng])]
            ) / Arel.sql('2.0'),
            2
          ]
        )
      ])
    ]).*(2).*(3956).lt(location_table[:radius])
    where(q)
  }
end 

Usage:

Location.inside_radius(lat: 37, lng: -122)

Produces

SELECT locations.*
FROM 
  locations 
WHERE 
ASIN(SQRT((POWER(SIN(RADIANS(([locations].[lat] - 37))) / 2.0, 2) + COS(RADIANS(37)) * COS(RADIANS([locations].[lat])) * POWER(SIN(RADIANS(([locations].[lng] - -122))) / 2.0, 2)))) * 2 * 3956 < [locations].[radius]