Postgresql earthdistance - earth_box with radius

Question

Please, can you explain me this behaviour of earth_box function ... or what I'm doing wrong?

data used

40.749276, -73.985643 = Empire State Building - is in my table
40.689266, -74.044512 = Statue of Liberty - is my current position in select - 8324m far from Empire State Building

my table

=> select id, latitude, longitude, title from requests;
 id | latitude  | longitude  |         title
----+-----------+------------+-----------------------
  1 | 40.749276 | -73.985643 | Empire State Building

distance from Empire State Building to Statue of Liberty

=> SELECT id, latitude, longitude, title, earth_distance(ll_to_earth(40.689266, -74.044512), ll_to_earth(latitude, longitude)) as distance_from_current_location FROM requests ORDER BY distance_from_current_location ASC;
 id | latitude  | longitude  |         title         | distance_from_current_location
----+-----------+------------+-----------------------+--------------------------------
  1 | 40.749276 | -73.985643 | Empire State Building |               8324.42998846164

My current position is Statue of Libery which is more than 8000m far from Empire State Buildng, but select return row with id 1 even when radius is only 5558m ! Can you explain me this behaviour or what is wrong?

=> SELECT id,latitude,longitude,title FROM requests WHERE earth_box(ll_to_earth(40.689266, -74.044512), 5558) @> ll_to_earth(requests.latitude, requests.longitude);
 id | latitude  | longitude  |         title
----+-----------+------------+-----------------------
  1 | 40.749276 | -73.985643 | Empire State Building

versions of extensions and postgresql

=> \dx
                                     List of installed extensions
      Name      | Version |   Schema   |                         Description
 ---------------+---------+------------+--------------------------------------------------------------  cube          | 1.0     | public     | data type for multidimensional
 cubes  earthdistance | 1.0     | public     | calculate great-circle
 distances on the surface of the Earth  plpgsql       | 1.0     |
 pg_catalog | PL/pgSQL procedural language

 => select version();
                                                                version
 --------------------------------------------------------------------------------------------------------------------------------------  PostgreSQL 9.4beta2 on x86_64-apple-darwin13.3.0, compiled by Apple
 LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit

thank you noe

Umpa · Accepted Answer · 2014-11-03T10:17:33.337

10

The problem here is that earth_box gets Statute Miles. 8324.42998846164 meters are near 5.172560986623845 statute miles Unit Converter

The solution: convert the radius into Statute Miles units

earth_box(ll_to_earth(40.689266, -74.044512), 5558/1.609) //doesn't return results

earth_box(ll_to_earth(40.689266, -74.044512), 9000/1.609) //does.

edited Nov 03 '14 at 10:17

answered Nov 03 '14 at 09:53

Umpa

334
11
16

2

This information is hard to find. Everyone seems to think this requires meters. Converting to Statute Miles units gets better results but still not that great. 8400/1.609 should already return Empire State Building but it doesn't. I'm aware that earthdistance is not supposed to be very accurate, but also it shouldn't be that far off. Maybe there is more to it? – Matt Aug 27 '18 at 21:03

score 1 · Answer 2 · answered Jun 14 '20 at 00:12

As per the doc, by default, the radius is expressed in meters.

I just tested out and it looks from the documentation that you need both earth_box and earth_distance in your WHERE clause statement.

So, you need to use both earth_box and earth_distance in conjunction to get correct results.

Also in doc in earth_box function description it 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 the following will return results

SELECT  earth_distance(ll_to_earth(40.749276, -73.985643), ll_to_earth(40.689266,-74.044512)) distance
FROM (SELECT 1) test
WHERE
    (earth_box(ll_to_earth(40.749276, -73.985643), 9000) @> ll_to_earth(40.689266,-74.044512))
              AND earth_distance(ll_to_earth(40.749276, -73.985643), ll_to_earth(40.689266,-74.044512)) <= 9000
     order by distance desc

but this won't as the actual distance is about 8324.429988461638 meters

SELECT  earth_distance(ll_to_earth(40.749276, -73.985643), ll_to_earth(40.689266,-74.044512)) distance
FROM (SELECT 1) test
WHERE
    (earth_box(ll_to_earth(40.749276, -73.985643), 6000) @> ll_to_earth(40.689266,-74.044512))
              AND earth_distance(ll_to_earth(40.749276, -73.985643), ll_to_earth(40.689266,-74.044512)) <= 6000
     order by distance desc

So i have to ask, that's the point of `earth_box` if you have to just check with `earth_distance` anyways? why even put the `earth_box` into the equation? — Sampson Crowley, Mar 22 '21 at 23:42
@SampsonCrowley The point is that `earth_box()` returns something that is index-able (i.e. a precalculated box that contains *at least* the points you are after). You can then use `earth_distance()` to shave off the corners and obtain the exact result. — Pieter Ennes, Apr 20 '22 at 19:04
Thank you @PieterEnnes I eventually realized this after some thought, but glad someone came to spell it out — Sampson Crowley, Oct 04 '22 at 04:05

Postgresql earthdistance - earth_box with radius

2 Answers2