3

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.

Paco Valdez
  • 1,915
  • 14
  • 26
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468

1 Answers1

3

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.

Community
  • 1
  • 1
Artem Koshelev
  • 10,548
  • 4
  • 36
  • 68
  • Which cords have the order reversed? I believe latitude is first, it works in Google Maps. – Evan Carroll Feb 22 '12 at 04:47
  • I don't understand your update, are you identifying a solution to my question, or a problem in your own answer? – Evan Carroll Feb 22 '12 at 05:12
  • Sorry for my English. I'm trying to tell you that points you are using are actually located somewhere in Antarctica from the Postgresql point of view due to reversed latitude and longtitude components. And it gives you the right distance, but for wrong points. – Artem Koshelev Feb 22 '12 at 05:31