-
Notifications
You must be signed in to change notification settings - Fork 0
BaseData
Tomer Sagi edited this page Nov 10, 2016
·
1 revision
This page details the process required to generate the tables of Adir dataset and basic data, that is later used to create scenarios from.
Prepare maintenance requirements
CREATE TABLE airlines.Airport (
AirportCode Varchar(3),
CITY_MARKET_ID Integer,
AirportName Varchar(31),
Longitude Float,
Latitude Float,
UTCoffset Float
);
CREATE TABLE scenarioDB_USA.Airport (
AirportCode Varchar(3) NOT NULL,
CITY_MARKET_ID Integer,
AirportName Varchar(31),
Longitude Float,
Latitude Float,
UTCoffset Float,
CONSTRAINT pk_Airport PRIMARY KEY (AirportCode)
);
CREATE TABLE airlines.HoursSequence (
h Integer NOT NULL,
CONSTRAINT HoursSequence_PK PRIMARY KEY (h)
);
CREATE TABLE scenarioDB_USA.AirportSample (
AirportCode Varchar(3) NOT NULL,
Scenario Integer NOT NULL DEFAULT 0
);
CREATE TABLE scenarioDB_USA.AirportCapacity (
AirportID Varchar(3) NOT NULL,
IntStart Integer NOT NULL,
IntEnd Integer,
DepCap Integer,
ArrCap Integer,
Scenario Integer NOT NULL
);
CREATE TABLE airlines.City (
CityCode int,
CityName varchar(25),
CityState varchar(2),
CityCountry varchar(2)
);
CREATE TABLE airlines.Plane (
planeID Varchar(7),
planeModel Varchar(30),
Config Varchar(20),
Operator Varchar(20),
CONSTRAINT Plane_PK PRIMARY KEY (planeID)
);
CREATE TABLE airlines.PlaneModel (
planeModel Varchar(100) NOT NULL,
rangeMins Integer,
rangeKM Integer,
opCost Integer,
turnaround Integer,
transit Integer,
family Varchar(100),
CONSTRAINT NewTable_PK PRIMARY KEY (planeModel)
);
CREATE TABLE scenarioDB_USA.Plane (
planeID Varchar(20) NOT NULL,
planeModel Varchar(100),
family Varchar(100),
rangeMinutes Integer,
opCost Float,
turnAroundtime Integer,
transitTime Integer,
scenario Integer NOT NULL DEFAULT 0,
locationAtStart Varchar(3),
Config Varchar(15),
CONSTRAINT Plane_PK PRIMARY KEY (planeID,scenario)
);
CREATE TABLE scenarioDB_USA.PlaneModel (
planeModel Varchar(100) NOT NULL,
rangeMins Integer,
rangeKM Integer,
opCost Integer,
turnaround Integer,
transit Integer,
family Varchar(100),
CONSTRAINT PlaneModel_PK PRIMARY KEY (planeModel)
);
CREATE TABLE airlines.SampleFlights (
flightID Integer,
carrier Varchar(2),
flightNo Integer,
plannedDepTime Timestamptz,
plannedArrTime Timestamptz,
distanceMiles Integer,
departsFrom Varchar(3),
ArrivesAt Varchar(3),
TailNum Varchar(6),
ArrDelayMinutes Float,
Cancelled Float,
ActualElapsedTime Float,
DayOfWeek Integer,
CancellationCode Varchar(1),
Diverted Float,
CarrierDelay Integer,
WeatherDelay Integer,
NASDelay Integer,
SecurityDelay Integer,
LateAircraftDelay Integer,
DepTime Timestamptz,
ArrTime Timestamptz,
checkCol Varchar(2)
);
/*Used for schedule fixing algorithm*/
CREATE TABLE scenarioDB_USA.ImpossibleFlights (
flightID Integer NOT NULL,
flightNo Integer,
plannedDepTime Timestamptz,
plannedArrTime Timestamptz,
distanceMiles Integer,
departsFrom Varchar(3),
ArrivesAt Varchar(3),
TailNum Varchar(20),
Scenario Integer NOT NULL DEFAULT 1,
Previous Integer DEFAULT 0,
CONSTRAINT Flights_PK PRIMARY KEY (flightID,Scenario)
);
CREATE TABLE scenarioDB_USA.Scenarios (
id Integer NOT NULL,
dataset Varchar(50),
name Varchar(100),
startTS Timestamptz,
endTS Timestamptz,
P1 Float,
P2 Float,
P3 Float,
alpha Float,
beta Float,
gamma Float,
scenStart Timestamptz,
scenEnd Timestamptz,
CONSTRAINT Scenarios_PK PRIMARY KEY (id)
);
/*used to generate start locations*/
CREATE TABLE airlines.ADiRStartLocations (
TailNum Varchar(15) NOT NULL,
Location Varchar(3) NOT NULL,
sid Integer NOT NULL,
CONSTRAINT ADiRStartLocations_PK PRIMARY KEY (TailNum,sid,Location)
);
CREATE TABLE scenarioDB_USA.PlaneMaintenance (
TailNum Varchar(100) NOT NULL,
StartTS Timestamptz NOT NULL,
Type Char(1) NOT NULL,
EndTS Timestamptz,
Scenario Integer NOT NULL DEFAULT 0,
Location Varchar(3),
Remaining Integer,
CONSTRAINT PlaneMaintenance_PK PRIMARY KEY (TailNum,StartTS,Scenario)
);
CREATE TABLE scenarioDB_USA.PlaneMaintenanceBackup (
TailNum Varchar(100) NOT NULL,
StartTS Timestamptz NOT NULL,
Type Char(1) NOT NULL,
EndTS Timestamptz,
Scenario Integer NOT NULL DEFAULT 0,
Location Varchar(3),
Remaining Integer,
CONSTRAINT PlaneMaintenanceBU_PK PRIMARY KEY (TailNum,StartTS,Scenario)
);
CREATE TABLE scenarioDB_USA.AircraftDisruptions (
Scenario Integer NOT NULL,
TailNumber Varchar(20) NOT NULL,
Start Timestamptz NOT NULL,
"End" Timestamptz,
DisruptionSet Integer NOT NULL DEFAULT 0
);
CREATE TABLE scenarioDB_USA.AirportDisruptions (
Scenario Integer NOT NULL,
AirportCode Varchar(3) NOT NULL,
Start Timestamptz NOT NULL,
"End" Timestamptz,
DepRate Integer,
ArrRate Integer,
DisruptionSet Integer NOT NULL DEFAULT 0
);
CREATE TABLE scenarioDB_USA.DisruptionSet (
scenario Integer NOT NULL,
setID Integer NOT NULL DEFAULT 0,
isTest Char(100)
);
CREATE TABLE scenarioDB_USA.FlightDisruptions (
Scenario Integer NOT NULL,
flightID Integer NOT NULL,
Delay Integer,
DisruptionSet Integer NOT NULL DEFAULT 0
);
CREATE TABLE scenarioDB_USA.TrainingSet (
id Integer NOT NULL,
Description Varchar(255),
Scenario Integer
);
CREATE TABLE scenarioDB_USA.TrainingSetDisruptionSets (
tSet Integer NOT NULL,
scenario Integer NOT NULL,
disruptionSet Integer NOT NULL
); CREATE TABLE scenarioDB_USA.Itineraries (
Scenario Integer NOT NULL,
ItinID Integer NOT NULL,
Type Char(100),
PricePerPassenger Float,
Passengers Integer
);
CREATE TABLE scenarioDB_USA.ItineraryFlights (
Scenario Integer NOT NULL,
ItinID Integer NOT NULL,
FlightID Integer NOT NULL,
Class Char(100)
); CREATE TABLE scenarioDB_USA.Route (
Origin Varchar(3) NOT NULL,
Destination Varchar(3) NOT NULL,
RouteType Char(1),
flightMinutes Integer,
Scenario Integer
);###Penalties
CREATE TABLE scenarioDB_USA.CancelDelayPenalties (
Scenario Integer NOT NULL,
PenaltyType Varchar(12) NOT NULL,
Cabin Varchar(1) NOT NULL,
ItineraryType Varchar(1) NOT NULL,
Cost Float
);
CREATE TABLE scenarioDB_USA.DowngradePenalties (
Scenario Integer NOT NULL,
FromCabin Varchar(1) NOT NULL,
ToCabin Varchar(1) NOT NULL,
ItineraryType Varchar(1) NOT NULL,
Cost Float
);###TzData
CREATE SCHEMA tzdata;
CREATE TABLE tzdata.country (
country_code CHAR(2) NULL,
country_name VARCHAR(45) NULL,
CONSTRAINT country_PK PRIMARY KEY (country_code)
);
CREATE TABLE tzdata.timezone (
zone_id INT NOT NULL,
abbreviation VARCHAR(6) NOT NULL,
time_start INT NOT NULL,
gmt_offset INT NOT NULL,
dst CHAR(1) NOT NULL,
CONSTRAINT timezone_PK PRIMARY KEY (zone_id,time_start)
);
CREATE TABLE tzdata.zone (
zone_id INT NOT NULL,
country_code CHAR(2) NOT NULL,
zone_name VARCHAR(35) NOT NULL,
PRIMARY KEY (zone_id));
CREATE TABLE airlines.tempTZ (
flightID INTEGER,
plannedDepTime TIMESTAMPTZ
);Insert data to airlines schema using talend script called ''Prepare Supporting Data''.
###tempTz
DELETE FROM airlines.tempTZ;
INSERT INTO airlines.tempTZ
WITH tzInterpret AS (SELECT flightID, plannedDepTime, departsFrom, AC.tz, tz.time_start, tz.abbreviation
FROM ((airlines.SampleFlights SF INNER JOIN airlines.airportCoordinates AC ON SF.departsFrom=AC.iata) INNER JOIN tzdata.zone z ON AC.tz=z.zone_name) INNER JOIN tzdata.timezone tz on z.zone_id=tz.zone_id
WHERE EXTRACT(EPOCH FROM plannedDepTime)-time_start >0 ORDER BY flightID,EXTRACT(EPOCH FROM plannedDepTime)-time_start)
SELECT tzi.flightID, TO_TIMESTAMP_TZ(CONCAT(TO_CHAR(tzi.plannedDepTime AT TIMEZONE 'GMT'),tzi.abbreviation),'YYYY-MM- DD HH:MI:SSTZ') as newplannedDep
FROM tzInterpret as tzi WHERE tzi.time_start =(SELECT MAX(tzi1.time_start) FROM tzInterpret tzi1 WHERE tzi1.flightID=tzi.flightID); SELECT count(*) FROM airlines.tempTZ
/*should be 1164155*/
WITH tzInterpret AS (SELECT flightID, plannedDepTime, departsFrom, AC.tz, tz.time_start, tz.abbreviation
FROM ((airlines.SampleFlights SF INNER JOIN airlines.airportCoordinates AC ON SF.departsFrom=AC.iata) INNER JOIN tzdata.zone z ON AC.tz=z.zone_name) INNER JOIN tzdata.timezone tz on z.zone_id=tz.zone_id
WHERE EXTRACT(EPOCH FROM plannedDepTime)-time_start >0 ORDER BY flightID,EXTRACT(EPOCH FROM plannedDepTime)- time_start)
SELECT count(*) FROM (SELECT flightID,MAX(tzi1.time_start) FROM tzInterpret tzi1 GROUP BY flightID) tztemp;
/*should be 458582*/Insert plane and plane model data to airlines schema using talend script called ''Prepare Supporting Data''.
SELECT * FROM airlines.Plane P
LEFT OUTER JOIN airlines.PlaneModel PM ON P.planeModel=PM.planeModel
WHERE PM.planeModel IS NULL;
/*Should be empty*/ CREATE VIEW airlines.FlightView AS
SELECT F.flightID, F.flightNo, TT.plannedDepTime, "timestampadd"('n'::varchar(1), F.plannedElapsed, TT.plannedDepTime) AS plannedArrTime, "timestampadd"('n'::varchar(1), (F.DepDelayMinutes)::int, TT.plannedDepTime) AS DepTime, "timestampadd" ('n'::varchar(1), ((F.ArrDelayMinutes)::int + F.plannedElapsed), TT.plannedDepTime) AS ArrTime, F.distanceMiles, F.departsFrom, DAC.CityCountry AS departCountry, F.ArrivesAt, AAC.CityCountry AS arriveCountry, F.DepDelayMinutes, F.ArrDelayMinutes, F.Cancelled, F.ActualElapsedTime AS actualElapsedTime, P.planeID, P.planeModel, F.DayOfWeek, F.Diverted, F.CancellationCode, F.CarrierDelay, F.WeatherDelay, F.NASDelay, F.SecurityDelay, F.LateAircraftDelay
FROM ((((((airlines.SampleFlights F LEFT JOIN airlines.Plane P ON ((F.TailNum = P.planeID))) LEFT JOIN airlines.Airport AA ON ((F.ArrivesAt = AA.AirportCode))) LEFT JOIN airlines.Airport DA ON ((F.departsFrom = DA.AirportCode))) LEFT JOIN airlines.City AAC ON ((AA.CITY_MARKET_ID = AAC.CityCode))) LEFT JOIN airlines.City DAC ON ((DA.CITY_MARKET_ID = DAC.CityCode))) LEFT JOIN airlines.tempTZ TT ON ((TT.flightID = F.flightID)));
UPDATE airlines.SampleFlights SET plannedDepTime=FV.plannedDepTime FROM airlines.FlightView FV WHERE FV.flightID = airlines.SampleFlights.flightID;Insert flight data to airlines schema using talend script called ''Prepare Main Data'' and then ''Load all Data''.
UPDATE airlines.SampleFlights SET TailNum='N13979' WHERE TailNum='N27523'; SELECT count(*) FROM airlines.SampleFlights;
/*1164155*/
SELECT count(*) FROM airlines.FlightView;
/*1164155*/
SELECT MIN(plannedDepTime) FROM airlines.FlightView;
/*01/01/2014 02:23 PM*/
SELECT MAX(plannedDepTime) FROM airlines.FlightView;
/*01/01/15 01:59 PM*/
__Validation : make sure the airports and countries in flight view are not null __Prepare views based on scenario 1 data
CREATE VIEW airlines.FlightsforAirportCapacity AS
SELECT flightID, plannedDepTime, plannedArrTime,
DATE(plannedDepTime::timestamptz AT TIME ZONE 'GMT') AT TIME ZONE 'GMT' AS plannedFlightDate, DATE (plannedArrTime::timestamptz AT TIME ZONE 'GMT') AT TIME ZONE 'GMT' AS plannedArrDate,
DAYOFWEEK(plannedDepTime::timestamptz AT TIME ZONE 'GMT') AS plannedFlightDay, DAYOFWEEK(plannedArrTime::timestamptz AT TIME ZONE 'GMT') AS plannedArrDay,
HOUR(plannedDepTime::timestamptz AT TIME ZONE 'GMT') AS plannedDepHour, HOUR(plannedArrTime::timestamptz AT TIME ZONE 'GMT') AS plannedArrHour,
actualDepTime AS DepTime, actualArrTime AS ArrTime,
DATE(actualDepTime::timestamptz AT TIME ZONE 'GMT') AT TIME ZONE 'GMT' AS FlightDate, DATE(actualArrTime::timestamptz AT TIME ZONE 'GMT') AT TIME ZONE 'GMT' AS ArrDate,
DAYOFWEEK(actualDepTime::timestamptz AT TIME ZONE 'GMT') AS FlightDay, DAYOFWEEK(actualArrTime::timestamptz AT TIME ZONE 'GMT') AS ArrDay,
HOUR(actualDepTime::timestamptz AT TIME ZONE 'GMT') AS DepHour, HOUR(actualArrTime::timestamptz AT TIME ZONE 'GMT') AS ArrHour,
departsFrom AS Origin, ArrivesAt AS Dest,
Cancelled, CancellationCode, DepDelayMinutes, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay, PlaneID AS TailNum
FROM (SELECT *,
TIMESTAMPADD(n, CAST ((CASE WHEN ArrDelayMinutes<=15 THEN 0 ELSE ArrDelayMinutes END) AS INTEGER), plannedDepTime) AS actualDepTime,
(CASE WHEN ArrDelayMinutes<=15 THEN plannedArrTime ELSE ArrTime END) AS actualArrTime FROM airlines.FlightView) AS tmp;
CREATE VIEW airlines.AirportActualCapacity AS
SELECT
COALESCE(Origin, Dest) AS AirportID,
COALESCE(FlightDate, ArrDate) AS actualFlightDate,
COALESCE(FlightDay, ArrDay) AS actualFlightDay,
COALESCE(DepHour, ArrHour) AS actualFlightHour,
COALESCE(DepCount, 0) AS actualDepCount,
COALESCE(ArrCount, 0) AS actualArrCount
FROM
(SELECT Origin, FlightDate, FlightDay, DepHour, COUNT(*) AS DepCount
FROM airlines.FlightsforAirportCapacity WHERE Cancelled=0
GROUP BY Origin, FlightDate, FlightDay, DepHour) AS AirportCapacity_Dep
FULL OUTER JOIN
(SELECT Dest, ArrDate, ArrDay, ArrHour, COUNT(*) AS ArrCount
FROM airlines.FlightsforAirportCapacity WHERE Cancelled=0
GROUP BY Dest, ArrDate, ArrDay, ArrHour) AS AirportCapacity_Arr
ON
Origin = Dest AND FlightDate = ArrDate AND DepHour = ArrHour
ORDER BY AirportID, actualFlightDate, actualFlightHour;
CREATE VIEW airlines.AirportPlannedCapacity AS
SELECT
COALESCE(Origin, Dest) AS AirportID,
COALESCE(plannedFlightDate, plannedArrDate) AS plannedFlightDate,
COALESCE(plannedFlightDay, plannedArrDay) AS plannedFlightDay,
COALESCE(plannedDepHour, plannedArrHour) AS plannedFlightHour,
COALESCE(plannedDepCount, 0) AS plannedDepCount,
COALESCE(plannedArrCount, 0) AS plannedArrCount
FROM
(SELECT Origin, plannedFlightDate, plannedFlightDay, plannedDepHour, COUNT(*) AS plannedDepCount
FROM airlines.FlightsforAirportCapacity
GROUP BY Origin, plannedFlightDate, plannedFlightDay, plannedDepHour) AS AirportCapacity_Dep
FULL OUTER JOIN
(SELECT Dest, plannedArrDate, plannedArrDay, plannedArrHour, COUNT(*) AS plannedArrCount
FROM airlines.FlightsforAirportCapacity
GROUP BY Dest, plannedArrDate, plannedArrDay, plannedArrHour) AS AirportCapacity_Arr
ON
Origin = Dest AND plannedFlightDate= plannedArrDate AND plannedDepHour = plannedArrHour
ORDER BY AirportID, plannedFlightDate, plannedFlightHour;
CREATE VIEW airlines.AirportCapacity AS
SELECT COALESCE(APC.AirportID, AAC.AirportID) AS AirportID, COALESCE(plannedFlightDate, actualFlightDate) AS FlightDate,
COALESCE(plannedFlightDay, actualFlightDay) AS FlightDay, COALESCE(plannedFlightHour, actualFlightHour) AS FlightHour,
COALESCE(plannedDepCount, 0) AS plannedDepCount, COALESCE(plannedArrCount, 0) AS plannedArrCount,
COALESCE(actualDepCount, 0) AS actualDepCount, COALESCE(actualArrCount, 0) AS actualArrCount,
(CASE WHEN plannedDepCount > actualDepCount OR actualDepCount IS NULL THEN plannedDepCount ELSE actualDepCount end) AS DepCount,
(CASE WHEN plannedArrCount > actualArrCount or actualArrCount IS NULL THEN plannedArrCount ELSE actualArrCount end) AS ArrCount,
(CASE WHEN actualDepCount IS NULL THEN plannedDepCount WHEN plannedDepCount IS NULL THEN -actualDepCount ELSE plannedDepCount - actualDepCount end) AS DepPlannedActualDiff,
(CASE WHEN actualArrCount IS NULL THEN plannedArrCount WHEN plannedArrCount IS NULL THEN -actualArrCount ELSE plannedArrCount - actualArrCount end) AS ArrPlannedActualDiff
FROM airlines.AirportPlannedCapacity APC FULL OUTER JOIN airlines.AirportActualCapacity AAC
ON APC.AirportID = AAC.AirportID AND APC.plannedFlightDate = AAC.actualFlightDate AND APC.plannedFlightHour = AAC.actualFlightHour;
DELETE FROM scenarioDB_USA.Flights;
INSERT INTO scenarioDB_USA.Flights
SELECT flightID,flightNo,plannedDepTime,plannedArrTime,distanceMiles,departsFrom,arrivesAt,planeID,1,0
FROM airlines.FlightView ;
INSERT INTO scenarioDB_USA.ImpossibleFlights SELECT * FROM scenarioDB_USA.Flights; CREATE VIEW scenarioDB_USA.AirportView AS
SELECT * FROM scenarioDB_USA.Airport AR NATURAL INNER JOIN scenarioDB_USA.AirportSample ASA; DELETE FROM scenarioDB_USA.AirportSample;
INSERT INTO scenarioDB_USA.AirportSample
(SELECT DISTINCT F.departsFrom,F.Scenario
FROM scenarioDB_USA.Flights F
UNION
SELECT DISTINCT F.ArrivesAt,F.Scenario
FROM scenarioDB_USA.Flights F)
INSERT INTO scenarioDB_USA.Airport
SELECT DISTINCT A.*
FROM airlines.Airport A INNER JOIN scenarioDB_USA.AirportSample S ON A.AirportCode = S.AirportCode WHERE S.Scenario = 1 CREATE VIEW scenarioDB_USA.AircraftEndPV AS
SELECT AEP.*, PF.family as fam
FROM scenarioDB_USA.AircraftEndPositions AEP
INNER JOIN (SELECT DISTINCT planeModel,family,scenario
FROM scenarioDB_USA.Plane) PF ON AEP.Scenario=PF.scenario AND AEP.PlaneModel=PF.planeModel;
Insert planes into the planes table (at this stage only data for scenario 1 is populated).
DELETE FROM scenarioDB_USA.Plane;
INSERT INTO scenarioDB_USA.Plane
SELECT P.planeID, P.planeModel, PM.family,PM.rangeMins,PM.opCost,PM.turnaround,PM.transit,1,'',P.Config
FROM (airlines.Plane P INNER JOIN airlines.PlaneModel PM ON P.planeModel=PM.planeModel)
WHERE P.planeID IN (SELECT F.TailNum FROM scenarioDB_USA.Flights F WHERE F.Scenario=1); 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*/ CREATE VIEW scenarioDB_USA.FlightView AS
SELECT flightID,flightNo,plannedDepTime, plannedArrTime,distanceMiles
,departsFrom,ArrivesAt,F.Scenario,planeID, planeModel, family,
rangeMinutes, Config, turnAroundtime, transitTime, opCost, Previous
FROM ((scenarioDB_USA.Flights F
INNER JOIN scenarioDB_USA.AirportSample ASD ON F.departsFrom = ASD.AirportCode AND F.Scenario = ASD.Scenario)
INNER JOIN scenarioDB_USA.AirportSample ASA ON F.ArrivesAt = ASA.AirportCode AND F.Scenario = ASA.Scenario)
LEFT OUTER JOIN scenarioDB_USA.Plane P ON F.TailNum = P.planeID AND F.Scenario=P.scenario ;
CREATE VIEW scenarioDB_USA.ImpossibleFlightsView AS
SELECT flightID,flightNo,plannedDepTime, plannedArrTime,distanceMiles,departsFrom,ArrivesAt,
F.Scenario,planeID, planeModel, family, rangeMinutes, Config, turnAroundtime, transitTime,
opCost, Previous
FROM ((scenarioDB_USA.ImpossibleFlights F
INNER JOIN scenarioDB_USA.AirportSample ASD ON F.departsFrom = ASD.AirportCode AND F.Scenario = ASD.Scenario)
INNER JOIN scenarioDB_USA.AirportSample ASA ON F.ArrivesAt = ASA.AirportCode AND F.Scenario = ASA.Scenario)
LEFT OUTER JOIN scenarioDB_USA.Plane P ON F.TailNum = P.planeID AND F.Scenario=P.scenario ;__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 */ CREATE VIEW scenarioDB_USA.DataForMaintenanceGen AS
SELECT FV.flightID, FV.planeID, FV.departsFrom, FV.ArrivesAt,
FV.plannedDepTime::timestamptz AT TIME ZONE 'GMT' AS plannedDepTime, FV.plannedArrTime::timestamptz AT TIME ZONE 'GMT' AS plannedArrTime,
((FV.plannedArrTime - FV.plannedDepTime)) as plannedDuration, FV.transitTime,
SF.DepTime::timestamptz AT TIME ZONE 'GMT' AS actualDepTime, SF.ArrTime::timestamptz AT TIME ZONE 'GMT' AS actualArrTime, SF.Cancelled
FROM scenarioDB_USA.FlightView FV
LEFT OUTER JOIN airlines.FlightView SF
ON FV.flightID = SF.flightID
WHERE ((FV.planeID!='NULL') and (Scenario='1')) order by FV.planeID, plannedDepTime;* Run java: “MaintenanceFileGen” to insert maintenance data to scenarioDB_USA.PlaneMaintenance.
/*Backup for flight fixing algorithm*/
INSERT INTO scenarioDB_USA.PlaneMaintenanceBackup SELECT * FROM scenarioDB_USA.PlaneMaintenance; /*Prepare views based on rest of the scenarios to be*/
CREATE VIEW scenarioAirportCapacityByDay_V AS
SELECT S.id AS ScenarioID, AirportID, FlightDay, FlightHour, MAX(DepCount) AS DepCount, MAX(ArrCount) AS ArrCount
FROM airlines.AirportCapacity,scenarioDB_USA.Scenarios S
WHERE FlightDate BETWEEN DATE(S.startTS) AND DATE(S.endTS)
GROUP BY S.id, AirportID, FlightDay, FlightHour
ORDER BY S.id, AirportID, FlightDay, FlightHour;
CREATE VIEW scenarioAirportCapacity_V AS
SELECT S.id AS ScenarioID, AirportID, FlightHour, MAX(DepCount) AS DepCount, MAX(ArrCount) AS ArrCount
FROM airlines.AirportCapacity,scenarioDB_USA.Scenarios S
WHERE FlightDate BETWEEN DATE(S.startTS) AND DATE(S.endTS)
GROUP BY S.id, AirportID, FlightHour
ORDER BY S.id, AirportID, FlightHour;
CREATE VIEW Airport0Capacity AS
SELECT SC.id AS ScenarioID, AirportCode, h, 0 AS DepCap, 0 AS ArrCap
FROM scenarioDB_USA.AirportSample ASA INNER JOIN scenarioDB_USA.Scenarios SC ON ASA.Scenario=SC.id CROSS JOIN HoursSequence;
Insert data into the ~AirportCapacity table (at this stage only data for scenario 1 is populated)
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;Insert flight disruptions to scenario 1
INSERT INTO scenarioDB_USA.FlightDisruptions
SELECT 1, flightID, (CASE WHEN Cancelled=1 THEN -1 ELSE ArrDelayMinutes END) AS Delay, 0
FROM airlines.FlightView
WHERE(Cancelled=1 and CancellationCode!='A') OR ArrDelayMinutes>15;###### Prepare views for airport disruptions
CREATE VIEW scenarioDB_USA.AirportCapacity_DepDisruptions AS
(SELECT Origin, plannedFlightDate, plannedDepHour, COUNT(*) AS DepDisCount
FROM airlines.FlightsforAirportCapacity
WHERE Cancelled=1 OR (ArrDelayMinutes>15 AND (plannedFlightDate != FlightDate OR plannedDepHour != DepHour))
GROUP BY Origin, plannedFlightDate, plannedDepHour);
CREATE VIEW scenarioDB_USA.AirportCapacity_AddedDeps AS
(SELECT Origin, FlightDate, DepHour, COUNT(*) AS addedDepCount
FROM airlines.FlightsforAirportCapacity
WHERE Cancelled=0 AND ArrDelayMinutes>15 AND (plannedFlightDate != FlightDate OR plannedDepHour != DepHour)
GROUP BY Origin, FlightDate, DepHour);
CREATE VIEW scenarioDB_USA.AirportCapacity_ArrDisruptions AS
(SELECT Dest, plannedArrDate, plannedArrHour, COUNT(*) AS ArrDisCount
FROM airlines.FlightsforAirportCapacity
WHERE Cancelled=1 OR (ArrDelayMinutes>15 AND (plannedArrDate != ArrDate OR plannedArrHour != ArrHour))
GROUP BY Dest, plannedArrDate, plannedArrHour);
CREATE VIEW scenarioDB_USA.AirportCapacity_AddedArrs AS
(SELECT Dest, ArrDate, ArrHour, COUNT(*) AS addedArrCount
FROM airlines.FlightsforAirportCapacity
WHERE Cancelled=0 AND ArrDelayMinutes>15 AND (plannedArrDate != ArrDate OR plannedArrHour != ArrHour)
GROUP BY Dest, ArrDate, ArrHour);
CREATE VIEW scenarioDB_USA.AirportCapacity_DepGapByDisruptions AS
SELECT COALESCE(D.Origin, A.Origin) AS Origin,
COALESCE(D.plannedFlightDate, A.FlightDate) AS FlightDate, COALESCE(D.plannedDepHour, A.DepHour) AS DepHour,
COALESCE(D.DepDisCount, 0) - COALESCE(A.AddedDepCount, 0) AS DepGap
FROM scenarioDB_USA.AirportCapacity_DepDisruptions D FULL OUTER JOIN scenarioDB_USA.AirportCapacity_AddedDeps A
ON D.Origin = A.Origin AND D.plannedFlightDate = A.FlightDate AND D.plannedDepHour = A.DepHour;
CREATE VIEW scenarioDB_USA.AirportCapacity_ArrGapByDisruptions AS
SELECT COALESCE(D.Dest, A.Dest) AS Dest,
COALESCE(D.plannedArrDate, A.ArrDate) AS ArrDate, COALESCE(D.plannedArrHour, A.ArrHour) AS ArrHour,
COALESCE(D.ArrDisCount, 0) - COALESCE(A.AddedArrCount, 0) AS ArrGap
FROM scenarioDB_USA.AirportCapacity_ArrDisruptions D FULL OUTER JOIN scenarioDB_USA.AirportCapacity_AddedArrs A
ON D.Dest = A.Dest AND D.plannedArrDate = A.ArrDate AND D.plannedArrHour = A.ArrHour;
CREATE VIEW scenarioDB_USA.AirportCapacity_ScenarioDepDisruptions AS
(SELECT FD.Scenario, Origin, plannedFlightDate, plannedDepHour, COUNT(*) AS DepDisCount
FROM airlines.FlightsforAirportCapacity F
LEFT OUTER JOIN (SELECT * FROM scenarioDB_USA.FlightDisruptions WHERE DisruptionSet=0) AS FD ON F.flightID=FD.flightID
WHERE (F.Cancelled=1 OR (F.ArrDelayMinutes>15 AND (F.plannedFlightDate != F.FlightDate OR F.plannedDepHour != F.DepHour)))
AND FD.flightID IS NOT NULL
GROUP BY F.Origin, F.plannedFlightDate, F.plannedDepHour, FD.Scenario);
CREATE VIEW scenarioDB_USA.AirportCapacity_ScenarioAddedDeps AS
(SELECT FD.Scenario, Origin, FlightDate, DepHour, COUNT(*) AS AddedDepCount
FROM airlines.FlightsforAirportCapacity F
LEFT OUTER JOIN (SELECT * FROM scenarioDB_USA.FlightDisruptions WHERE DisruptionSet=0) AS FD ON F.flightID=FD.flightID
WHERE (F.Cancelled=0 AND F.ArrDelayMinutes>15 AND (F.plannedFlightDate != F.FlightDate OR F.plannedDepHour != F.DepHour))
AND FD.flightID IS NOT NULL
GROUP BY F.Origin, F.FlightDate, F.DepHour, FD.Scenario);
CREATE VIEW scenarioDB_USA.AirportCapacity_ScenarioArrDisruptions AS
(SELECT FD.Scenario, Dest, plannedArrDate, plannedArrHour, COUNT(*) AS ArrDisCount
FROM airlines.FlightsforAirportCapacity F
LEFT OUTER JOIN (SELECT * FROM scenarioDB_USA.FlightDisruptions WHERE DisruptionSet=0) AS FD ON F.flightID=FD.flightID
WHERE (F.Cancelled=1 OR (F.ArrDelayMinutes>15 AND (F.plannedArrDate != F.ArrDate OR F.plannedArrHour != F.ArrHour)))
AND FD.flightID IS NOT NULL
GROUP BY F.Dest, F.plannedArrDate, F.plannedArrHour, FD.Scenario);
CREATE VIEW scenarioDB_USA.AirportCapacity_ScenarioAddedArrs AS
(SELECT FD.Scenario, Dest, ArrDate, ArrHour, COUNT(*) AS AddedArrCount
FROM airlines.FlightsforAirportCapacity F
LEFT OUTER JOIN (SELECT * FROM scenarioDB_USA.FlightDisruptions WHERE DisruptionSet=0) AS FD ON F.flightID=FD.flightID
WHERE (F.Cancelled=0 AND F.ArrDelayMinutes>15 AND (F.plannedArrDate != F.ArrDate OR F.plannedArrHour != F.ArrHour))
AND FD.flightID IS NOT NULL
GROUP BY F.Dest, F.ArrDate, F.ArrHour, FD.Scenario);
CREATE VIEW scenarioDB_USA.AirportCapacity_ScenarioDepGapByDisruptions AS
SELECT COALESCE(D.Scenario, A.Scenario) AS Scenario,
COALESCE(D.Origin, A.Origin) AS Origin, COALESCE(D.plannedFlightDate, A.FlightDate) AS FlightDate, COALESCE (D.plannedDepHour, A.DepHour) AS DepHour,
COALESCE(D.DepDisCount, 0) - COALESCE(A.AddedDepCount, 0) AS DepGap
FROM scenarioDB_USA.AirportCapacity_ScenarioDepDisruptions D FULL OUTER JOIN scenarioDB_USA.AirportCapacity_ScenarioAddedDeps A
ON D.Scenario = A.Scenario AND D.Origin = A.Origin AND D.plannedFlightDate = A.FlightDate AND D.plannedDepHour = A.DepHour;
CREATE VIEW scenarioDB_USA.AirportCapacity_ScenarioArrGapByDisruptions AS
SELECT COALESCE(D.Scenario, A.Scenario) AS Scenario,
COALESCE(D.Dest, A.Dest) AS Dest, COALESCE(D.plannedArrDate, A.ArrDate) AS ArrDate, COALESCE(D.plannedArrHour, A.ArrHour) AS ArrHour,
COALESCE(D.ArrDisCount, 0) - COALESCE(A.AddedArrCount, 0) AS ArrGap
FROM scenarioDB_USA.AirportCapacity_ScenarioArrDisruptions D FULL OUTER JOIN scenarioDB_USA.AirportCapacity_ScenarioAddedArrs A
ON D.Scenario = A.Scenario AND D.Dest = A.Dest AND D.plannedArrDate = A.ArrDate AND D.plannedArrHour = A.ArrHour;
CREATE VIEW scenarioDB_USA.AirportCapacity_DepDiff AS
SELECT SDG.Scenario, DG.Origin, DG.FlightDate, DG.DepHour, DG.DepGap AS OrigDepGap, SDG.DepGap as NewDepGap, SDG.DepGap AS DepGapDiff
FROM scenarioDB_USA.AirportCapacity_DepGapByDisruptions DG RIGHT OUTER JOIN scenarioDB_USA.AirportCapacity_ScenarioDepGapByDisruptions SDG
ON DG.Origin = SDG.Origin AND DG.FlightDate = SDG.FlightDate AND DG.DepHour = SDG.DepHour;
CREATE VIEW scenarioDB_USA.AirportCapacity_ArrDiff AS
SELECT SDG.Scenario, DG.Dest, DG.ArrDate, DG.ArrHour, DG.ArrGap AS OrigArrGap, SDG.ArrGap AS NewArrGap,SDG.ArrGap AS ArrGapDiff
FROM scenarioDB_USA.AirportCapacity_ArrGapByDisruptions DG RIGHT OUTER JOIN scenarioDB_USA.AirportCapacity_ScenarioArrGapByDisruptions SDG
ON DG.Dest = SDG.Dest AND DG.ArrDate = SDG.ArrDate AND DG.ArrHour = SDG.ArrHour;
CREATE VIEW scenarioDB_USA.AirportCapacity_Diff AS
SELECT AAC.AirportID, AAC.FlightDate, AAC.FlightHour AS IntStart, AAC.FlightHour + 1 AS IntEnd,
actualDepCount, plannedDepCount, AAC.DepPlannedActualDiff AS OrigDepDiff, COALESCE(DepGapDiff, 0) AS DepDiff,
(CASE WHEN plannedDepCount != 0 THEN (COALESCE(DepPlannedActualDiff, 0))/plannedDepCount ELSE 0 END) AS OrigDepDiff_prct,
actualArrCount, plannedArrCount, AAC.ArrPlannedActualDiff AS OrigArrDiff, COALESCE(ArrGapDiff, 0) AS ArrDiff,
(CASE WHEN plannedArrCount != 0 THEN (COALESCE(ArrPlannedActualDiff, 0))/plannedArrCount ELSE 0 end) AS OrigArrDiff_prct,
COALESCE(AD.Scenario, AAC.Scenario) AS Scenario
FROM (SELECT AAC.*, DD.Scenario, DD.DepGapDiff
FROM airlines.AirportCapacity AAC
LEFT OUTER JOIN scenarioDB_USA.AirportCapacity_DepDiff DD
ON AAC.AirportID=DD.Origin AND AAC.FlightDate=DD.FlightDate AND AAC.FlightHour=DD.DepHour) AS AAC
LEFT OUTER JOIN scenarioDB_USA.AirportCapacity_ArrDiff AD
ON AAC.AirportID=AD.Dest AND AAC.FlightDate=AD.ArrDate AND AAC.FlightHour=AD.ArrHour
AND (AAC.Scenario IS NULL OR AD.ScenarioIS NULL OR AAC.Scenario=AD.Scenario)
WHERE DepGapDiff IS NOT NULL OR ArrGapDiff IS NOT NULL;
SELECT COUNT(*) FROM scenarioDB_USA.AirportCapacity_DepGapByDisruptions AS DisGap
INNER JOIN (SELECT * FROM scenarioDB_USA.AirportCapacity_Diff WHERE Scenario=1) AS Diff
ON Diff.AirportID = DisGap.Origin AND Diff.FlightDate = DisGap.FlightDate AND Diff.IntStart = DisGap.DepHour
WHERE DisGap.DepGap != Diff.OrigDepDiff;
--validate that 0 is returned
SELECT COUNT(*) FROM scenarioDB_USA.AirportCapacity_ArrGapByDisruptions AS ArrGap
INNER JOIN (SELECT * FROM scenarioDB_USA.AirportCapacity_Diff WHERE Scenario=1) AS Diff
ON Diff.AirportID = ArrGap.Dest AND Diff.FlightDate = ArrGap.ArrDate AND Diff.IntStart = ArrGap.ArrHour
WHERE ArrGap.ArrGap != Diff.OrigArrDiff;
--validate that 0 is returned CREATE TABLE PAXDELAY.AIRCRAFT_CODE_MAPPINGS (
IATA_CODE CHAR(3),
ICAO_CODE VARCHAR2(4),
MANUFACTURER_AND_MODEL VARCHAR2(75),
INVENTORY_MANUFACTURER VARCHAR2(25),
INVENTORY_MODEL VARCHAR2(25),
WAKE_CATEGORY CHAR(1)
);
CREATE TABLE PAXDELAY.AIRLINE_INVENTORIES (
CARRIER VARCHAR2(3),
FIRST_YEAR NUMBER(4,0),
SERIAL_NUMBER VARCHAR2(12),
TAIL_NUMBER VARCHAR2(7),
AIRCRAFT_STATUS CHAR(1),
OPERATING_STATUS CHAR(1),
NUMBER_OF_SEATS NUMBER(3,0),
MANUFACTURER VARCHAR2(50),
MODEL VARCHAR2(25),
CAPACITY_IN_POUNDS NUMBER(6,0),
ACQUISITION_DATE VARCHAR2(10)
);
CREATE TABLE PAXDELAY.AIRLINE_ITINERARIES (
ITINERARY_ID INTEGER DEFAULT NEXTVAL('PAXDELAY.AIRLINE_ITINERARIES_ID_SEQ'),
YEAR NUMBER,
QUARTER NUMBER,
MONTH NUMBER,
DAY_OF_MONTH NUMBER,
PASSENGERS NUMBER,
NUM_FLIGHTS NUMBER,
FIRST_CARRIER VARCHAR2(3),
FIRST_FLIGHT_NUMBER VARCHAR2(6),
FIRST_DEPARTURE_TIME TIMESTAMP (9) WITH TIME ZONE,
FIRST_ARRIVAL_TIME TIMESTAMP (6) WITH TIME ZONE,
FIRST_FLIGHT_ID NUMBER,
SECOND_CARRIER VARCHAR2(3),
SECOND_FLIGHT_NUMBER VARCHAR2(6),
SECOND_DEPARTURE_TIME TIMESTAMP (9) WITH TIME ZONE,
SECOND_ARRIVAL_TIME TIMESTAMP (6) WITH TIME ZONE,
SECOND_FLIGHT_ID NUMBER,
ORIGIN CHAR(3),
CONNECTION VARCHAR2(3),
DESTINATION CHAR(3),
DAY_OF_WEEK NUMBER(1,0),
TRIP_DURATION NUMBER(4,0),
LAYOVER_DURATION NUMBER(4,0),
MULTI_DAY_FLAG NUMBER(1,0) DEFAULT 0
);
CREATE TABLE PAXDELAY.AIRPORTS (
CODE CHAR(3),
CITY VARCHAR2(50),
STATE CHAR(2),
TIMEZONE_REGION VARCHAR2(50)
);
CREATE TABLE PAXDELAY.AOTP (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
DAY_OF_MONTH NUMBER(2,0),
DAY_OF_WEEK NUMBER(1,0),
FLIGHT_DATE CHAR(10),
UNIQUE_CARRIER CHAR(2),
AIRLINE_ID NUMBER(10,0),
CARRIER CHAR(2),
TAIL_NUMBER VARCHAR2(10),
FLIGHT_NUMBER VARCHAR2(6),
ORIGIN VARCHAR2(5),
ORIGIN_CITY_NAME VARCHAR2(50),
ORIGIN_STATE CHAR(2),
ORIGIN_STATE_FIPS VARCHAR2(4),
ORIGIN_STATE_NAME VARCHAR2(25),
ORIGIN_WAC NUMBER(4,0),
DESTINATION VARCHAR2(5),
DESTINATION_CITY_NAME VARCHAR2(50),
DESTINATION_STATE CHAR(2),
DESTINATION_STATE_FIPS NUMBER(2,0),
DESTINATION_STATE_NAME VARCHAR2(25),
DESTINATION_WAC NUMBER(4,0),
PLANNED_DEPARTURE_TIME CHAR(4),
ACTUAL_DEPARTURE_TIME CHAR(4),
DEPARTURE_OFFSET NUMBER(4,0),
EPARTURE_DELAY NUMBER(4,0),
DEPARTURE_DELAY_15 NUMBER(2,0),
DEPARTURE_DELAY_GROUP NUMBER(2,0),
DEPARTURE_TIME_BLOCK CHAR(9),
TAXI_OUT_DURATION NUMBER(4,0),
WHEELS_OFF_TIME CHAR(4),
WHEELS_ON_TIME CHAR(4),
TAXI_IN_DURATION NUMBER(4,0),
PLANNED_ARRIVAL_TIME CHAR(4),
ACTUAL_ARRIVAL_TIME CHAR(4),
ARRIVAL_OFFSET NUMBER(4,0),
ARRIVAL_DELAY NUMBER(4,0),
ARRIVAL_DELAY_15 NUMBER(2,0),
ARRIVAL_DELAY_GROUP NUMBER(2,0),
ARRIVAL_TIME_BLOCK CHAR(9),
CANCELLED NUMBER(1,0),
CANCELLATION_CODE CHAR(1),
DIVERTED NUMBER(1,0),
PLANNED_ELAPSED_TIME NUMBER(4,0),
ACTUAL_ELAPSED_TIME NUMBER(4,0),
IN_AIR_DURATION NUMBER(4,0),
NUMBER_FLIGHTS NUMBER(1,0),
FLIGHT_DISTANCE NUMBER(5,0),
DISTANCE_GROUP NUMBER(2,0),
CARRIER_DELAY NUMBER(4,0) DEFAULT 0.00,
WEATHER_DELAY NUMBER(4,0) DEFAULT 0.00,
NAS_DELAY NUMBER(4,0) DEFAULT 0.00,
SECURITY_DELAY NUMBER(4,0) DEFAULT 0.00,
LATE_AIRCRAFT_DELAY NUMBER(4,0) DEFAULT 0.00
);
CREATE TABLE PAXDELAY.ASQP_CARRIERS (
CODE CHAR(2)
);
CREATE TABLE PAXDELAY.CARRIER_IATA_SEATS (
CARRIER VARCHAR2(3),
AIRCRAFT_CODE CHAR(3),
SEATS NUMBER(3,0)
);
CREATE TABLE PAXDELAY.DATA (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
DAY_OF_MONTH NUMBER(2,0),
PLANNED_NUM_FLIGHTS NUMBER(1,0),
PLANNED_FIRST_FLIGHT_ID NUMBER(12,0),
PLANNED_FIRST_CARRIER CHAR(2),
PLANNED_ORIGIN CHAR(3),
PLANNED_DESTINATION CHAR(3),
PLANNED_DEPARTURE_TIME TIMESTAMP (6) WITH TIME ZONE,
TRIP_DELAY NUMBER(8,4),
DAY NUMBER(3,0),
CATEGORY VARCHAR2(8),
HOUR NUMBER,
MINUTE NUMBER,
SECOND NUMBER
);
CREATE TABLE PAXDELAY.DB1B_MARKETS (
ITINERARY_ID NUMBER(16,0),
MARKET_ID NUMBER(16,0),
NUMBER_COUPONS NUMBER(6,0),
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
ORIGIN CHAR(3),
NUMBER_ORIGIN_AIRPORTS NUMBER(2,0),
ORIGIN_CITY_CODE NUMBER(6,0),
ORIGIN_COUNTRY_CODE VARCHAR2(3),
ORIGIN_STATE_FIPS NUMBER(2,0),
ORIGIN_STATE CHAR(2),
ORIGIN_STATE_NAME VARCHAR2(50),
ORIGIN_WAC NUMBER(4,0),
DESTINATION CHAR(3),
NUMBER_DESTINATION_AIRPORTS NUMBER(2,0),
DESTINATION_CITY_CODE NUMBER(6,0),
DESTINATION_COUNTRY_CODE VARCHAR2(3),
DESTINATION_STATE_FIPS NUMBER(2,0),
DESTINATION_STATE CHAR(2),
DESTINATION_STATE_NAME VARCHAR2(50),
DESTINATION_WAC NUMBER(4,0),
AIRPORT_GROUP VARCHAR2(51),
AIRPORT_WAC_GROUP VARCHAR2(38),
TICKETING_CARRIER_CHANGE NUMBER(1,0),
TICKETING_CARRIER_GROUP VARCHAR2(35),
OPERATING_CARRIER_CHANGE NUMBER(1,0),
OPERATING_CARRIER_GROUP VARCHAR2(35),
REPORTING_CARRIER CHAR(2),
TICKETING_CARRIER CHAR(2),
OPERATING_CARRIER CHAR(2),
BULK_FARE_FLAG NUMBER(1,0),
PASSENGERS NUMBER(4,0),
FARE NUMBER(7,2),
DISTANCE NUMBER(5,0),
DISTANCE_GROUP NUMBER(2,0),
MILES_FLOWN NUMBER(5,0),
NONSTOP_MILES NUMBER(5,0),
ITINERARY_GEOGRAPHY_TYPE NUMBER(1,0),
MARKET_GEOGRAPHY_TYPE NUMBER(1,0)
);
CREATE TABLE PAXDELAY.DB1B_ROUTE_DEMANDS (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
NUM_FLIGHTS NUMBER,
ORIGIN CHAR(3),
CONNECTION VARCHAR2(3),
DESTINATION CHAR(3),
FIRST_OPERATING_CARRIER VARCHAR2(3),
SECOND_OPERATING_CARRIER VARCHAR2(3),
PASSENGERS NUMBER
);
CREATE TABLE PAXDELAY.DB1B_T100_SEGMENT_FACTORS (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
CARRIER VARCHAR2(3),
ORIGIN CHAR(3),
DESTINATION CHAR(3),
DB1B_PASSENGERS NUMBER(6,0),
T100_PASSENGERS NUMBER(6,0),
SCALING_FACTOR NUMBER(8,4)
);
CREATE TABLE PAXDELAY.DB1B_UNIQUE_CARRIER_SEGMENTS (
ID Integer DEFAULT NEXTVAL('PAXDELAY.DB1B_UNIQUE_SEGMENTS_ID_SEQ'),
QUARTER NUMBER(1,0),
TICKETING_CARRIER VARCHAR2(3),
OPERATING_CARRIER VARCHAR2(3),
ORIGIN CHAR(3),
DESTINATION CHAR(3)
);
CREATE TABLE PAXDELAY.FLIGHTS (
ID NUMBER,
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
DAY_OF_MONTH NUMBER(2,0),
DAY_OF_WEEK NUMBER(1,0),
HOUR_OF_DAY NUMBER(2,0),
MINUTES_OF_HOUR NUMBER(2,0),
CARRIER CHAR(2),
TAIL_NUMBER VARCHAR2(10),
FLIGHT_NUMBER CHAR(4),
ORIGIN CHAR(3),
DESTINATION CHAR(3),
PLANNED_DEPARTURE_TIME TIMESTAMP (6) WITH TIME ZONE,
PLANNED_ARRIVAL_TIME TIMESTAMP (6) WITH TIME ZONE,
ACTUAL_DEPARTURE_TIME TIMESTAMP (6) WITH TIME ZONE,
ACTUAL_ARRIVAL_TIME TIMESTAMP (6) WITH TIME ZONE,
WHEELS_OFF_TIME TIMESTAMP (6) WITH TIME ZONE,
WHEELS_ON_TIME TIMESTAMP (6) WITH TIME ZONE,
CANCELLED_FLAG NUMBER(1,0),
DIVERTED_FLAG NUMBER(1,0),
NUM_FLIGHTS NUMBER(1,0),
FLIGHT_DISTANCE NUMBER(5,0),
CARRIER_DELAY NUMBER(4,0),
WEATHER_DELAY NUMBER(4,0),
NAS_DELAY NUMBER(4,0),
SECURITY_DELAY NUMBER(4,0),
DATE_AIRCRAFT_DELAY NUMBER(4,0),
ICAO_AIRCRAFT_CODE VARCHAR2(4),
SEATING_CAPACITY NUMBER(3,0)
);
CREATE TABLE PAXDELAY.ITINERARIES (
ID NUMBER(12,0),
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
DAY_OF_MONTH NUMBER(2,0),
DAY_OF_WEEK NUMBER(1,0),
HOUR_OF_DAY NUMBER(2,0),
MINUTES_OF_HOUR NUMBER(2,0),
NUM_FLIGHTS NUMBER(1,0),
MULTI_CARRIER_FLAG NUMBER(1,0),
FIRST_OPERATING_CARRIER CHAR(2),
SECOND_OPERATING_CARRIER CHAR(2),
ORIGIN CHAR(3),
CONNECTION CHAR(3),
DESTINATION CHAR(3),
PLANNED_DEPARTURE_TIME TIMESTAMP (6) WITH TIME ZONE,
PLANNED_ARRIVAL_TIME TIMESTAMP (6) WITH TIME ZONE,
LAYOVER_DURATION NUMBER(4,0),
FIRST_FLIGHT_ID NUMBER(12,0),
SECOND_FLIGHT_ID NUMBER(12,0)
);
CREATE TABLE PAXDELAY.ROUTE_DEMANDS (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
NUM_FLIGHTS NUMBER,
ORIGIN CHAR(3),
CONNECTION VARCHAR2(3),
DESTINATION CHAR(3),
FIRST_OPERATING_CARRIER VARCHAR2(3),
SECOND_OPERATING_CARRIER VARCHAR2(3),
PASSENGERS NUMBER
);
CREATE TABLE PAXDELAY.T100_DB1B_ROUTE_DEMANDS (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
NUM_FLIGHTS NUMBER(1,0),
ORIGIN CHAR(3),
CONNECTION CHAR(3),
DESTINATION CHAR(3),
FIRST_OPERATING_CARRIER VARCHAR2(3),
SECOND_OPERATING_CARRIER VARCHAR2(3),
PASSENGERS NUMBER(10,4)
);
CREATE TABLE PAXDELAY.T100_MARKETS (
PASSENGERS NUMBER(6,0),
FREIGHT NUMBER(8,0),
MAIL NUMBER(8,0),
DISTANCE NUMBER(4,0),
UNIQUE_CARRIER VARCHAR2(6),
AIRLINE_ID NUMBER(6,0),
UNIQUE_CARRIER_NAME VARCHAR2(100),
UNIQUE_CARRIER_ENTITY VARCHAR2(6),
REGION CHAR(1),
CARRIER VARCHAR2(3),
CARRIER_NAME VARCHAR2(100),
CARRIER_GROUP NUMBER(2,0),
CARRIER_GROUP_NEW NUMBER(2,0),
ORIGIN CHAR(3),
ORIGIN_CITY_NAME VARCHAR2(50),
ORIGIN_CITY_CODE NUMBER(6,0),
ORIGIN_STATE CHAR(2),
ORIGIN_STATE_FIPS NUMBER(2,0),
ORIGIN_STATE_NAME VARCHAR2(50),
ORIGIN_WAC NUMBER(4,0),
DESTINATION CHAR(3),
DESTINATION_CITY_NAME VARCHAR2(50),
DESTINATION_CITY_CODE NUMBER(6,0),
DESTINATION_STATE CHAR(2),
DESTINATION_STATE_FIPS NUMBER(2,0),
DESTINATION_STATE_NAME VARCHAR2(50),
DESTINATION_WAC NUMBER(4,0),
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
DISTANCE_GROUP NUMBER(2,0),
SERVICE_CLASS CHAR(1)
);
CREATE TABLE PAXDELAY.T100_SEATS (
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
CARRIER VARCHAR2(3),
ORIGIN CHAR(3),
DESTINATION CHAR(3),
DEPARTURES_PERFORMED NUMBER(6,0),
NUM_AIRCRAFT_TYPES NUMBER(2,0),
SEATS_MEAN NUMBER(6,3),
SEATS_SQUARED_MEAN NUMBER(9,3),
SEATS_STD_DEV NUMBER(6,3),
SEATS_COEFF_VAR NUMBER(6,5)
);
CREATE TABLE PAXDELAY.T100_SEGMENTS (
DEPARTURES_SCHEDULED NUMBER(4,0),
DEPARTURES_PERFORMED NUMBER(4,0),
PAYLOAD NUMBER(10,0),
SEATS NUMBER(6,0),
PASSENGERS NUMBER(6,0),
FREIGHT NUMBER(8,0),
MAIL NUMBER(8,0),
DISTANCE NUMBER(4,0),
RAMP_TO_RAMP NUMBER(6,0),
AIR_TIME NUMBER(6,0),
UNIQUE_CARRIER VARCHAR2(6),
AIRLINE_ID NUMBER(6,0),
UNIQUE_CARRIER_NAME VARCHAR2(100),
UNIQUE_CARRIER_ENTITY VARCHAR2(6),
REGION CHAR(1),
CARRIER VARCHAR2(3),
CARRIER_NAME VARCHAR2(100),
CARRIER_GROUP NUMBER(2,0),
CARRIER_GROUP_NEW NUMBER(2,0),
ORIGIN CHAR(3),
ORIGIN_CITY_NAME VARCHAR2(50),
ORIGIN_CITY_CODE NUMBER(6,0),
ORIGIN_STATE CHAR(2),
ORIGIN_STATE_FIPS NUMBER(2,0),
ORIGIN_STATE_NAME VARCHAR2(50),
ORIGIN_WAC NUMBER(4,0),
DESTINATION CHAR(3),
DESTINATION_CITY_NAME VARCHAR2(50),
DESTINATION_CITY_CODE NUMBER(6,0),
DESTINATION_STATE CHAR(2),
DESTINATION_STATE_FIPS NUMBER(2,0),
DESTINATION_STATE_NAME VARCHAR2(50),
DESTINATION_WAC NUMBER(4,0),
AIRCRAFT_GROUP NUMBER(2,0),
AIRCRAFT_TYPE NUMBER(4,0),
AIRCRAFT_CONFIG NUMBER(1,0),
YEAR NUMBER(4,0),
QUARTER NUMBER(1,0),
MONTH NUMBER(2,0),
DISTANCE_GROUP NUMBER(2,0),
SERVICE_CLASS CHAR(1)
);
CREATE TABLE PAXDELAY.UNIQUE_CARRIER_ROUTES (
YEAR NUMBER(4,0),
NUM_FLIGHTS NUMBER(1,0),
FIRST_OPERATING_CARRIER CHAR(2),
SECOND_OPERATING_CARRIER CHAR(2),
ORIGIN CHAR(3),
CONNECTION CHAR(3),
DESTINATION CHAR(3)
);
CREATE TABLE public.temp_flights_aotp (
id Integer,
year Numeric,
quarter Numeric,
month Numeric,
day_of_month Numeric,
day_of_week Numeric,
hour_of_day Float,
minutes_of_hour Float,
carrier Char(2),
tail_number Varchar(10),
flight_number Varchar(6),
origin Varchar(5),
destination Varchar(5),
planned_departure_time TimestampTz,
planned_arrival_time TimestampTz,
actual_departure_time TimestampTz,
actual_arrival_time TimestampTz,
wheels_off_time TimestampTz,
wheels_on_time TimestampTz,
cancelled_flag Numeric,
diverted_flag Numeric,
num_flights Numeric,
flight_distance Numeric,
carrier_delay Numeric,
weather_delay Numeric,
nas_delay Numeric,
security_delay Numeric,
late_aircraft_delay Numeric
);
Insert data into the Route table (at this stage only data for scenario 1 is populated)
INSERT INTO scenarioDB_USA.Route
SELECT F.departsFrom, F.ArrivesAt,
CASE WHEN CD.CityCountry=CA.CityCountry THEN 'C' ELSE 'I' END AS RouteType,
round(AVG(DATEDIFF(n,F.plannedDepTime,F.plannedArrTime))), F.Scenario
FROM (((scenarioDB_USA.Flights F INNER JOIN scenarioDB_USA.Airport DA ON F.departsFrom=DA.AirportCode)
INNER JOIN scenarioDB_USA.Airport AA ON F.ArrivesAt=AA.AirportCode)
INNER JOIN airlines.City CD ON CD.CityCode=DA.CITY_MARKET_ID)
INNER JOIN airlines.City CA ON CA.CityCode=AA.CITY_MARKET_ID
GROUP BY F.departsFrom, F.ArrivesAt, F.Scenario, CD.CityCountry, CA.CityCountry;
### Penalties
Insert data into the penalty tables (at this stage only data for scenario 1 is populated)
DELETE FROM scenarioDB_USA.CancelDelayPenalties;
INSERT INTO scenarioDB_USA.CancelDelayPenalties SELECT * FROM scenarioDB.CancelDelayPenalties WHERE Scenario=1;
DELETE FROM scenarioDB_USA.DowngradePenalties;
INSERT INTO scenarioDB_USA.DowngradePenalties SELECT * FROM scenarioDB.DowngradePenalties WHERE Scenario=1;
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'B', 'P', 'C', 350);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'B', 'P', 'D', 100);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'B', 'P', 'I', 700);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'F', 'P', 'C', 450);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'F', 'P', 'D', 200);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'F', 'P', 'I', 1400);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'P', 'E', 'C', 50);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'P', 'E', 'D', 50);
INSERT INTO scenarioDB_USA.DowngradePenalties VALUES (1, 'P', 'E', 'I', 50);