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

+3


source to share


1 answer


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.

+5


source







All Articles