sqlpostgresqldistancegeographyearthdistance

Trying to calculate distance between two lat-lng points in PostgreSQL - what is the unit of PostgreSQL earth_box(ll_to_earth) return value?

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?

Solution

  • 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.