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