Postgresql earthdistance - earth_box with radius
Please explain to me this behavior of the earth_box function ... or what am I 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 the Empire State Building to the 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 the Statue of Liberi, which is over 8000m from the Empire State Buildng, but select the return line ID 1, even if the radius is only 5558m! Can you explain this behavior to me 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
extension and postgresql versions
=> \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 NE
source to share
The problem is that the earth_box gets stat miles. 8324.42998846164 meters about 5.172560986623845 statute miles Unit Converter
Solution: Convert Radius to Charter Miles
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.
source to share