Geographically closest 2 airports between 2 cities

Question

I have a database of all airports and need a query that finds the shortest distance between airports in 2 locations (cities for example). Here is my query.

SELECT 
  a.city, 
  a.iata_faa, 
  md.min_distance
FROM
   airports as a, (
        SELECT 
          aa.point AS pointa,
          ab.point AS pointb,
          min(st_distance(aa.point,ab.point)) AS min_distance
        FROM
          airports AS aa, 
          airports AS ab 
        WHERE 
          aa.city = 'Miami' AND ab.city = 'Atlanta'
        GROUP BY 1,2

) AS md 

WHERE
  st_distance(a.pointa, md.pointb) = md.min_distance
;

I'm closer and tried the <-> operator in the ORDER BY clause but what I'm getting is and ambiguous column reference:

ERROR:  column reference "point" is ambiguous
LINE 23:   st_distance(a.point, md.point) = md.min_distance

Revised query:

SELECT 
  a.city, 
  b.city,
  md.min_distance
FROM
  airports AS a, airports AS b,
  (
      SELECT 
        aa.point,
        ab.point,
        min(st_distance(aa.point,ab.point)) AS min_distance
      FROM
        airports AS aa, 
        airports AS ab 
      WHERE 
        aa.city = 'Miami' AND ab.city = 'Atlanta'
      GROUP BY 1,2

  ) AS md 

WHERE
  a.city = 'Miami' AND b.city = 'Atlanta' AND 
  st_distance(a.point, md.point) = md.min_distance

GROUP BY 1,2
ORDER BY a.point <-> b.point::geometry limit 1;
;    

Answer 1

If I understand correctly, you're asking for this:

Given all the airports in Atlanta ("City A"), and all the airports in Miami ("City B"), which pair of airports (one from Atlanta and one from Miami) are closest to each other?

For readability, I like using WITH clauses for this sort of thing:

WITH FirstCityAirports AS (
    SELECT * FROM airports
    WHERE airports.city = 'Atlanta'
),
SecondCityAirports AS (
    SELECT * FROM airports
    WHERE airports.city = 'Miami'
),
Distances AS (
    SELECT FirstCityAirports.name AS FirstAirportName,
        SecondCityAirports.name AS SecondAirportName,
        ST_Distance(FirstCityAirports.point, SecondCityAirports.point) AS distance
    FROM FirstCityAirports, SecondCityAirports
)
SELECT * FROM Distances ORDER BY distance DESC LIMIT 1;

You have to be careful with the ST_Distance function if your points are stored in a non-projected coordinate system (i.e. latitude and longitude). If that is the case, you'll want to use something like this instead of ST_Distance:

ST_DistanceSpheroid(FirstCityAirports.point,
    SecondCityAirports.point,
    'SPHEROID["WGS 84",6378137,298.257223563]')

Answer 2

I was able to get it to work. Here is the query:

 SELECT 
        a.city, 
        a.iata_faa,
        b.city,
        b.iata_faa,
        md.min_distance
      FROM
        airports AS a, airports AS b,
        (
            SELECT 
              aa.point,
              ab.point,
              min(st_distance(aa.point,ab.point))  AS min_distance
            FROM
              airports AS aa, 
              airports AS ab 
            WHERE
              aa.country = $1 AND ab.country = $2
            GROUP BY 1,2
            ORDER BY aa.point <-> ab.point::geometry limit 1
        ) AS md 

      WHERE
        a.country = $1 AND b.country = $2 AND 
        st_distance(a.point, b.point) = md.min_distance
      GROUP BY 1,2,3,4,5
      ;    

Now I just need to turn the distance into kilometers but this issue is solved. Thanks to those who commented.