CODEWITHSUNDEEP
ruby-on-railsactiverecord
Sauron
Sauron

Reputation: 6657

Rails: Dynamic Select with Active Record

I need to create a dynamic SQL select statement to get the city, state combination from a table called Metros with the least distance from a set of passed in GPS coordinates. Note the set of passed in GPS coordinates are variables that will pull from a list. In order to calculate the distance I need to be able to pass in a latitude and longitude, and using the the haversine formula, calculate the distance between the present row and all 291 cities in the table Metros, then select the minimum City,state.

In rails the issue I am running into is how to properly create the select statement with the Metros table to allow for passing in the variable GPS coordinates but also using Active Record instead of conventional SQL.

At present, this is how far I have gotten:

    Metros.select(
        "major_city
        , major_state
        ," haversine(row[latitude], row[longitude], lat2, long2)" as 'distance'")
           .group("major_city,major_state").limit(1).order('distance')

row[latitude], row[longitude] are the passed in variable latitude and longitude (the present row I am comparing all 291 cities/states GPS coordinates in the Metros table and need to select the one with the least distance outputted by the Haversine function). As for lat2, long2, they need to refer to the latitude and longitude columns in the Metros table.

In building this query I am unaware of how to input the Haversine function as well as pull those records from the database with Active Record and calculate.

Is there a better way to do what I am trying to do?

Upvotes: 3

Views: 827

Answers (1)

James Daniels
James Daniels

Reputation: 6981

You should implement haversine as a SQL function and then call in ActiveRecord like so:

Metros.select("major_city, major_state, haversine(?, ?, lat, long) as 'distance'", lat, long).
  group("major_city,major_state").
  limit(1).
  order('distance')

Upvotes: 1

Related Questions