Query N closest database entries using active record query interface

Question

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.

this link from SO probably can help you https://stackoverflow.com/a/39338205/938947 — widjajayd, May 04 '20 at 02:23
This will work, but I'm wondering if there's a cleaner way that doesn't require literal SQL, that can be built on top of active record query interface. — jacob_g, May 04 '20 at 03:23

score 1 · Accepted Answer · answered May 04 '20 at 16:32

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.

Thanks, can't believe how to add a function via migration. – jacob_g May 04 '20 at 18:38 — jacob_g, May 04 '20 at 18:38

Query N closest database entries using active record query interface

1 Answers1