2

I'm using Postgres in a Rails app. Storing Lat/Lon in the database as float values. I want to be able to compare locations' lat/lons, but I only know how to do exact equality:

Location.where(lat: @lat, lon: @lon)

My issue is, I want Postgres to read, say 71.233434 as equal to 71.233545, or something. In other words, can I query for rounded equality, or equality within a range?

I've Googled around for this pretty extensively (maybe using the wrong terms? I can't think of it. "Active Record Postgres float within", "Active Record Postgres rounded query", etc). No luck, though. I've only found how to round values coming out of the database, which isn't much help, cause that's easy to do in Ruby.

Any ideas? I'd prefer some sort of Active Record solution, if it exists, but if not, I'd also definitely appreciate (a link to) actual querying code.

Thanks!

EDIT -- Looking at this answer, I tried the below:

Location.find_by_sql("SELECT * FROM locations WHERE lat = ROUND(30.67035, 0.001)")

Hoping to find the Location in the DB with the lat value of 30.6703649, but I ran into this error:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  function round(numeric, numeric) does not exist
LINE 1: SELECT * FROM places WHERE lat = ROUND(30.67035, 0.001)

Fundamentally same error when I try the opposite, which makes a bit more sense:

Location.find_by_sql("SELECT * FROM locations WHERE ROUND(lat, 0,001) = 30.67035")

My answer based on the response below:

  scope :none, -> { where('1=2') } # returns an empty relation
  scope :by_ll, -> (lat, lon) { by_lat(lat).by_lon(lon) }
  scope :by_lat, -> (lat) { lat ? where("ROUND( CAST(lat as numeric), 4 ) = ?", lat.round(4) ) : none }
  scope :by_lon, -> (lon) { lon ? where("ROUND( CAST(lon as numeric), 4 ) = ?", lon.round(4) ) : none }
Community
  • 1
  • 1
Sasha
  • 5,308
  • 7
  • 47
  • 87

1 Answers1

4

The error is because there is no form of round which takes two numerics as arguments. It expects a numeric and an int, where the int is the number of places to which to round.

So, try this:

Location.find_by_sql("SELECT * FROM locations WHERE ROUND(lat, 5) = 30.67036")

Here is the relevant doc (search for round).

Also note that I tweaked the right side of the expression slightly. Given you're looking for 30.6703649, rounded to the 5th decimal place, that would be 30.67036, since the next digit is 4, meaning it would be rounded down.

Also, regarding your first set of numbers, for 71.233434 to be seen as equal to 71.233545, you would need to round to the 3rd decimal place and check for 71.233.

I would recommend that you determine what tolerance you want to support in the values -- i.e. to the 3rd decimal place, or 5th -- and then use that argument in the call to round.

khampson
  • 12,796
  • 3
  • 36
  • 38
  • Thanks so much! That nailed it. For the record, I also had to cast the lat/lon values (which were DP values) to Numerics. I've put the end result in my question above. – Sasha Nov 15 '14 at 00:05
  • you're comparing rounding to powers of 1/10 made by DB with rounding to powers of 1/10 made by ruby. I suspect might sometimes round same value differently, especially since powers of 1/10 don't have an exact representation in binary floating point! I'd try something like `ROUND(lat, 5) = ROUND(?, 5)` to do both same way (untested). – Beni Cherniavsky-Paskin Jan 05 '18 at 09:46