I have a query like so:
SELECT * FROM chat_rooms
WHERE earth_box(ll_to_earth(5, 5), 111000) @> ll_to_earth(lat, lng);
Essentially, I have lat = lng = 5
in the query, and in my chat_rooms
table I have two entries, one with lat=lng=5
, the other with lat=lng=4
. Using an online calculator, the distance between these two entries is 157 miles = 252.667km (i.e. entry lat=lng=4
should be returned when radius >= 157 miles. However, the query only returns the entry with lat=lng=4
when the radius is specified as ~111,000+. My question is, what unit is the radius in, and am I conducting this query correctly?
As per the doc, by default, the radius is expressed in meters.
The most important flaw is that the distance between 4;4 and 5;5 is 157 km, not miles. Even though, 111,000m is not the same as 157,000m, but the doc for earth_box
says
Some points in this box are further than the specified great circle distance from the location, so a second check using earth_distance should be included in the query.
so your query should be similar to
SELECT * FROM chat_rooms
WHERE earth_box(ll_to_earth(5, 5), 111000) @> ll_to_earth(lat, lng)
AND earth_distance(ll_to_earth(5,5), ll_to_earth(lat, lng)) <= 111000
in which case entry at 5;5 won't be returned, but will be if you use 158000 instead.