3.2.1.4. Trajectory assessment for ETMS flights
The SQL code that changes values for these three fields, for ETMS flights is:
'--------------------------------------------------------------------------------
SELECT MergedTraffic.Fluid, MergedTraffic.Sources, IIf(([ETMSFlight].[FlightLevel]<=30) And (CoordDistance([IATA_Lat_Lon_Elev_GMT].[Lat],[IATA_Lat_Lon_Elev_GMT].[Lon],[ETM SFlight].[Latitude],[ETMSFlight].[Longitude])<=20),'C','I') AS Departure, IIf([MaxOfAvgSpeed]>10000 Or [MaxOfDeltaLevel]>10000,'D','F') AS EnRoute, IIf(([ETMSFlight_1].[FlightLevel]<=30) And (CoordDistance([IATA_Lat_Lon_Elev_GMT_1].[Lat],[IATA_Lat_Lon_Elev_GMT_1].[Lon],[ ETMSFlight_1].[Latitude],[ETMSFlight_1].[Longitude])<=20),'C','I') AS Arrival
FROM (((((((MergedTraffic INNER JOIN [SELECT TrafficId, Max(Abs(ETMSFlight.AvgSpeed)) AS MaxOfAvgSpeed FROM ETMSFlight
GROUP BY TrafficId; ]. AS FlightTable1 ON MergedTraffic.TrafficID = FlightTable1.TrafficId) INNER JOIN [SELECT TrafficId, Max(Abs(ETMSFlight.DeltaLevel)) AS MaxOfDeltaLevel
FROM ETMSFlight GROUP BY TrafficId; ]. AS FlightTable2 ON MergedTraffic.TrafficID = FlightTable2.TrafficId) INNER JOIN
[SELECT ETMSFlight.TrafficId, Min(ETMSFlight.UniqueID) AS MinOfUniqueID FROM ETMSFlight GROUP BY ETMSFlight.TrafficId; ]. AS FlightTable3 ON MergedTraffic.TrafficID = FlightTable3.TrafficId) INNER JOIN
ETMSFlight ON FlightTable3.MinOfUniqueID = ETMSFlight.UniqueID) INNER JOIN IATA_Lat_Lon_Elev_GMT ON MergedTraffic.DepartureAirport = IATA_Lat_Lon_Elev_GMT.CODE) INNER JOIN [SELECT ETMSFlight.TrafficId, Max(ETMSFlight.UniqueID) AS MaxOfUniqueID
FROM ETMSFlight GROUP BY ETMSFlight.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 ETMSFlight AS ETMSFlight_1 ON FlightTable4.MaxOfUniqueID = ETMSFlight_1.UniqueID
WHERE (((MergedTraffic.Sources)='ETMS'));
'--------------------------------------------------------------------------------
3.2.2. Merge AMOC and ETMS flight tables
3.2.2.1. Clear MergedFlight table for a new merging process
Until now all data concerning flight legs was retrieved directly from importing tools. Before merging the flight legs, the merged flight table must be cleared:
'--------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "MergedFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "MergedFlight", "Model_MergedFlight", True
'--------------------------------------------------------------------------------
3.2.2.2. Merge AMOC flight table
The AMOC flight legs merging includes two steps:
3.2.2.2.1. Merge flight legs for non duplicated flights In the merged flight table will be inserted all the flight legs from all non duplicated flights. '------------------------------------------------------------------------------
INSERT INTO MergedFlight ( TrafficId, CallSign, EventTime, Latitude, Longitude, FlightLevel, GroundSpeed, Source, AvgSpeed, DeltaLevel )
SELECT AmocFlight.TrafficId, AmocFlight.CallSign, AmocFlight.EventTime, AmocFlight.Latitude, AmocFlight.Longitude, AmocFlight.FlightLevel, AmocFlight.GroundSpeed, AmocFlight.Source, AmocFlight.AvgSpeed, AmocFlight.DeltaLevel
FROM (MergedTraffic LEFT JOIN AmocETMSDuplicateFlights ON MergedTraffic.Fluid = AmocETMSDuplicateFlights.Fluid) INNER JOIN AmocFlight ON MergedTraffic.TrafficID = AmocFlight.TrafficId
WHERE (((MergedTraffic.Sources)='AMOC') AND ((AmocETMSDuplicateFlights.Fluid) Is Null))
ORDER BY AmocFlight.UniqueID;
'------------------------------------------------------------------------------
中国航空网 www.aero.cn
航空翻译 www.aviation.cn
本文链接地址：AERO2K Flight Movement Inventory Project Report(72)
