3.3.3.11. Step 11 - Prepare data for merging
This step's purpose is to prepare ETMS data for merging. This includes first the saving of
two new values for each flight leg in the flight table (these values are the average speed
and the climb/descent rate for two consecutive flight legs that belong to the same flight)
and secondly detecting dubious flights and taking decisions in order to have a correct set
of flights for merging procedure.
Dubious flights are:
a.
One flight sectioned in two or more sets of flight legs, but with different traffic information.
b.
Flights with few flight legs, which in fact don't provide any information.
c.
Flight legs with dubious information, like average speed or climb/descent rate too high.
d.
Duplicated flights (FLUID field not unique).
This step includes 13 sub steps:
3.3.3.11.1. Sub step 11.1 - Delete temporary expanded flight table The first sub step is used to clear the content of the temporary expanded flight table, in which the calculation will take place. The executed code is:
'------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "ETMS_ExpandedFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "ETMS_ExpandedFlight", "ModelFlight", True
'------------------------------------------------------------------------------
3.3.3.11.2. Sub step 11.2 - Copy all flight legs to the expanded flight table In this sub step all flight legs from the flight table will be appended to the temporary expanded flight table, which is empty. It is necessary to use this temporary table, even if the table has the same structure as the fight table, because the UniqueID field must be filled with consecutive values for each flight and that flight must be sorted ascending by EventTime field values. So finally, in ETMS_ExpandedFlight table, each flight will have ordered flight legs and the unique identifier will have consecutive values in ascending order. The appending query SQL code is:
'------------------------------------------------------------------------------
INSERT INTO ETMS_ExpandedFlight (TrafficId, CallSign, EventTime, Latitude, Longitude, FlightLevel, GroundSpeed) SELECT ETMSFlight.TrafficId, ETMSFlight.CallSign, ETMSFlight.EventTime, ETMSFlight.Latitude, ETMSFlight.Longitude, ETMSFlight.FlightLevel, ETMSFlight.GroundSpeed FROM ETMSFlight ORDER BY ETMSFlight.TrafficId, ETMSFlight.EventTime;
'------------------------------------------------------------------------------
3.3.3.11.3. Sub step 11.3 - Calculate the average speed and the climb/descent rate To calculate these two values for each pair of two consecutive flight legs belonging to the same flight, the list of all these pairs is needed. A selecting query is used (ETMSFlightUniqueIdPairs). This query will return for each two consecutive flight legs that belong to the same flight, the following values:
1.
UID1 -first leg unique identifier
2.
UID2 - second leg unique identifier
3.
EventTime1 -first leg event time
4.
EventTime2 - second leg event time
5.
Lat1 -first leg latitude
6.
Lon1 -first leg longitude
7.
Lat2 - second leg latitude
8.
Lon2 - second leg longitude
9.
Level1 - first leg flight level
10. 中國航空網 m.k6050.com 航空翻譯 www.aviation.cn 本文鏈接地址：AERO2K Flight Movement Inventory Project Report(63)
