I have a large Postgres table contain geographic positions (latitude and longitude) both fields are indexed, and both are defined as
If I run a query looking for an exact position match, something like this:
WHERE latitude = 1.234567890123456789 AND longitude = 9.876543210987654321
Then get a very fast response, but I get very few results because the database is searching for a very precise match.
For my purposes, I'm looking for positions that match to within a few meters so a match to 4 or 5 decimal places should be fine. This gives me the results I'm looking for:
WHERE ABS(latitude - 1.234567890123456789) < 0.0001 AND ABS(longitude - 9.876543210987654321) < 0.0001
But NOT the performance (it can take 5 minutes to run, compared to a fraction of a second for the exact search)
Next I tried rounding the precision down:
WHERE ROUND( latitude, 4) = ROUND( 1.234567890123456789, 4) AND ROUND( longitude,4) = ROUND( 9.876543210987654321, 4)
Again, same problem. Got the results I wanted, but took far too long.
So, my question is how can I search for a close match between two numbers, without losing performance?
UPDATE - SOLVED:
As a couple of commenters have observed, using
BETWEEN seems to work fine.