-
Notifications
You must be signed in to change notification settings - Fork 0
Scenarios
Tomer Sagi edited this page Nov 10, 2016
·
1 revision
This page details the process required to generate scenarios and scenario-specific data.
[Validation] (#validation)
[Validation] (#validation-1)
[Validation] (#validation-2)
[Validation] (#validation-3)
[Filling the impossible flights table] (#filling-the-impossible-flights-table)
[Filling the PlaneMaintenance table] (#filling-the-planemaintenance-table)
[Fixing Invalid Rotations] (#fixing-invalid-rotations)
[Validation] (#validation-4)
[Add disruption sets for all scenarios] (#add-disruption-sets-for-all-scenarios)
[Generate initial flight disruptions for all scenarios] (#generate-initial-flight-disruptions-for-all-scenarios)
[Validation] (#validation-5)
[Generate disruptions for all scenarios] (#generate-disruptions-for-all-scenarios)
[Validation] (#validation-6)
[Validation] (#validation-7)
[Validation] (#validation-8)
[Validation] (#validation-9)
DELETE FROM scenarioDB_USA.Scenarios;
INSERT INTO scenarioDB_USA.Scenarios
SELECT 1, 'roadefusa', 'Base data- not a valid scenario', TIMESTAMPADD('hh', 14, MIN(plannedDepTime)), MAX (plannedArrTime), 20000, 5000, 1000, 1, 1, 1, MIN(plannedDepTime), MAX(plannedArrTime)
FROM airlines.FlightView;
INSERT INTO scenarioDB_USA.Scenarios VALUES (2, 'roadefusa', '2- Full 3 days', '2014-01-21T14:00GMT', '2014-01- 23T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-01-21T00:00GMT', '2014-01-23T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (3, 'roadefusa', '3- Partial 3 days', '2014-01-21T14:00GMT', '2014-01-23T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-01-21T00:00GMT', '2014-01-23T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (4, 'roadefusa', '4- Full 4 days', '2014-01-24T14:00GMT', '2014-01-26T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-01-24T00:00GMT', '2014-01-26T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (5, 'roadefusa', '5- Full 3 days', '2014-02-03T14:00GMT', '2014-02-05T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-02-03T00:00GMT', '2014-02-05T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (6, 'roadefusa', '6- Full 4 days', '2014-04-23T14:00GMT', '2014-04-25T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-04-23T00:00GMT', '2014-04-25T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (7, 'roadefusa', '7- Full 3 days', '2014-06-17T14:00GMT', '2014-06-19T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-06-17T00:00GMT', '2014-06-19T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (8, 'roadefusa', '8- Full 3 days', '2014-08-18T14:00GMT', '2014-08-20T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-08-18T00:00GMT', '2014-08-20T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (9, 'roadefusa', '9- Full 3 days', '2014-09-10T14:00GMT', '2014-09-12T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-09-10T00:00GMT', '2014-09-12T23:59GMT');
INSERT INTO scenarioDB_USA.Scenarios VALUES (10, 'roadefusa', '10- Full 3 days', '2014-10-28T14:00GMT', '2014-10-30T23:59GMT', 20000, 5000, 1000, 1, 1, 1, '2014-10-28T00:00GMT', '2014-10-30T23:59GMT');
Additional scenarios can be created similarly. /*All the scenarios except for scenario 3*/
INSERT INTO scenarioDB_USA.Flights
SELECT flightID,flightNo,plannedDepTime,plannedArrTime,distanceMiles,departsFrom,arrivesAt,planeID,S.id,0
FROM airlines.FlightView, scenarioDB_USA.Scenarios S
WHERE S.id!=3 AND S.id>1 AND (plannedDepTime BETWEEN S.scenStart AND S.scenEnd) AND plannedArrTime <= S.scenEnd;
/*validation that there is scenarios except scenario 1 */
SELECT * FROM scenarioDB_USA.Flights F WHERE F.Scenario != 1
/*shouldn't be empty*/
/*validation that there is no scenario 3*/
SELECT * FROM scenarioDB_USA.Flights F WHERE F.Scenario = 3
/*should be empty*/
/*Scenario 3 uses planes of scenario 2*/
DELETE FROM scenarioDB_USA.Plane WHERE scenario=2;
/*validation there is no scenario 2*/
SELECT * FROM scenarioDB_USA.Plane F WHERE F.Scenario = 2
/*should be empty*/
/*adding a Carrier column*/
ALTER TABLE scenarioDB_USA.Plane ADD Carrier VarChar(2);
INSERT INTO scenarioDB_USA.Plane
SELECT P.planeID, P.planeModel, PM.family,PM.rangeMins,PM.opCost,PM.turnaround,PM.transit,S.id,'',P.Config, B.Carrier
FROM ((airlines.Plane P INNER JOIN airlines.PlaneModel PM ON P.planeModel=PM.planeModel) INNER JOIN (SELECT DISTINCT SF.TailNum, SF.Carrier FROM airlines.SampleFlights SF WHERE SF.TailNum != '') AS B ON P.planeID = B.TailNum AND P.planeModel=PM.planeModel) CROSS JOIN scenarioDB_USA.Scenarios S
WHERE P.planeID IN (SELECT F.TailNum FROM scenarioDB_USA.Flights F WHERE F.Scenario=S.id) AND S.id=2
DELETE FROM scenarioDB_USA.Flights WHERE scenario=3;
INSERT INTO scenarioDB_USA.Flights
SELECT F.flightID, F.flightNo, f.plannedDepTime, f.plannedArrTime,
f.distanceMiles, f.departsFrom, f.ArrivesAt, f.TailNum, 3,0
FROM scenarioDB_USA.Flights F
INNER JOIN scenarioDB_USA.Plane P ON F.Scenario=P.scenario AND F.TailNum=P.planeID
WHERE F.Scenario=2 AND P.planeModel='Embraer EMB-120ER Brasilia';
Additional scenarios can be created similarly. SELECT scenario,count(*), count(distinct flightID) FROM scenarioDB_USA.Flights GROUP BY Scenario;
/*Should show a nonzero row for each scenario, the two columns should be identical*/
Validation TODO: make sure the airports and countries in flight view are not null
SELECT * FROM scenarioDB_USA.FlightView FV WHERE FV.departsFrom IS NULL OR FV.ArrivesAt IS NULL
/*should be empty*/ DELETE FROM scenarioDB_USA.AirportSample WHERE Scenario!=1;
INSERT INTO scenarioDB_USA.AirportSample
(SELECT DISTINCT F.departsFrom,F.Scenario
FROM scenarioDB_USA.Flights F
WHERE scenario!=1
UNION
SELECT DISTINCT F.ArrivesAt,F.Scenario
FROM scenarioDB_USA.Flights F
WHERE scenario!=1) SELECT scenario,count(*) FROM scenarioDB_USA.AirportSample GROUP BY Scenario;
/*Should show a nonzero row for each scenario*/ DELETE FROM scenarioDB_USA.Plane WHERE scenario>2;
INSERT INTO scenarioDB_USA.Plane
SELECT P.planeID, P.planeModel, PM.family,PM.rangeMins,PM.opCost,PM.turnaround,PM.transit,S.id,'',P.Config, B.Carrier
FROM ((airlines.Plane P INNER JOIN airlines.PlaneModel PM ON P.planeModel=PM.planeModel) INNER JOIN (SELECT DISTINCT SF.TailNum, SF.Carrier FROM airlines.SampleFlights SF WHERE SF.TailNum != '') AS B ON P.planeID = B.TailNum AND P.planeModel=PM.planeModel) CROSS JOIN scenarioDB_USA.Scenarios S
WHERE P.planeID IN (SELECT F.TailNum FROM scenarioDB_USA.Flights F WHERE F.Scenario=S.id) AND S.id>2; SELECT scenario,count(*) FROM scenarioDB_USA.Plane GROUP BY Scenario;
/*Should show a nonzero row for each scenario*/ DELETE FROM scenarioDB_USA.AircraftEndPositions WHERE scenario!=1;
INSERT INTO scenarioDB_USA.AircraftEndPositions
SELECT F2.Scenario, F2.ArrivesAt, P.planeModel, P.Config
FROM ((scenarioDB_USA.Flights F2 INNER JOIN
(SELECT Scenario, TailNum, max(plannedArrTime) maxArr
FROM scenarioDB_USA.Flights
GROUP BY Scenario,TailNum) F ON F2.TailNum=F.TailNum AND F2.Scenario=F.Scenario AND maxArr=F2.plannedArrTime )
INNER JOIN scenarioDB_USA.Scenarios S ON S.id=F2.Scenario)
INNER JOIN scenarioDB_USA.Plane P ON F2.TailNum=P.planeID AND S.id=P.Scenario WHERE S.id!=1;
/*validation there are no rows for scenario 1*/
SELECT * FROM scenarioDB_USA.AircraftEndPositions WHERE scenario=1;
/*should be empty*/
DELETE FROM airlines.ADiRStartLocations;
INSERT INTO airlines.ADiRStartLocations
SELECT F.TailNum, F.departsFrom, S.id
FROM (scenarioDB_USA.Flights F
INNER JOIN (SELECT scenario,TailNum,Min(plannedDepTime) as minDep
FROM scenarioDB_USA.Flights
GROUP BY scenario,TailNum) MF
ON F.Scenario=MF.Scenario AND F.TailNum=MF.TailNum)
INNER JOIN scenarioDB_USA.Scenarios S ON F.Scenario=S.id
WHERE MF.minDep=F.plannedDepTime;
UPDATE scenarioDB_USA.Plane SET locationAtStart = Location
FROM airlines.ADiRStartLocations WHERE planeID=TailNum AND sid=scenario; SELECT * FROM airlines.Plane P
LEFT OUTER JOIN airlines.PlaneModel PM ON P.planeModel=PM.planeModel
WHERE PM.planeModel IS NULL;
/*Should be empty*/
SELECT * FROM scenarioDB_USA.Flights P
LEFT OUTER JOIN airlines.ADiRStartLocations ADR ON P.TailNum=ADR.TailNum
WHERE ADR.TailNum IS NULL AND P.TailNum IS NOT NULL;
/*Should be empty*/
SELECT P.scenario, AEP.count as AEPCount, P.count as Pcount FROM
(SELECT scenario, count(*) FROM scenarioDB_USA.AircraftEndPV AEP GROUP BY scenario) AS AEP
INNER JOIN
(SELECT scenario,count(*) FROM scenarioDB_USA.Plane GROUP BY Scenario) AS P
ON AEP.Scenario=P.Scenario;
/*The 2 columns should be identical*/ DELETE FROM scenarioDB_USA.ImpossibleFlights;
INSERT INTO scenarioDB_USA.ImpossibleFlights SELECT * FROM scenarioDB_USA.Flights; DELETE FROM scenarioDB_USA.PlaneMaintenance WHERE scenario!=1;
INSERT INTO scenarioDB_USA.PlaneMaintenance
SELECT TailNUM, PM.StartTS, PM.Type, PM.EndTS, S.id, PM.Location, DATEDIFF('n',S.scenStart, PM.StartTS)
FROM (scenarioDB_USA.Scenarios S INNER JOIN scenarioDB_USA.Plane P ON S.id=P.scenario)
INNER JOIN scenarioDB_USA.PlaneMaintenance PM ON PM.TailNum=P.planeID
WHERE S.id!=1 AND PM.Scenario=1 AND PM.StartTS BETWEEN S.scenStart AND s.scenEnd;
/*Backup for flight fixing algorithm*/
DELETE FROM scenarioDB_USA.PlaneMaintenanceBackup;
INSERT INTO scenarioDB_USA.PlaneMaintenanceBackup SELECT * FROM scenarioDB_USA.PlaneMaintenance; /*validation that the two tables are equal*/
(SELECT scenario, COUNT(*) FROM scenarioDB_USA.PlaneMaintenance WHERE Scenario>1 GROUP BY scenario ORDER BY scenario) EXCEPT
(SELECT scenario, COUNT(*) FROM scenarioDB_USA.PlaneMaintenanceBackup WHERE Scenario>1 GROUP BY scenario ORDER BY scenario)
/*should be empty*/
/*creating backup for flights table*/
CREATE TABLE scenarioDB_USA.FlightsBackup AS
SELECT *
FROM scenarioDB_USA.FlightsThe Flights table currently contains invalid rotations. In order to fix them run the following:
•Make sure there is a backup for both Flights and PlaneMaintenance tables
•Run PlanSimulator.java (located in pdata_sim project), given the paramters: scenarioIDs(delimited by ','), numberOfTrials (e.g. "2,3" 10 will fix the rotations of scenarios 2 and 3 by trying 10 different solutions for each scenario and choosing the best one)
•In order to rerun it run the following sql script first:
◾delete from scenarioDB_USA.Flights where Scenario!=1
◾insert into scenarioDB_USA.Flights select * from scenarioDB_USA.ImpossibleFlights where Scenario!=1
◾delete from scenarioDB_USA.PlaneMaintenance where Scenario!=1
◾insert into scenarioDB_USA.PlaneMaintenance select * from scenarioDB_USA.PlaneMaintenanceBackup where Scenario!=1
•Repeat Update aircraft start and end positions
Rerun the PlanSimulator and make sure the log contains the following message: "found 0 invalid sub-rotations with 0
flights, 0 of them are maintenance"
SELECT count(*) FROM scenarioDB_USA.Flights WHERE TailNum IS NULL AND Scenario!=1;
/* should be 0 */ DELETE FROM scenarioDB_USA.AirportCapacity;
INSERT INTO scenarioDB_USA.AirportCapacity (AirportID, IntStart, IntEnd, DepCap, ArrCap, Scenario)
SELECT AC.AirportCode, h, h+1, COALESCE(DepCount, 0), COALESCE(ArrCount, 0), AC.ScenarioID
FROM airlines.Airport0Capacity AC LEFT OUTER JOIN airlines.scenarioAirportCapacity_V ACV
ON ACV.AirportID=AC.AirportCode AND AC.h = ACV.FlightHour AND ACV.ScenarioID = AC.ScenarioID
ORDER BY AC.ScenarioID, AC.AirportCode, h; DELETE FROM scenarioDB_USA.DisruptionSet;
INSERT INTO scenarioDB_USA.DisruptionSet
SELECT DISTINCT id, 0, 1
FROM scenarioDB_USA.Scenarios
WHERE id!=1; DELETE FROM scenarioDB_USA.FlightDisruptions WHERE scenario!=1;
INSERT INTO scenarioDB_USA.FlightDisruptions
SELECT F.Scenario, FD.flightID, FD.Delay, FD.DisruptionSet
FROM scenarioDB_USA.FlightDisruptions FD INNER JOIN scenarioDB_USA.Flights F ON FD.flightID = F.flightID
INNER JOIN scenarioDB_USA.Scenarios S ON S.id=F.Scenario
INNER JOIN airlines.FlightView SF ON SF.flightID=F.flightID
WHERE F.Scenario!=1 AND FD.Scenario=1 AND FD.DisruptionSet=0 AND (SF.Cancelled=1 OR F.plannedDepTime<S.startTS OR
((SF.CarrierDelay<=SF.WeatherDelay OR SF.CarrierDelay<=SF.NASDelay OR SF.CarrierDelay<=SF.SecurityDelay)
AND (SF.LateAircraftDelay<=SF.WeatherDelay OR SF.LateAircraftDelay<=SF.NASDelay OR SF.LateAircraftDelay<=SF.SecurityDelay))); SELECT DISTINCT * FROM (SELECT COUNT(*) FROM scenarioDB_USA.FlightDisruptions GROUP BY Scenario, flightID) AS temp;
--should return 1For each scenario (except for 1) run the java main file DisruptionsSimulator.java (located in pdata-sim project in the disruptionsimulator module) with the parameters: scenario IDs (delimited by ','), names of the simulators to run: "FlightDisruptions,AircraftDisruptions,AirportReduction" (e.g. "2,3" "FlightDisruptions,AircraftDisruptions,AirportReduction")
The AirportCapacity and the AirportDisruptions tables currently contain invalid airport capacities. In order to fix them run the following:
For each scenario (except for 1) run the java main file AirportCapacitySimulator.java (located in pdata-sim project in the airportcapacitiysimulator module) with the parameters: scenario IDs (delimited by ',') (e.g. "2,3")
Rerun the simulator and make sure no updates are reported in the log
DELETE FROM scenarioDB_USA.Itineraries WHERE Scenario!=1;
INSERT INTO scenarioDB_USA.Itineraries
SELECT DISTINCT F.Scenario, ITI.ItinID, ITI.Type, ITI.PricePerPassenger, ITI.Passengers
FROM (scenarioDB_USA.Itineraries ITI
INNER JOIN scenarioDB_USA.ItineraryFlights ITF ON ITI.ItinID=ITF.ItinID)
INNER JOIN scenarioDB_USA.Flights F ON ITF.FlightID=F.flightID WHERE F.Scenario!=1
DELETE FROM scenarioDB_USA.ItineraryFlights WHERE Scenario!=1;
INSERT INTO scenarioDB_USA.ItineraryFlights
SELECT F.Scenario, ITF.ItinID, ITF.FlightID, ITF.Class
FROM scenarioDB_USA.ItineraryFlights ITF INNER JOIN scenarioDB_USA.Flights F ON ITF.FlightID=F.flightID
WHERE F.Scenario!=1 SELECT DISTINCT Scenario FROM scenarioDB_USA.Itineraries;
--expected a list of all the scenarios
SELECT DISTINCT Scenario FROM scenarioDB_USA.ItineraryFlights;
--expected a list of all the scenarios Generate common & scenario data
DELETE FROM scenarioDB_USA.Route WHERE Scenario!=1;
INSERT INTO scenarioDB_USA.Route
SELECT DISTINCT R.Origin, R.Destination, R.RouteType, R.flightMinutes, S.id
FROM scenarioDB_USA.Route AS R CROSS JOIN scenarioDB_USA.Scenarios S
WHERE R.Origin IN (SELECT AirportCode FROM scenarioDB_USA.AirportSample A WHERE A.Scenario=S.id)
AND R.Destination IN (SELECT AirportCode FROM scenarioDB_USA.AirportSample A WHERE A.Scenario=S.id)
AND S.id!=1; SELECT DISTINCT Scenario FROM scenarioDB_USA.Route;
--expected a list of all the scenarios
Generate scenario data
DELETE FROM scenarioDB_USA.CancelDelayPenalties WHERE Scenario!=1;
INSERT INTO scenarioDB_USA.CancelDelayPenalties
SELECT S.id, P.PenaltyType, P.Cabin, P.ItineraryType, P.Cost
FROM scenarioDB_USA.CancelDelayPenalties P, scenarioDB_USA.Scenarios S
WHERE p.Scenario=1 AND s.id!=1
DELETE FROM scenarioDB_USA.DowngradePenalties WHERE Scenario!=1;
INSERT INTO scenarioDB_USA.DowngradePenalties
SELECT S.id, P.FromCabin, P.ToCabin, P.ItineraryType, P.Cost
FROM scenarioDB_USA.DowngradePenalties P, scenarioDB_USA.Scenarios S
WHERE p.Scenario=1 AND s.id!=1 SELECT Scenario, count(*) FROM scenarioDB_USA.CancelDelayPenalties group by Scenario;
--expected each of the scenarios to get count=27
SELECT Scenario, count(*) FROM scenarioDB_USA.DowngradePenalties group by Scenario;
--expected each of the scenarios to get count=18