3.1.2.2. Merge AMOC traffic table
For merging AMOC traffic table, it is necessary to find the arrival time for each flight, because AMOC Importing tool does not provide that information completely. So, to resolve the problem, a simple solution is adopted: the last flight leg event time is considered as flight's arrival time.
'--------------------------------------------------------------------------------
INSERT INTO MergedTraffic ( Fluid, Sources, TrafficID, CallSign, DepartureTime, DepartureAirport, ArrivalAirport, AircraftType, ArrivalTime )
SELECT AmocTraffic.Fluid, AmocTraffic.Sources, AmocTraffic.TrafficId, AmocTraffic.CallSign, AmocTraffic.DepartureTime, AmocTraffic.DepartureAirport, AmocTraffic.ArrivalAirport, AmocTraffic.AircraftType, Max(AmocFlight.EventTime) AS MaxOfEventTime
FROM AmocTraffic INNER JOIN AmocFlight ON AmocTraffic.TrafficId = AmocFlight.TrafficId

GROUP BY AmocTraffic.Fluid, AmocTraffic.Sources, AmocTraffic.TrafficId, AmocTraffic.CallSign, AmocTraffic.DepartureTime, AmocTraffic.DepartureAirport, AmocTraffic.ArrivalAirport, AmocTraffic.AircraftType;
'--------------------------------------------------------------------------------

3.1.2.3. Merge ETMS traffic table

Unlike AMOC importing tool, ETMS Importing tool generates also the arrival time, so data will be copied directly from ETMSTraffic to MergedTraffic.
'--------------------------------------------------------------------------------

INSERT INTO MergedTraffic ( Fluid, Sources, TrafficID, CallSign, DepartureTime, DepartureAirport, ArrivalAirport, AircraftType, ArrivalTime )
SELECT ETMSTraffic.Fluid, ETMSTraffic.Sources, ETMSTraffic.TrafficId, ETMSTraffic.CallSign, ETMSTraffic.DepartureTime, ETMSTraffic.DepartureAirport, ETMSTraffic.ArrivalAirport, ETMSTraffic.AircraftType, ETMSTraffic.MaxStopTime
FROM ETMSTraffic;
'--------------------------------------------------------------------------------

3.1.3. Calculate average speed (AvgSpeed field values)

This step includes three sub steps: calculation of average speed, saving (into "inconsistent table") and deleting inconsistent flights.
3.1.3.1. Calculation of average speed

In MergedTraffic table, each record identifies one flight. For each flight an average speed is calculated, having the departure time, the arrival time, the departure airport position and arrival airport position (distance between airports).
'-------------------------------------------------------------------------------- 
UPDATE (MergedTraffic MergedTraffic.DepartureAirportIATA_Lat_Lon_Elev_GMTMergedTraffic.ArrivalAirportMergedTraffic.AvgSpeed INNER = IAT AS =  JOIN IATA_Lat_Lon_Elev_GMTA_Lat_Lon_Elev_GMT.CODE) INNER  IATA_Lat_Lon_Elev_GMT_1IATA_Lat_Lon_Elev_GMT_1.CODE   ON JOIN  ON SET = 

CalculateAvgSpeed([DepartureTime],[ArrivalTime],[IATA_Lat_Lon_Elev_GMT].[Lat],[IATA _Lat_Lon_Elev_GMT].[lon],IATA_Lat_Lon_Elev_GMT_1.Lat,IATA_Lat_Lon_Elev_GMT_1 .lon);
'--------------------------------------------------------------------------------

3.1.3.2. Save inconsistent flights

After the average speed is calculated, flights that have the average speed zero must be deleted. But first, they must be saved into the Inconsistent_MergedTraffic table. A flight is considered as inconsistent if it has an average speed higher than zero, because that means that either it has no departure or arrival airport, either departure time is higher than arrival time, etc…
'--------------------------------------------------------------------------------

INSERT INTO Inconsistent_MergedTraffic (Fluid, Sources, CallSign, DepartureTime, DepartureAirport, ArrivalAirport, AircraftType, ArrivalTime, AvgSpeed, Cause) SELECT Fluid, Sources, CallSign, DepartureTime, DepartureAirport, ArrivalAirport, AircraftType, ArrivalTime, AvgSpeed, 'invalid airport or average speed' AS Cause FROM MergedTraffic WHERE (((MergedTraffic.AvgSpeed)=0));
　
中國航空網 www.k6050.com
航空翻譯 www.aviation.cn
本文鏈接地址：AERO2K Flight Movement Inventory Project Report(69)