3.1.3.3. Delete inconsistent flights
After being saved, inconsistent flights must be deleted:
'--------------------------------------------------------------------------------
DELETE * FROM MergedTraffic WHERE ((([MergedTraffic].[AvgSpeed])=0));
'--------------------------------------------------------------------------------
3.1.4. Find duplicate flights
At this moment, in MergedTraffic there are two kinds of duplicated flights. First, flights with identical fluids and secondly flights with different fluids. This step's purpose is to identify duplicated flights with different fluids and change one fluid to other's value, in order to have only duplicated flights with identical fluids. A method is used, which's purpose is to identify flights with:
o Sources1 = 'AMOC'
o Sources2 = 'ETMS'
o CallSign1 = CallSign2
o DepartureAirport1 = DepartureAirport2
o Fluid1 <> Fluid2
o DepartureTime1 – DepartureTime2 < 800 minutes
o (AverageSpeed(FirstFlightLeg1, FirstFlightLeg2) = 0) OR
.
(AverageSpeed(LastFlightLeg1, FirstFlightLeg2) = 0) OR
.
(AverageSpeed(LastFlightLeg1, FirstFlightLeg2) >= 110)
'--------------------------------------------------------------------------------
SELECT AmocFirstLastPoint.TrafficId AS AmocTrafficId, ETMSFirstLastPoint.TrafficId AS ETMSTrafficId, AmocFirstLastPoint.Fluid AS AmocFluid, ETMSFirstLastPoint.Fluid AS ETMSFluid, AmocFirstLastPoint.DepartureTime AS AmocDT, ETMSFirstLastPoint.DepartureTime AS ETMSDT FROM AmocFirstLastPoint INNER JOIN ETMSFirstLastPoint ON (AmocFirstLastPoint.CallSign = ETMSFirstLastPoint.CallSign) AND (AmocFirstLastPoint.DepartureAirport = ETMSFirstLastPoint.DepartureAirport) WHERE (([AmocFirstLastPoint].[Fluid])<>[ETMSFirstLastPoint].[Fluid]) AND ((Abs(DateDiff('n',[AmocFirstLastPoint].[FirstEventTime],[ETMSFirstLastPoint].[FirstEvent Time])))<800) AND (((Abs(CalculateAvgSpeed([AmocFirstLastPoint].[FirstEventTime],[ETMSFirstLastPoint].[F irstEventTime],[AmocFirstLastPoint].[FirstLat],[AmocFirstLastPoint].[FirstLon],[ETMSFirstL astPoint].[FirstLat],[ETMSFirstLastPoint].[FirstLon])))=0) OR ((Abs(CalculateAvgSpeed([AmocFirstLastPoint].[LastEventTime],[ETMSFirstLastPoint].[Fi rstEventTime],[AmocFirstLastPoint].[LastLat],[AmocFirstLastPoint].[LastLon],[ETMSFirstL astPoint].[FirstLat],[ETMSFirstLastPoint].[FirstLon])))=0) OR ((Abs(CalculateAvgSpeed([AmocFirstLastPoint].[LastEventTime],[ETMSFirstLastPoint].[Fi rstEventTime],[AmocFirstLastPoint].[LastLat],[AmocFirstLastPoint].[LastLon],[ETMSFirstL astPoint].[FirstLat],[ETMSFirstLastPoint].[FirstLon])))>=110));
'--------------------------------------------------------------------------------
With this method all pairs of duplicated flights are discovered. For each pair, the flight with first departure time will keep its fluid unchanged, but for the other one the fluid will be updated to first one's value.
3.2. Merging procedure
After previous step, it is certain that all duplicated flights in MergedTraffic table have
identical fluids. Having this information, now the merging procedure can start.
First step in the execution of this procedure is to assess trajectories for each flight.
3.2.1. Trajectory assessment
For each flight in MergedTrafic, an assessment sequence (three values) will be attributed:
.
Complete or Incomplete for Departure
.
Fine or Dubious for Cruise (En route)
. Complete or Incomplete for Arrival These values will be saved into TrajectoryAssessment table (see Table 67). This table should initially be emptied.
3.2.1.1. Clear TrajectoryAssessment table
'--------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "TrajectoryAssessment"
中国航空网 www.aero.cn
航空翻译 www.aviation.cn
本文链接地址：AERO2K Flight Movement Inventory Project Report(70)
