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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 |
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.
Read the latest GIS posts from Florian Nadler right here on our GIS blog spot.
+43 (0) 2622 93022-0
office@cybertec.at
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
But how far to 'dirt hole'? 🙂
If you want a quick estimate you could also use the spherical law of cosines:
TEST=# SELECT acos(
sin(radians(10.8230989))*sin(radians(50.1109221))
+cos(radians(10.8230989))*cos(radians(50.1109221))
*cos(radians(8.6821267)-radians(106.6296638))
)*6371;
?column?
-----------------
9644.2899148857
(1 row)
Also for small distances < 1km the law of haversines is less error prone:
TEST=# SELECT asin(
sqrt(sin((radians(50.1109221)-radians(10.8230989))/2)^2
+cos(radians(10.8230989))*cos(radians(50.1109221))
*sin((radians(8.6821267)-radians(106.6296638))/2)^2)
)*2*6371;
?column?
-----------------
9644.2899148857
(1 row)