CODEWITHSUNDEEP
sqlruby-on-railsdatabaseactiverecordgeospatial
jacob_g
jacob_g

Reputation: 779

Query N closest database entries using active record query interface

In my Rails project I have a table called "items", where each item has a latitude and longitude property. When I retrieve the items for my client, I fetch the current user's longitude and latitude. Using these two sets of coordinates, I want to use a haversine function and fetch 100 results with the smallest haversine distance.

The SQL should look something like this:

SELECT
*,
haversine_distance(user_lat, user_long, item_lat, item_long) as distance
FROM
db.items
WHERE
item_lat < {user_lat} +1
AND item_lat > {user_lat}+1
AND ..
AND ..
ORDER BY distance DESC
LIMIT 100

but this would necessitate using a custom function in the query, which I'm not sure how to do. Also, regarding lines 7 and 8 of the query, I'm attempting to a where clause to quickly filter the set of results within some reasonable distance so I don't have to apply the haversine to the entire table. Right now, I have the function defined in a singleton called DistanceService, but I assume I cannot use Ruby functions in the query.

When I tried to add the function haversine_distance to the controller and call it like:

      @items = Item.select("items.*, haversine_distance(geo_long, geo_lat,
                                  #{@current_user.geo_long}, #{@current_user.geo_lat}) AS distance")
                   .where(status: 'available', ....)

I was met with FUNCTION ****_api_development.haversine_distance does not exist. This leads me to think the function should be somehow defined in SQL first via some migration, but I'm not very familiar with SQL and don't know how I would do that with a migration.

I'm also wondering if there's a solution that would support pagination, as it will hopefully eventually become a requirement for my project.

Thanks.

Upvotes: 0

Views: 44

Answers (1)

max
max

Reputation: 101811

In Rails you define database functions through migrations:

class AddHaversineDistanceFunction < ActiveRecord::Migration
  def up
    execute <<~SQL
      DELIMITER $$
      DROP FUNCTION IF EXISTS haversine_distance$$

      CREATE FUNCTION haversine_distance(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
      ) RETURNS FLOAT
      NO SQL DETERMINISTIC
      COMMENT 'Returns the distance in km
             between two known points of latitude and longitude'
      BEGIN
      RETURN DEGREES(ACOS(
        COS(RADIANS(lat1)) *
        COS(RADIANS(lat2)) *
        COS(RADIANS(lon2) - RADIANS(lon1)) +
        SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
      )) * 111.045;
      END$$

      DELIMITER;
    SQL
  end

  def down
    execute "DROP FUNCTION IF EXISTS haversine_distance"
  end
end

The actual function here is adapted from Plum Island Media.

But you might want to check out the geocoder gem which provides this functionality and uses a better formula.

Upvotes: 1

Related Questions