On my way back from Vietnam I was watching the screen in front of me displaying the “Distance to destination”. On such a long flight the number you see in front of you can be quite depressing so I spent some time to figure out, how this number could be calculated with PostgreSQL.

Earthdistance comes to the rescue

The earthdistance module allows you to calculate the so called “great circle distance” using PostgreSQL. What is a “great circle distance”? If you want to move from A to B you got basically two choices: Go a straight line. Due to the fact that the earth is not flat, a straight line is not the shortest connection between two points in the face of the earth.

The shortest connection can be found if the course is constantly adjusted. The “great circle distance” is the shortest distance found by constantly adjusting the course.

Two modules are needed to do our calculation: cube and earthdistance:

test=# CREATE EXTENSION cube;

CREATE EXTENSION

test=# CREATE EXTENSION earthdistance;

CREATE EXTENSION

In the next step we can try to figure out the coordinates of our two locations. To do that I am using the PL/Python function presented in the previous blog post:

test=# SELECT * FROM geo('saigon');

                      location                      |    lat     |     lng    

----------------------------------------------------+------------+-------------

 Ho Chi Minh City, District 7, Ho Chi Minh, Vietnam | 10.8230989 | 106.6296638

(1 row)

Saigon is now called Ho Chi Minh City. The coordinates returned by the functions are correct. Now we can check what the function returns for Frankfurt:

test=# SELECT * FROM geo('frankfurt');

      location      |    lat     |    lng   

--------------------+------------+-----------

 Frankfurt, Germany | 50.1109221 | 8.6821267

(1 row)

Earthdistance provides us with a datatype called “earth”. Converting our coordinates to “earth” is the key to doing the final calculation:

test=# SELECT ll_to_earth(50.1109221, 8.6821267);

                      ll_to_earth                     

-------------------------------------------------------

 (4043469.56789498, 617446.96485031, 4893888.03869973)

(1 row)

The final calculation works like this:

test=# SELECT earth_distance(

            ll_to_earth(50.1109221, 8.6821267),

            ll_to_earth(10.8230989, 106.6296638)) / 1000;

    ?column?    

-----------------

 9655.1406871522

(1 row)

Note that the distance is returned in meters so we have to divide by 1000.

9600km sounds pretty reasonable. Let us cross check: http://www.luftlinie.org/Saigon/Frankfurt-Flughafen

The results are correct.