Calculating distance of five nearest neighbors based on latitude and longitude & conditional on other criteria

[Chhandas Bhattacharyya]

Hi all,
I have the following data -

* Example generated by -dataex-. For more info, type help dataex
clear
input str25 state str51 constituency_name int id_constituency double(longitude_const latitude_const) str28 district float(longitude_dist latitude_dist id_district)
"Andaman & Nicobar Islands" "Andaman & Nicobar Islands"  1 92.96817805308999 11.225999159636997 "Nicobar"             92.77676  9.167497 592
"Andaman & Nicobar Islands" "Andaman & Nicobar Islands"  1 92.96817805308999 11.225999159636997 "South Andaman"       93.06517 12.316763 581
"Andaman & Nicobar Islands" "Andaman & Nicobar Islands"  1 92.96817805308999 11.225999159636997 "North Andaman"         93.008 13.664213 580
"Andhra Pradesh"            "Adilabad"                   2 78.81338386515995 19.320615748491523 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Peddapalle"                 3 79.57577485435998  18.78837322944987 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Karimnagar"                 4 79.03308027930613 18.385828320028814 "Warangal"            79.80193 17.958584 494
"Andhra Pradesh"            "Nizamabad"                  5 78.37405492077372  18.75619169762445 "Mahbubnagar"         78.14144  16.50209 501
"Andhra Pradesh"            "Zahirabad"                  6 77.91974420611125 18.127830708539545 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Medak"                      7 78.45910593266693  17.88367254383691 "Guntur"              80.08513 16.304316 504
"Andhra Pradesh"            "Malkajgiri"                 8 78.55286732288674  17.52485201104889 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Secunderabad"               9  78.4726379682811 17.415650991321836 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Hyderabad"                 10 78.46370717253765 17.358966680732674 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Chevella"                  11 77.98201392955524 17.271316322236114 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Mahbubnagar"               12  77.8497551424558 16.762933490838307 "Krishna"             80.79764 16.564482 502
"Andhra Pradesh"            "Nagarkurnool"              13  78.3223089683058 16.343045085338662 "Srikakulam"          83.98566  18.58011 519
"Andhra Pradesh"            "Nalgonda"                  14  79.4102034127569 16.826681571446812 "Karimnagar"          79.23056 18.555668 488
"Andhra Pradesh"            "Bhongir"                   15  79.0656572454372 17.373255317399156 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Warangal"                  16 79.58604003451505 17.969919223182032 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Mahabubabad"               17  80.4596759256997  17.90898404475372 "Nellore"             79.69965  14.48353 510
"Andhra Pradesh"            "Khammam"                   18 80.61144235829843 17.295187274724555 "Hyderabad"           78.46864 17.390778 500
"Andhra Pradesh"            "Araku"                     19 82.59324899133026  18.08393105810147 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Srikakulam"                20   84.177788820924 18.639003109217473 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Vizianagaram"              21  83.5171335456341 18.303536599472537 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Visakhapatnam"             22  83.2325924043949 17.920530456751777 "Nizamabad"           78.14312 18.534904 487
"Andhra Pradesh"            "Anakapalle"                23 82.82121782578982 17.678922781369913 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Kakinada"                  24 82.23263723768132  17.18690195723905 "Vizianagaram"        83.37046 18.474163 520
"Andhra Pradesh"            "Amalapuram"                25 82.03009857787113 16.670990511104495 "Anantapur"            77.5757  14.48217 514
"Andhra Pradesh"            "Rajahmundry"               26 81.68594843141682  17.00909185172801 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Narsapuram"                27  81.5982398277044 16.590519751629127 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Eluru"                     28 81.17671858993603  16.88451974686367 "Rangareddy"          78.17613 17.288992 497
"Andhra Pradesh"            "Machilipatnam"             29 81.06107667440122 16.291410062036995 "Medak"               78.21442 17.888908 493
"Andhra Pradesh"            "Vijayawada"                30 80.44135130017769 16.783895688759475 "Nalgonda"            79.28371 17.072279 495
"Andhra Pradesh"            "Guntur"                    31 80.92094060408492 16.548667038509095 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Narasaraopet"              32 79.82059439929034  16.38173432260522 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Bapatla"                   33 80.28670429270522 15.878205750247082 "Khammam"             80.68621 17.595402 498
"Andhra Pradesh"            "Ongole"                    34 79.34422152362932 15.631196425196173 "Prakasam"            79.51421 15.623143 505
"Andhra Pradesh"            "Nandyal"                   35 78.41298418570942 15.503212994204702 "East Godavari"       82.00789  17.19417 523
"Andhra Pradesh"            "Kurnool"                   36 77.53051213770084 15.562487951326496 "Chittoor"            79.01334 13.468456 534
"Andhra Pradesh"            "Anantapur"                 37 77.40356440536824 14.794092220067494 "Vishakhapatnam"      82.68998 17.892574 522
"Andhra Pradesh"            "Hindupur"                  38 77.72533167347557 14.174117994804481 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Kadapa"                    39 78.61650474385662 14.679057668316652 "West Godavari"         81.401 16.896278 524
"Andhra Pradesh"            "Nellore"                   40 79.66227083592486  14.80508429048871 "Adilabad"            78.97846 19.261234 486
"Andhra Pradesh"            "Tirupati"                  41 79.79458738492971 13.945780761516255 "Kurnool"             78.00491 15.537107 507
"Andhra Pradesh"            "Rajampet"                  42  78.8324922054124 13.899452853706714 "Cuddapah"            78.78225 14.461016 511
"Andhra Pradesh"            "Chittoor"                  43 79.00303798616177 13.256036464966256 "West Godavari"         81.401 16.896278 524
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Lower Dibang Valley" 95.77615 28.274437 286
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "East Siang"          95.16434  28.12273 243
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Papumpare"           93.59371 27.289156 250
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Lohit"               96.19165 27.840454 287
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Dibang Valley"        95.9361 28.882183 285
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Lower Subansiri"     93.93581 27.625887 248
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Tirap"               95.43756 26.946495 291
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Upper Siang"         94.99305  29.16618 241
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Upper Subansiri"     93.93407  28.28965 244
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "East Kameng"           92.964  27.44255 246
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Tawang"              91.95289 27.662634 247
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Anjaw"                96.8282 28.061714 288
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "West Siang"          94.50751   28.3222 242
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Kurung Kumey"        93.38803 28.000763 245
"Arunachal Pradesh"         "Arunachal West"            44 93.48957952256596 27.857617710806068 "Changlang"           96.34138  27.36369 290
"Arunachal Pradesh"         "Arunachal East"            45 95.90123066942066 28.226031461870946 "West Kameng"         92.40787 27.303205 252
"Assam"                     "Karimganj"                 46 92.47557701160522 24.554165683059622 "Barpeta"             91.02546 26.437153 261
"Assam"                     "Silchar"                   47 92.85486342959742 24.798072458871673 "North Cachar Hills"  93.03106 25.377554 277
"Assam"                     "Autonomous District"       48 93.11412542577268 25.817486848700877 "Cachar"              92.85336 24.811785 283
"Assam"                     "Dhubri"                    49 90.16606902239583 26.089918129015437 "Hailakandi"          92.58573  24.49468 453
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Lakhimpur"           94.04574  27.15839 251
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Tinsukia"            95.62374 27.575405 289
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Jorhat"              94.27184  26.79139 254
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Kamrup"              91.53639 26.135096 269
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Karimganj"           92.37442 24.581865 448
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Golaghat"            93.83142 26.479424 258
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Sibsagar"            94.81663 27.007664 255
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Kokrajhar"           90.26758  26.60488 209
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Marigaon"            92.27891 26.285145 267
"Assam"                     "Kokrajhar"                 50 90.68691850076684   26.6125067749359 "Sonitpur"            92.92029  26.78521 256
"Assam"                     "Barpeta"                   51 90.90349310230972  26.33030501463461 "Nalbari"             91.43639  26.53143 259
"Assam"                     "Gauhati"                   52 91.38173993861915 26.074217452204778 "Dhemaji"             94.69566  27.50464 249
"Assam"                     "Mangaldoi"                 53 91.96770005783328 26.565505574316603 "Nagaon"              92.84622  26.23494 265
"Assam"                     "Tezpur"                    54 93.04994892341438 26.810799168855027 "Karbi Anglong"       93.40475  26.15579 266
"Assam"                     "Nawgong"                   55 92.62357538470009 26.128869107365965 "Bongaigaon"          90.61163  26.44504 264
"Assam"                     "Kaliabor"                  56 93.48326943982131 26.482609058056532 "Dibrugarh"           95.05091  27.36838 253
"Assam"                     "Jorhat"                    57 94.61593328095458  26.89331769232362 "Darrang"             92.02704  26.60516 257
"Assam"                     "Dibrugarh"                 58 95.30420468089486 27.361800899996453 "Dhuburi"             89.73599 26.306864 219
"Assam"                     "Lakhimpur"                 59 94.76218433958623  27.43923256111814 "Goalpara"            90.60036  26.05722 271
"Bihar"                     "Valmiki Nagar"             60 84.29418510966083 27.148883221127452 "Jamui"               86.29292  24.78425 240
"Bihar"                     "Paschim Champaran"         61 84.69514097674238 26.805640514650413 "Munger"              86.52502  25.21039 231
"Bihar"                     "Purvi Champaran"           62 84.76619679375274 26.540944825871936 "Madhepura"           86.89391  25.82627 221
"Bihar"                     "Sheohar"                   63 85.23253783783277 26.590122076664755 "Bhagalpur"           87.08439   25.2709 230
"Bihar"                     "Sitamarhi"                 64 85.59500862413432 26.582263731086407 "Muzaffarpur"          85.3294  26.15189 174
"Bihar"                     "Madhubani"                 65 85.93731150078678  26.37550424626954 "Lakhisarai"          86.12888 25.155886 232
"Bihar"                     "Jhanjharpur"               66 86.34331678874432 26.393252173037634 "Araria"              87.35212   26.1992 217
"Bihar"                     "Supaul"                    67 86.61075242213673 26.234556150678323 "Madhubani"           86.21628 26.405573 214
"Bihar"                     "Araria"                    68 87.36056782242305  26.19730574771291 "Munger"              86.52502  25.21039 231
"Bihar"                     "Kishanganj"                69 87.86511519612829 26.182767940047526 "Supaul"              86.79758  26.24785 215
"Bihar"                     "Katihar"                   70 87.68366587715613 25.535794708909965 "Munger"              86.52502  25.21039 231
"Bihar"                     "Purnia"                    71 87.30711294780446 25.751331840828808 "Saharsa"             86.58745  25.81696 220
"Bihar"                     "Madhepura"                 72 86.77368792566953 25.786594586180165 "Chhapra"             84.82576 25.905203 178
"Bihar"                     "Darbhanga"                 73  86.1179922409104 26.076137438575152 "Gaya"                84.93409 24.699785 202
"Bihar"                     "Muzaffarpur"               74 85.52227638074196 26.114346499612157 "Darbhanga"             86.023  26.11414 218
"Bihar"                     "Vaishali"                  75   85.179523288049 26.147419563268144 "Purnia"              87.40462  25.81652 222
end

I want to do two things. Firstly, I want to calculate the distance of up to five nearest districts for all observations in constituency_name, conditional on whether they lie within the same state. I saw a post in the archives which gives a code to get the distance to second nearest neighbour, but it also mentions that the calculation is often inaccurate.
My second objective is to arrange the dataset by constituency_name such that all its nearest neighbours in terms of district come first, then for the second constituency_name and so on. So for example, the first five rows will have the five nearest neighbours in terms of district by "Adilabad" and from the 6th to 10th rows it will have the nearest neighbour by district of "Peddapalle" and so on.
I am new to Stata and it's my first time posting on this forum.
Looking forward to helpful guidance from you all.
Thanks in advance!
Chhandas Bhattacharyya.

[Mike Lacy]

I'd do this using the community-contribute program -geonear- (-ssc describe geonear-). I'd match with more than 5 neighbors, and then discard the ones outside the state. Your desire for a rearrangement of the data is likely not necessary for analysis, but I believe can be achieved by using the built-in -sort- or -gsort-, about which see -help sort-.

Regarding the use of -geonear-, here is something illustrative of technique, but perhaps not perfect, as I may not entirely understand what you want:

egen id_obs = group(id*) // create an observation id
// Create a file of potential neighbors
preserve
rename id_obs id_nay
tempfile nayfile
save `nayfile'
restore
// State info for later merge
preserve
keep id_obs state
tempfile state
save `state'
restore
//  Create a long-layour file of 10 nearest neighbor pairs for each original observation.
geonear id_obs latitude_dist longitude_dist using `nayfile', ///
   neighbors(id_nay latitude_dist longitude_dist) ignoreself long ///
   nearcount(10) // get extra to permit discards
//   
// Get state info. and drop pairs outside the state.
merge m:1 id_obs using `state'
bysort id_obs: drop if state != state[1]
//
// Five or fewer matched pairs.
bysort id_obs: keep if _n <=5

[Chhandas Bhattacharyya]

Thank you, Mike!
I have been able to generate the required results using the code you provided.
Regards,
Chhandas Bhattacharyya