What's the quickest route between antipodes using regularly scheduled transit?

This isn't strictly a new answer (yet), but presents a dataset of antipodal airports someone might use to find a better route. Read on for the most antipodal airports and a shocking reveal about the Santiago-Xi'an route.

Continuing this exploration, I turn to the work of an a3nm, who previously engaged in some airport antipode-related tomfoolery. Using his pointer to the OpenFlights database (to whom all credit is given per their license), I can get datafiles for airports and air routes around the world.

First, I'll load the airport data into a PostgreSQL table using this procedure, and enable the table for PostGIS support so we can do spatial calculations.

We'll setup a couple of scratch columns, calculate the antipode for each airport, and convert that to a geometry (there's probably a better way to do this if you know what you're doing. A common theme here is that we don't, in fact, know what we're doing):

update airports set antipode_latitude = -latitude;
update airports set antipode_longitude = 180+longitude;
update airports set antipode_longitude = antipode_longitude-360 where antipode_longitude > 180;
update airports SET antipode = ST_SetSRID(ST_MakePoint(antipode_longitude,antipode_latitude),4326);

And sanity check the results based on some of the ones we already know about:

select airports.name, city, country, iata, ST_Distance_Sphere(airports.antipode, (select airports.geom from airports where iata='SCL')) as distance from airports order by distance limit 3;
Ankang Airport  Ankang  China   AKA 80599.02914563
Xi\\'An Xiguan  Xi\\'AN China   SIA 109730.42018116
Xianyang    Xi'an   China   XIY **124745.39283865**

Oh no! We've reveled a devastating truth about my previous answer. SCL-XIY is actually 24km too far to strictly qualify. This can probably be rectified by starting your journey a bit farther into Santiago or Xi'an and taking some kind of airport bus (which you'll have plenty of time to do if you're doing a 72-hour roundtrip), but it's a sad finding indeed.

select airports.name, city, country, iata, ST_Distance_Sphere(airports.antipode, (select airports.geom from airports where iata='AKL')) as distance from airports order by distance limit 5;
Ronda Airport   Ronda   Spain   RRA 28932.88795948
Ronda   Ronda   Spain       30772.20555266
Moron Ab    Sevilla Spain   OZP 40636.98417791
Malaga  Malaga  Spain   AGP 73182.10790714
Sevilla Sevilla Spain   SVQ 75861.92508438

The good news is that the results seem sane. Now we can find the most antipodal airports, because why not? Let's continue our trend of using the database stupidly, because it's mildly easier, and create a duplicate scratch table so we can run the query across the two tables. We'll also limit our search to airports with IATA codes, to exclude most of the random train stations in the dataset and give us the best chance of finding airports with easy-to-find commercial service:

create table airports2 (like airports including all);
insert into airports2 select * from airports;

select airports.name, airports.city, airports.country, airports.iata, airports2.name, airports2.city, airports2.country, airports2.iata, st_distance_sphere(airports.antipode, airports2.geom) as distance from airports, airports2 where airports.geom && ST_Expand(airports2.antipode, 25) and airports.iata <> '' and airports2.iata <> '' order by ST_DISTANCE(airports.geom, airports2.antipode) asc limit 1;
Sultan Mahmud Badaruddin Ii Palembang   Indonesia   PLM Benito Salas    Neiva   Colombia    NVA 5810.60702928

And sure enough, PLM and NVA are quite close:

If you're curious, and I know you are, PLM and NVA still win even if you remove the restriction that airports have IATA codes.

Now we'll query for all antipodal airports (with IATA codes) within the 100km range, trim every other entry since they're matched pairs, and produce a data file listing 366 candidate city-pairs to investigate. We can also do a slightly bigger set if we relax the 100km limit a hair and figure we can always walk a bit if nothing else.

select airports.name, airports.city, airports.country, airports.iata, airports2.name, airports2.city, airports2.country, airports2.iata, st_distance_sphere(airports.antipode, airports2.geom) as error from airports, airports2 where airports.geom && ST_Expand(airports2.antipode, 25) and airports.iata <> '' and airports2.iata <> '' order by ST_DISTANCE_sphere(airports.antipode, airports2.geom) asc limit 1000;

In our next installment, we'll see if we can find a faster route.