PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    Just use simple location object with radius

    Location.flat_map do |location|
  Location.within(location.radius, origin: [37,-122]).ids
end
    Login or Signup to reply.
  2. 0

    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})

    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]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search