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.aero.cn
航空翻译 www.aviation.cn
本文链接地址：AERO2K Flight Movement Inventory Project Report(22)
