7
I D C C F C
I D C C F I
I D C I F C
I D C I F I
I D C C D C
I D C C D I
I D C I D C
I D C I D I
8
I D I C F C
I D I C F I
I D I I F C
I D I I F I
I D I C D C
I D I C D I
I D I I D C
I D I I D I
3.2.2.3. Merge ETMS flight table
3.2.2.3.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 ETMSFlight.TrafficId, ETMSFlight.CallSign, ETMSFlight.EventTime, ETMSFlight.Latitude, ETMSFlight.Longitude, ETMSFlight.FlightLevel, ETMSFlight.GroundSpeed, ETMSFlight.Source, ETMSFlight.AvgSpeed, ETMSFlight.DeltaLevel
FROM (MergedTraffic LEFT JOIN AmocETMSDuplicateFlights ON MergedTraffic.Fluid = AmocETMSDuplicateFlights.Fluid) INNER JOIN ETMSFlight ON MergedTraffic.TrafficID = ETMSFlight.TrafficId
WHERE (((MergedTraffic.Sources)='ETMS') AND ((AmocETMSDuplicateFlights.Fluid) Is Null))
ORDER BY ETMSFlight.UniqueID;
'------------------------------------------------------------------------------
3.2.2.3.2. Merge flight legs for duplicated flights In the merged flight table will be inserted only those flight legs that fulfill trajectory assessment merging conditions. Table 69 shows with yellow color what flight legs will be merged in the MegedTraffic table. '------------------------------------------------------------------------------
INSERT INTO MergedFlight ( TrafficId, CallSign, EventTime, Latitude, Longitude, FlightLevel, GroundSpeed, Source, AvgSpeed, DeltaLevel )
SELECT ETMSAssessmentFlightLegs.TrafficID, ETMSAssessmentFlightLegs.CallSign, ETMSAssessmentFlightLegs.EventTime, ETMSAssessmentFlightLegs.Latitude, ETMSAssessmentFlightLegs.Longitude, ETMSAssessmentFlightLegs.FlightLevel, ETMSAssessmentFlightLegs.GroundSpeed, ETMSAssessmentFlightLegs.Source, ETMSAssessmentFlightLegs.AvgSpeed, ETMSAssessmentFlightLegs.DeltaLevel
FROM AmocETMSAssessmentCombinations INNER JOIN ETMSAssessmentFlightLegs ON AmocETMSAssessmentCombinations.Fluid = ETMSAssessmentFlightLegs.Fluid
WHERE (((ETMSAssessmentFlightLegs.Assessment)="1") AND ((Left([AmocETMSAssessmentCombinations].[Action],1))=1)) OR (((ETMSAssessmentFlightLegs.Assessment)="2") AND ((Mid([AmocETMSAssessmentCombinations].[Action],2,1))=1)) OR (((ETMSAssessmentFlightLegs.Assessment)="3") AND ((Mid([AmocETMSAssessmentCombinations].[Action],3,1))=1));
'------------------------------------------------------------------------------
3.3. Copy flights to the final (Flights.mdb) database
At this point in the Merge database are saved all flights that AMOC and ETMS sources provide. The only thing that left to do is to copy merged flights into the final database. The problem is that in the traffic table, for duplicated flights, both AMOC and ETMS flight heads are included. From these two records, only one will be selected and copied to the database. This record collects information from both records. The differences between the two flights are the departure time, arrival time and source name and eventually the aircraft type. For the resulted record will be selected the earliest departure time, the latest arrival time, "AMOC/ETMS" for source name and AMOC source's aircraft type. 中国航空网 www.aero.cn 航空翻译 www.aviation.cn 本文链接地址：AERO2K Flight Movement Inventory Project Report(74)
