postgresqlearthdistance

Why is <@> not returning the right distance in Postgresql?


Essentially, I have three coordinates in play.

  1. Cleveland, TN -- (35.255097,-84.86844) -- This is the origin.
  2. Atlanta, GA -- (32.4608333,-84.9877778) -- This is much closer to that origin.
  3. Columbus, GA -- (33.7488889,-84.3880556) -- This is definitely further away.

Here is a Google Map with these three points for comparison.

Now using the Earthdistance module for Postgresql I'll use the <@> operator to get the air miles between the two points.

SELECT 'Celeveland, TN' AS origin
 , '(35.255097,-84.86844)' AS origin_cords
 , city || ', ' || region AS dest
 , cords AS cords
 , cords <@> '(35.255097,-84.86844)'::point AS distance_miles
FROM maxmind.city
WHERE (region = 'GA' AND city ='Atlanta') OR (region = 'GA' AND city = 'Columbus')
;

Yet this is what I get...

     origin     |     origin_cords      |     dest     |          cords           |  distance_miles  
----------------+-----------------------+--------------+--------------------------+------------------
 Celeveland, TN | (35.255097,-84.86844) | Columbus, GA | (32.4608333,-84.9877778) |  18.952732930393
 Celeveland, TN | (35.255097,-84.86844) | Atlanta, GA  | (33.7488889,-84.3880556) | 34.5888147812704
(2 rows)

So what it tells me is that Columbus, GA is closer to Cleveland (18.95mi), TN than Atlanta, GA (34.58mi) even though I can clearly tell that's not true. I've confirmed these results on Postgresql 9.1 and Postgresql 8.4.


Solution

  • Reverse coordinates order, PostGIS expects latitude to be first.

    UPD: Sorry, I've been confused by the postgis tag, this is not a PostGIS function. From the earthdistance documentation you've linked: “the first component is taken to represent longitude in degrees, and the second component is taken to represent latitude in degrees”. You are right, it works in Google maps, but in reverse order.

    UPD 2: it appears we have the whole GIS Stackexchange.