DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "TrajectoryAssessment", "Model_TrajectoryAssessment", True
'--------------------------------------------------------------------------------

3.2.1.2. Assessment creation

After being cleared, TrajectoryAssessment must be filled with data about all flights from the MergedTraffic table. A simple inserting code is used to copy all fluids from MergedTraffic table to TrajectoryAssessment table.
'--------------------------------------------------------------------------------
INSERT INTO TrajectoryAssessment (Fluid, Sources, Departure, EnRoute, Arrival)

SELECT MergedTraffic.Fluid, MergedTraffic.Sources, 'I' AS Departure, 'D' AS EnRoute, 'I' AS Arriva
FROM MergedTraffic;
'--------------------------------------------------------------------------------

3.2.1.3. Trajectory assessment for AMOC flights

For departure: If the distance between departure airport and first trajectory point is inferior or equal to 20 (nm) and the flight level of the first trajectory point is inferior or equal to 30 (hundred feet) then the trajectory's departure part is complete (C). Else, the attributed value is I. For cruise (en route): If for all flight legs that are in cruise part of the trajectory, that is distance to departure airport or to arrival airport is superior to 20 (nm), the average speed (nm/hour) and the climb/descent rate (feet/minute) are inferior to 10000, then the trajectory's cruise part is fine (F). Else, the attributed value is D. For arrival: If the distance between arrival airport and last trajectory point is inferior or equal to 20 (nm) and the flight level of the last trajectory point is inferior or equal to 30 (hundred feet) then the trajectory's arrival part is complete (C). Else, the attributed value is
I.

In the previous step, the TrajectoryAssessment table was filled with all fluids from MergedTraffic and default values for Departure, EnRoute and Arrival fields (I – D - I). In this step, for all flights that belong to AMOC source application these three fields will be modified according to trajectory assessment (complete-incomplete, fine-dubious, complete-incomplete). The SQL code that changes values for these three fields, for AMOC flights is:
'--------------------------------------------------------------------------------
SELECT MergedTraffic.Fluid, MergedTraffic.Sources, IIf(([AmocFlight].[FlightLevel]<=30) And (CoordDistance([IATA_Lat_Lon_Elev_GMT].[Lat],[IATA_Lat_Lon_Elev_GMT].[Lon],[Amoc Flight].[Latitude],[AmocFlight].[Longitude])<=20),'C','I') AS Departure, IIf([MaxOfAvgSpeed]>10000 Or [MaxOfDeltaLevel]>10000,'D','F') AS EnRoute, IIf(([AmocFlight_1].[FlightLevel]<=30) And (CoordDistance([IATA_Lat_Lon_Elev_GMT_1].[Lat],[IATA_Lat_Lon_Elev_GMT_1].[Lon],[ AmocFlight_1].[Latitude],[AmocFlight_1].[Longitude])<=20),'C','I') AS Arrival
FROM (((((((MergedTraffic INNER JOIN [SELECT TrafficId, Max(Abs(AmocFlight.AvgSpeed)) AS MaxOfAvgSpeed
FROM AmocFlight
GROUP BY TrafficId;
]. AS FlightTable1 ON MergedTraffic.TrafficID = FlightTable1.TrafficId) INNER JOIN [SELECT TrafficId, Max(Abs(AmocFlight.DeltaLevel)) AS MaxOfDeltaLevel
FROM AmocFlight
GROUP BY TrafficId;
]. AS FlightTable2 ON MergedTraffic.TrafficID = FlightTable2.TrafficId) INNER JOIN [SELECT AmocFlight.TrafficId, Min(AmocFlight.UniqueID) AS MinOfUniqueID
FROM AmocFlight
GROUP BY AmocFlight.TrafficId;
]. AS FlightTable3 ON MergedTraffic.TrafficID = FlightTable3.TrafficId) INNER JOIN AmocFlight ON FlightTable3.MinOfUniqueID = AmocFlight.UniqueID) INNER JOIN IATA_Lat_Lon_Elev_GMT ON MergedTraffic.DepartureAirport = IATA_Lat_Lon_Elev_GMT.CODE) INNER JOIN [SELECT AmocFlight.TrafficId, Max(AmocFlight.UniqueID) AS MaxOfUniqueID
FROM AmocFlight
GROUP BY AmocFlight.TrafficId;
]. AS FlightTable4 ON MergedTraffic.TrafficID = FlightTable4.TrafficId) INNER JOIN IATA_Lat_Lon_Elev_GMT AS IATA_Lat_Lon_Elev_GMT_1 ON MergedTraffic.ArrivalAirport = IATA_Lat_Lon_Elev_GMT_1.CODE) INNER JOIN AmocFlight AS AmocFlight_1 ON FlightTable4.MaxOfUniqueID = AmocFlight_1.UniqueID
　
中國航空網 www.k6050.com
航空翻譯 www.aviation.cn
本文鏈接地址：AERO2K Flight Movement Inventory Project Report(71)