4
.
A
i
r
p
o
r
t
t
a
b
l
e
s
t
r
u
c
t
u
r
e
a
n
d
r
e
l
a
t
i
o
n
s
h
i
p
s

The mix of airport codes in ETMS and AMOC data led to review the airport table. Indeed, if AMOC data used mainly 4-letters code, ETMS data used mainly three letters code (IATA or FAA codes) for American airports and four-letters code for Europe and the rest of the world. It appears that some three letters code referred to two different airports. For example, BUY can refer to Burlington airport in North Carolina in the USA (identified as KBUY by ICAO) but also to Bunbury airport in Western Australia (identified as YBUN by ICAO).
This type of problem did not allow producing a one to one static table listing IATA and ICAO airport codes. Two airport tables were then created, one used for AMOC and the rest of the world data (Table 21), another one for ETMS data (Table 22). The table created for ETMS data considered predominantly codes of airports situated on American ground.
Both airport tables were completed with data obtained from Volpe Center and various websites. The coordinates of the airports were checked using DAFIF (Digital Aeronautical Flight Information File) data.
Table 21: Table IATA_ICAO_AIRPORT_INFO.

Col #  Column Name  Data Type  Data Description 
1  IATA_AIRPORT_CODE  VARCHAR2(3)  Code of the airport as given by IATA 
2  ICAO_AIRPORT_CODE  VARCHAR2(4)  Code of the airport as given by ICAO 
3  LATITUDE  NUMBER(17,7)  Latitude of the airport in decimal degree 
4  LONGITUDE  NUMBER(17,7)  Longitude of the airport in decimal degree 
5  AIRPORT_NAME  VARCHAR2(50)  Name of the airport 
6  CITY_NAME  VARCHAR2(50)  Name of the city 
7  STATE  VARCHAR2(15)  Name of the state 
8  COUNTRY_CODE  VARCHAR2(2)  ISO country code 
9  COUNTRY_NAME  VARCHAR2(40)  Name of the country 
10  TIME_ZONE  VARCHAR2(10)  Unique code given to each location 
11  REGION  VARCHAR2(50)  Name of the region as given by ICAO 

Table 22: Table ETMS_AIRPORTS.
Col #  Column Name  Data Type  Data Desription 
1  ETMS_CODE  VARCHAR2(5)  Code of the airport as given in ETMS data 
2  IATA_CODE  VARCHAR2(4)  Code of the airport as given 

 
 
 by IATA 
3  ICAO_CODE  VARCHAR2(4)  Code of the airport as given by ICAO 
4  NAME  VARCHAR2(100)  Name of the airport 
5  CITY  VARCHAR2(50)  Name of the city 
6  CTY_NAME  VARCHAR2(30)  Name of the country 
7  CTY_CODE  VARCHAR2(2)  Country code 
8  ISO_CODE  VARCHAR2(2)  ISO country code 
9  STATE_CODE  VARCHAR2(2)  Code of the state 
10  LATITUDE  NUMBER  Latitude of the airport in decimal degree 
11  LONGITUDE  NUMBER  Longitude of the airport in decimal degree 
12  ELEVATION  NUMBER  Altidue of the airport in hundred of feet 
13  TIME_ZONE  VARCHAR2(50)  Unique code given to each location 
14  SOURCE  VARCHAR2(100)  Name of the source of the information 
15  REGION  VARCHAR2(50)  Name of the region as given by ICAO 
Figure 13: Illustration of the location of all airports listed in the table (colors attributed according to the time zone).
　
中國航空網 www.k6050.com
航空翻譯 www.aviation.cn
本文鏈接地址：AERO2K Flight Movement Inventory Project Report(22)