3.3.3.5.3. Sub step 5.3 - Link each flight head with its flight legs The principle of this sub step is to link a flight leg to a flight head if the flight leg's CallSign is identical with the head's CallSign and the flight leg's EventTime is between head's DepartureTime and MaxStopTime (arrival time). If the condition is accomplished the flight leg's TrafficId will be updated to flight head's TrafficId. Otherwise the TrafficId will remain 0. The updating query SQL code is:
'------------------------------------------------------------------------------
UPDATE ETMSTraffic INNER JOIN ETMSFlight ON ETMSTraffic.CallSign = ETMSFlight.CallSign SET ETMSFlight.TrafficId = [ETMSTraffic].[TrafficId] WHERE (((ETMSFlight.EventTime)>=[DepartureTime] And (ETMSFlight.EventTime)<=[MaxStopTime]));
'------------------------------------------------------------------------------
3.3.3.5.4. Sub step 5.4 - Delete the temporary index csTime (CallSign + EventTime) In order to decrease database's size, all unnecessary indexes must be deleted. After being used in the precedent sub step, the csTime index belonging to the flight table, must deleted because already another index duplicates it. This index is tfTime (TrafficId + EventTime) (cf. Table 60). To delete csTime index the following SQL code is used:
'------------------------------------------------------------------------------
DROP INDEX csTime ON ETMSFlight;
'------------------------------------------------------------------------------
3.3.3.5.5. Sub step 5.5 - Delete inconsistent records from flight table All unlinked flight legs must be deleted. These legs have TrafficId = 0. The deletion query SQL code is:
'------------------------------------------------------------------------------
DELETE * FROM ETMSFlight WHERE (TrafficId=0);
'------------------------------------------------------------------------------
3.3.3.5.6. Sub step 5.6 - Update fields in traffic table according to AERO2k specific data type To reach a state as closest as possible to the final state, the traffic table data must be updated to the specific AERO2K data type. For example the CallSign values before this sub step includes the ETMS-generated flight_UID that must be removed. The updating query SQL code is:
'------------------------------------------------------------------------------
UPDATE ETMSTraffic SET ETMSTraffic.CallSign = Right([CallSign],Len([CallSign])-6);
'------------------------------------------------------------------------------
3.3.3.5.7. Sub step 5.7 - Update fields in flight table according to AERO2K specific data type
The same thing as in the precedent sub step must be done for the flight table. The updating query SQL code is:
'------------------------------------------------------------------------------
UPDATE ETMSFlight SET ETMSFlight.CallSign = Right([CallSign],Len([CallSign])-6), ETMSFlight.Latitude = [Latitude]/60, ETMSFlight.Longitude = [Longitude]/60;
'------------------------------------------------------------------------------
3.3.3.5.8. Sub step 5.8 - Create tfTime index (TrafficId + EventTime in ETMSFlight table)
To create tfTime index, the following SQL code is used:
'------------------------------------------------------------------------------
CREATE INDEX tfTime ON ETMSFlight (TrafficId, EventTime);
'------------------------------------------------------------------------------
中國航空網 www.k6050.com
航空翻譯 www.aviation.cn
本文鏈接地址：AERO2K Flight Movement Inventory Project Report(61)
