3.3.3.6. Step 6 - Change airport codes to a preferred code (IATA)
In order to have a standard data for further use, independent from the source application (Amoc, ETMS, Back Aviation, …), all data that may differ from one application to another will be converted to AERO2K data type. Two of the fields that will be changed are the departure airport and the arrival airport. Their codes will be converted to IATA code. The updating query SQL code is:
'--------------------------------------------------------------------------------
UPDATE ETMSTraffic INNER JOIN IATA_ICAO_Link ON ETMSTraffic.DepartureAirport = IATA_ICAO_Link.ICAO SET ETMSTraffic.DepartureAirport = [IATA];
'--------------------------------------------------------------------------------
UPDATE ETMSTraffic INNER JOIN IATA_ICAO_Link ON ETMSTraffic.ArrivalAirport = IATA_ICAO_Link.ICAO SET ETMSTraffic.ArrivalAirport = [IATA];
'--------------------------------------------------------------------------------
The IATA_ICAO_Link is a table that stores the links between different codes of the same airport (IATA, ICAO).
3.3.3.7. Step 7 - Consistency check
To complete this step, two sub steps must be done.
3.3.3.7.1. Sub step 7.1 - Delete inconsistent flight data
The inconsistent data is:
.
Flight heads without flight legs.
.
Flight legs without flight head.
Before deleting all inconsistent data, it is saved in the ETMS_InconsistentTraffic (flight heads) and ETMS_InconsistentFlight (flight legs).
3.3.3.7.2. Sub step 7.2 - Update flight legs that belong to the same flight and have identical EventTime
This sub step implies searching for flight legs with the same call sign and event time. After detecting all sets of similar flight legs, for each set, the first flight leg will remain unchanged and each next flight leg's event time will be incremented step by step with 1 second. This method is temporary, a better solution should be found in further versions.
3.3.3.8. Step 8 - Change all Date/Time field values to GMT time
The ETMS application provides data with all Date/Time fields already converted to GMT time, so in this step nothing happens. It exists just for compatibility with all other AERO2K importing tools.
3.3.3.9. Step 9 -Calculate the fluid
The fluid field is a unique identifier for a flight. It is calculated from flight head's importing data. Fluid = CCCCCCCAAAAYYMMHHNN CCCCCCC = CallSign, if CallSign length is smaller than 7, then the rest of this CCCCCCC field will be filled with '-' character AAAA = DepartureAirport, if DepartureAirport length is smaller than 4, then the rest of this AAAA field will be filled with '-' character YYMMHHNN = Format (DepartureTime, "yymmhhnn"). The leading 0 for each YY, MM, HH and NN field components is important. To complete this step, next updating query SQL code is executed:
'------------------------------------------------------------------------
UPDATE ETMSTraffic SET ETMSTraffic.Fluid = [CallSign] & String(7-Len([CallSign]),'-') & IIf(IsNull([DepartureAirport]),'',[DepartureAirport]) & String(4-Len(IIf(IsNull([DepartureAirport]),'',[DepartureAirport])),'-') & Right(Trim(Str(Year([DepartureTime]))),2) & IIf(Month([DepartureTime])<10,'0','') & Trim(Str(Month([DepartureTime]))) & IIf(Day([DepartureTime])<10,'0','') & Trim(Str(Day([DepartureTime]))) & IIf(Hour([DepartureTime])<10,'0','') & Trim(Str(Hour([DepartureTime]))) & IIf(Minute([DepartureTime])<10,'0','') & Trim(Str(Minute([DepartureTime]))) WHERE (((True)<>False));
'------------------------------------------------------------------------
3.3.3.10. Step 10 - Split the flights that pass the midnight
The splitting procedure must find all the flights that pass from one day to the next one. To split the flight at midnight a new record is inserted between the record with EventTime just before midnight and the next record, that implicitly passes midnight. 中国航空网 www.aero.cn 航空翻译 www.aviation.cn 本文链接地址：AERO2K Flight Movement Inventory Project Report(62)
