Compute distance in kms using longitudes & latitudes in a wide format

[Killian Foubert]

Dear Stata users

For my PhD thesis I need to compute the distance in kilometers between every provinces in some countries. Here you can find an example with Afghanistan. I constructed the full bilateral matrix myself.

NAME_1 corresponds to the name of the first province
ProvB corresponds to the name of the second province
_ID corresponds to the ID of the first province
_IDB corresponds to the ID of the second province
_CX & _CY correspond to the coordinates of my first province
_CXB & _CYB correspond to the coordinates of my second province

What I would like to do is create a new variable called distance, with the distance in kilometers between every provinces. I thought about the spdistance already but I am not sure how to use it. Does someone have any idea how to do that?

Thank you in advance, and have a good day.
Killian

 * Example generated by -dataex-. To install: ssc install dataex clear input str10(NAME_1 ProvB) float(_ID _ID2) double(_CX _CY) float(_CXB _CYB) "Badakhshan" "Badghis" 1 2 71.45897693843455 37.03289461515222 63.7527 35.13975 "Badakhshan" "Baghlan" 1 3 71.45897693843455 37.03289461515222 68.713875 35.70536 "Badakhshan" "Balkh" 1 4 71.45897693843455 37.03289461515222 67.13496 36.64803 "Badakhshan" "Bamyan" 1 5 71.45897693843455 37.03289461515222 67.181305 34.72142 "Badakhshan" "Daykundi" 1 6 71.45897693843455 37.03289461515222 66.18399 33.75492 "Badakhshan" "Farah" 1 7 71.45897693843455 37.03289461515222 62.30092 32.605053 "Badakhshan" "Faryab" 1 8 71.45897693843455 37.03289461515222 64.863556 36.069233 "Badakhshan" "Ghazni" 1 9 71.45897693843455 37.03289461515222 67.82718 33.28918 "Badakhshan" "Ghor" 1 10 71.45897693843455 37.03289461515222 64.88652 34.18288 "Badakhshan" "Hilmand" 1 11 71.45897693843455 37.03289461515222 64.01518 31.139856 "Badakhshan" "Hirat" 1 12 71.45897693843455 37.03289461515222 62.12152 34.19075 "Badakhshan" "Jawzjan" 1 13 71.45897693843455 37.03289461515222 65.866234 36.818436 "Badakhshan" "Kabul" 1 14 71.45897693843455 37.03289461515222 69.3109 34.55905 "Badakhshan" "Kandahar" 1 15 71.45897693843455 37.03289461515222 65.69916 30.97302 "Badakhshan" "Kapisa" 1 16 71.45897693843455 37.03289461515222 69.61837 34.96282 "Badakhshan" "Khost" 1 17 71.45897693843455 37.03289461515222 69.87198 33.37244 "Badakhshan" "Kunar" 1 18 71.45897693843455 37.03289461515222 71.11573 34.964382 "Badakhshan" "Kunduz" 1 19 71.45897693843455 37.03289461515222 68.75136 36.839554 "Badakhshan" "Laghman" 1 20 71.45897693843455 37.03289461515222 70.14319 34.756153 "Badakhshan" "Logar" 1 21 71.45897693843455 37.03289461515222 69.07781 33.99335 "Badakhshan" "Nangarhar" 1 22 71.45897693843455 37.03289461515222 70.468254 34.274925 "Badakhshan" "Nimroz" 1 23 71.45897693843455 37.03289461515222 62.31349 30.805174 "Badakhshan" "Nuristan" 1 24 71.45897693843455 37.03289461515222 70.79991 35.411602 "Badakhshan" "Paktika" 1 25 71.45897693843455 37.03289461515222 68.75298 32.477806 "Badakhshan" "Paktya" 1 26 71.45897693843455 37.03289461515222 69.42431 33.68051 "Badakhshan" "Panjshir" 1 27 71.45897693843455 37.03289461515222 69.82158 35.455067 "Badakhshan" "Parwan" 1 28 71.45897693843455 37.03289461515222 68.88772 34.968697 "Badakhshan" "Samangan" 1 29 71.45897693843455 37.03289461515222 67.74056 35.94946 "Badakhshan" "Sari Pul" 1 30 71.45897693843455 37.03289461515222 66.16863 35.714645 "Badakhshan" "Takhar" 1 31 71.45897693843455 37.03289461515222 69.778984 36.704178 "Badakhshan" "Uruzgan" 1 32 71.45897693843455 37.03289461515222 66.00427 32.819263 "Badakhshan" "Wardak" 1 33 71.45897693843455 37.03289461515222 68.28417 34.317154 "Badakhshan" "Zabul" 1 34 71.45897693843455 37.03289461515222 67.104996 32.23607 "Badghis" "Badakhshan" 2 1 63.752695914679826 35.139751366529445 71.45898 37.032894 "Badghis" "Baghlan" 2 3 63.752695914679826 35.139751366529445 68.713875 35.70536 "Badghis" "Balkh" 2 4 63.752695914679826 35.139751366529445 67.13496 36.64803 "Badghis" "Bamyan" 2 5 63.752695914679826 35.139751366529445 67.181305 34.72142 "Badghis" "Daykundi" 2 6 63.752695914679826 35.139751366529445 66.18399 33.75492 "Badghis" "Farah" 2 7 63.752695914679826 35.139751366529445 62.30092 32.605053 "Badghis" "Faryab" 2 8 63.752695914679826 35.139751366529445 64.863556 36.069233 "Badghis" "Ghazni" 2 9 63.752695914679826 35.139751366529445 67.82718 33.28918 "Badghis" "Ghor" 2 10 63.752695914679826 35.139751366529445 64.88652 34.18288 "Badghis" "Hilmand" 2 11 63.752695914679826 35.139751366529445 64.01518 31.139856 "Badghis" "Hirat" 2 12 63.752695914679826 35.139751366529445 62.12152 34.19075 "Badghis" "Jawzjan" 2 13 63.752695914679826 35.139751366529445 65.866234 36.818436 "Badghis" "Kabul" 2 14 63.752695914679826 35.139751366529445 69.3109 34.55905 "Badghis" "Kandahar" 2 15 63.752695914679826 35.139751366529445 65.69916 30.97302 "Badghis" "Kapisa" 2 16 63.752695914679826 35.139751366529445 69.61837 34.96282 "Badghis" "Khost" 2 17 63.752695914679826 35.139751366529445 69.87198 33.37244 "Badghis" "Kunar" 2 18 63.752695914679826 35.139751366529445 71.11573 34.964382 "Badghis" "Kunduz" 2 19 63.752695914679826 35.139751366529445 68.75136 36.839554 "Badghis" "Laghman" 2 20 63.752695914679826 35.139751366529445 70.14319 34.756153 "Badghis" "Logar" 2 21 63.752695914679826 35.139751366529445 69.07781 33.99335 "Badghis" "Nangarhar" 2 22 63.752695914679826 35.139751366529445 70.468254 34.274925 "Badghis" "Nimroz" 2 23 63.752695914679826 35.139751366529445 62.31349 30.805174 "Badghis" "Nuristan" 2 24 63.752695914679826 35.139751366529445 70.79991 35.411602 "Badghis" "Paktika" 2 25 63.752695914679826 35.139751366529445 68.75298 32.477806 "Badghis" "Paktya" 2 26 63.752695914679826 35.139751366529445 69.42431 33.68051 "Badghis" "Panjshir" 2 27 63.752695914679826 35.139751366529445 69.82158 35.455067 "Badghis" "Parwan" 2 28 63.752695914679826 35.139751366529445 68.88772 34.968697 "Badghis" "Samangan" 2 29 63.752695914679826 35.139751366529445 67.74056 35.94946 "Badghis" "Sari Pul" 2 30 63.752695914679826 35.139751366529445 66.16863 35.714645 "Badghis" "Takhar" 2 31 63.752695914679826 35.139751366529445 69.778984 36.704178 "Badghis" "Uruzgan" 2 32 63.752695914679826 35.139751366529445 66.00427 32.819263 "Badghis" "Wardak" 2 33 63.752695914679826 35.139751366529445 68.28417 34.317154 "Badghis" "Zabul" 2 34 63.752695914679826 35.139751366529445 67.104996 32.23607 "Baghlan" "Badakhshan" 3 1 68.71387657891233 35.70536172348113 71.45898 37.032894 "Baghlan" "Badghis" 3 2 68.71387657891233 35.70536172348113 63.7527 35.13975 "Baghlan" "Balkh" 3 4 68.71387657891233 35.70536172348113 67.13496 36.64803 "Baghlan" "Bamyan" 3 5 68.71387657891233 35.70536172348113 67.181305 34.72142 "Baghlan" "Daykundi" 3 6 68.71387657891233 35.70536172348113 66.18399 33.75492 "Baghlan" "Farah" 3 7 68.71387657891233 35.70536172348113 62.30092 32.605053 "Baghlan" "Faryab" 3 8 68.71387657891233 35.70536172348113 64.863556 36.069233 "Baghlan" "Ghazni" 3 9 68.71387657891233 35.70536172348113 67.82718 33.28918 "Baghlan" "Ghor" 3 10 68.71387657891233 35.70536172348113 64.88652 34.18288 "Baghlan" "Hilmand" 3 11 68.71387657891233 35.70536172348113 64.01518 31.139856 "Baghlan" "Hirat" 3 12 68.71387657891233 35.70536172348113 62.12152 34.19075 "Baghlan" "Jawzjan" 3 13 68.71387657891233 35.70536172348113 65.866234 36.818436 "Baghlan" "Kabul" 3 14 68.71387657891233 35.70536172348113 69.3109 34.55905 "Baghlan" "Kandahar" 3 15 68.71387657891233 35.70536172348113 65.69916 30.97302 "Baghlan" "Kapisa" 3 16 68.71387657891233 35.70536172348113 69.61837 34.96282 "Baghlan" "Khost" 3 17 68.71387657891233 35.70536172348113 69.87198 33.37244 "Baghlan" "Kunar" 3 18 68.71387657891233 35.70536172348113 71.11573 34.964382 "Baghlan" "Kunduz" 3 19 68.71387657891233 35.70536172348113 68.75136 36.839554 "Baghlan" "Laghman" 3 20 68.71387657891233 35.70536172348113 70.14319 34.756153 "Baghlan" "Logar" 3 21 68.71387657891233 35.70536172348113 69.07781 33.99335 "Baghlan" "Nangarhar" 3 22 68.71387657891233 35.70536172348113 70.468254 34.274925 "Baghlan" "Nimroz" 3 23 68.71387657891233 35.70536172348113 62.31349 30.805174 "Baghlan" "Nuristan" 3 24 68.71387657891233 35.70536172348113 70.79991 35.411602 "Baghlan" "Paktika" 3 25 68.71387657891233 35.70536172348113 68.75298 32.477806 "Baghlan" "Paktya" 3 26 68.71387657891233 35.70536172348113 69.42431 33.68051 "Baghlan" "Panjshir" 3 27 68.71387657891233 35.70536172348113 69.82158 35.455067 "Baghlan" "Parwan" 3 28 68.71387657891233 35.70536172348113 68.88772 34.968697 "Baghlan" "Samangan" 3 29 68.71387657891233 35.70536172348113 67.74056 35.94946 "Baghlan" "Sari Pul" 3 30 68.71387657891233 35.70536172348113 66.16863 35.714645 "Baghlan" "Takhar" 3 31 68.71387657891233 35.70536172348113 69.778984 36.704178 "Baghlan" "Uruzgan" 3 32 68.71387657891233 35.70536172348113 66.00427 32.819263 "Baghlan" "Wardak" 3 33 68.71387657891233 35.70536172348113 68.28417 34.317154 "Baghlan" "Zabul" 3 34 68.71387657891233 35.70536172348113 67.104996 32.23607 "Balkh" "Badakhshan" 4 1 67.13495651828454 36.648027822673775 71.45898 37.032894 end

[Killian Foubert]

And sorry, I actually meant a long format, not wide

[Robert Picard]

You've already done the hard part and created all the pairwise combinations of province centroids. You can use geodist (from SSC) to compute the distances. Here's a quick example using the first 10 observations of your data example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10(NAME_1 ProvB) float(_ID _ID2) double(_CX _CY) float(_CXB _CYB)
"Badakhshan" "Badghis"  1  2 71.45897693843455 37.03289461515222   63.7527  35.13975
"Badakhshan" "Baghlan"  1  3 71.45897693843455 37.03289461515222 68.713875  35.70536
"Badakhshan" "Balkh"    1  4 71.45897693843455 37.03289461515222  67.13496  36.64803
"Badakhshan" "Bamyan"   1  5 71.45897693843455 37.03289461515222 67.181305  34.72142
"Badakhshan" "Daykundi" 1  6 71.45897693843455 37.03289461515222  66.18399  33.75492
"Badakhshan" "Farah"    1  7 71.45897693843455 37.03289461515222  62.30092 32.605053
"Badakhshan" "Faryab"   1  8 71.45897693843455 37.03289461515222 64.863556 36.069233
"Badakhshan" "Ghazni"   1  9 71.45897693843455 37.03289461515222  67.82718  33.28918
"Badakhshan" "Ghor"     1 10 71.45897693843455 37.03289461515222  64.88652  34.18288
"Badakhshan" "Hilmand"  1 11 71.45897693843455 37.03289461515222  64.01518 31.139856
end

geodist _CY _CX _CYB _CXB, gen(d)
list

and here are the results:

. list

     +----------------------------------------------------------------------------------------------+
     |     NAME_1      ProvB   _ID   _ID2         _CX         _CY       _CXB       _CYB           d |
     |----------------------------------------------------------------------------------------------|
  1. | Badakhshan    Badghis     1      2   71.458977   37.032895    63.7527   35.13975   724.90812 |
  2. | Badakhshan    Baghlan     1      3   71.458977   37.032895   68.71387   35.70536   287.01499 |
  3. | Badakhshan      Balkh     1      4   71.458977   37.032895   67.13496   36.64803   388.01388 |
  4. | Badakhshan     Bamyan     1      5   71.458977   37.032895    67.1813   34.72142   463.59604 |
  5. | Badakhshan   Daykundi     1      6   71.458977   37.032895   66.18399   33.75492   601.42377 |
     |----------------------------------------------------------------------------------------------|
  6. | Badakhshan      Farah     1      7   71.458977   37.032895   62.30092   32.60505   970.54289 |
  7. | Badakhshan     Faryab     1      8   71.458977   37.032895   64.86356   36.06923   599.97549 |
  8. | Badakhshan     Ghazni     1      9   71.458977   37.032895   67.82718   33.28918   530.93028 |
  9. | Badakhshan       Ghor     1     10   71.458977   37.032895   64.88652   34.18288   674.02794 |
 10. | Badakhshan    Hilmand     1     11   71.458977   37.032895   64.01518   31.13986   947.66322 |
     +----------------------------------------------------------------------------------------------+

.

[Killian Foubert]

Thanks a lot! That's perfect