-
Notifications
You must be signed in to change notification settings - Fork 0
Itineraries
Tomer Sagi edited this page Nov 10, 2016
·
1 revision
This page details the process required to generate Itineraries and Itinerary Flights.
Steps:
[3. Load ROUTE_DEMAND table from csv / create route demand table form Seats, Markets and Coupons data] (#3-load-route_demand-table-from-csv--create-route-demand-table-form-seats-markets-and-coupons-data-1)
CREATE SCHEMA PAXDELAY; 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.ASQP_CARRIERS (
CODE CHAR(2)
);
CREATE TABLE PAXDELAY.DB1B_COUPONS (
ITINERARY_ID numeric(16,0),
MARKET_ID numeric(16,0),
SEQUENCE_NUMBER numeric(2,0),
NUMBER_COUPONS numeric(2,0),
YEAR numeric(4,0),
QUARTER numeric(1,0),
ORIGIN char(3),
NUMBER_ORIGIN_AIRPORTS numeric(2,0),
ORIGIN_CITY_CODE numeric(6,0),
ORIGIN_COUNTRY_CODE varchar(3),
ORIGIN_STATE_FIPS numeric(2,0),
ORIGIN_STATE char(2),
ORIGIN_STATE_NAME varchar(50),
ORIGIN_WAC numeric(4,0),
DESTINATION char(3),
NUMBER_DESTINATION_AIRPORTS numeric(2,0),
DESTINATION_CITY_CODE numeric(6,0),
DESTINATION_COUNTRY_CODE varchar(3),
DESTINATION_STATE_FIPS numeric(2,0),
DESTINATION_STATE char(2),
DESTINATION_STATE_NAME varchar(50),
DESTINATION_WAC numeric(4,0),
BREAK_CODE char(1),
COUPON_TYPE char(1),
TICKETING_CARRIER varchar(3),
OPERATING_CARRIER varchar(3),
REPORTING_CARRIER varchar(3),
PASSENGERS numeric(4,0),
FARE_CLASS char(1),
DISTANCE numeric(4,0),
DISTANCE_GROUP numeric(2,0),
GATEWAY_FLAG numeric(1,0),
ITINERARY_GEOGRAPHY_TYPE numeric(1,0),
COUPON_GEOGRAPHY_TYPE numeric(1,0)
);
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),
LATE_AIRCRAFT_DELAY NUMBER(4,0),
ICAO_AIRCRAFT_CODE VARCHAR2(4),
SEATING_CAPACITY NUMBER(3,0),
scenario int
);
CREATE TABLE public.temp_itineraries
(
scenario numeric(5,0) NOT NULL,
num_flights numeric(1,0) NOT NULL,
first_flight_id numeric(12,0) NOT NULL,
second_flight_id numeric(12,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),
SCENARIO int
);
CREATE TABLE PAXDELAY.ITINERARY_ALLOCATION_SIMPLE (
Itinerary_id Integer,
First_Operating_Carrier Varchar(2),
Second_Operating_Carrier Varchar(2),
First_Flight_ID Integer,
Second_Flight_ID Integer,
Passengers Float
);
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_STAT 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_itineraries (
num_flights number(1, 0) not null,
first_flight_id number(12, 0) not null,
second_flight_id number(12, 0)
); CREATE OR REPLACE VIEW PAXDELAY.ITINERARY_ALLOCATION_VIEW AS SELECT Scenario, First_Operating_Carrier, Second_Operating_Carrier, First_Flight_ID, Second_Flight_ID, sum(Passengers) as psngrs, MIN(ITINERARY_ID) as ITIN_ID FROM PAXDELAY.ITINERARY_ALLOCATION_SIMPLE GROUP BY Scenario, First_Operating_Carrier, Second_Operating_Carrier,First_Flight_ID, Second_Flight_ID; CREATE SEQUENCE PAXDELAY.AIRLINE_ITINERARIES_ID_SEQ;
CREATE SEQUENCE PAXDELAY.DB1B_UNIQUE_SEGMENTS_ID_SEQ;
CREATE SEQUENCE PAXDELAY.DBOBJECTID_SEQUENCE;
CREATE SEQUENCE PAXDELAY.ITINERARY_ID_SEQ; GRANT VIEW ON SCHEMA PAXDELAY TO airline_update;
GRANT SELECT ON ALL TABLES IN SCHEMA PAXDELAY TO airline_update;
GRANT DELETE ON ALL TABLES IN SCHEMA PAXDELAY TO airline_update;
GRANT INSERT ON ALL TABLES IN SCHEMA PAXDELAY TO airline_update;
GRANT UPDATE ON ALL TABLES IN SCHEMA PAXDELAY TO airline_update;
GRANT ALL ON public.temp_itineraries TO airline_update;
GRANT ALL ON PAXDELAY.FLIGHT_ID_CONV TO airline_update;- Update planes that changed tail-number during 2014
UPDATE airlines.faaMASTER SET N_NUMBER='N530UA' WHERE N_NUMBER='N775FD';
UPDATE airlines.faaMASTER SET N_NUMBER='N511UA' WHERE N_NUMBER='N771FD';
UPDATE airlines.faaMASTER SET N_NUMBER='N534UA' WHERE N_NUMBER='N776FD';
UPDATE airlines.faaMASTER SET N_NUMBER='N573UA' WHERE N_NUMBER='N787FD';
INSERT INTO airlines.faaMASTER SELECT 'N658CT',FA1.SERIAL_NUMBER, FA1.MFR_MDL_CODE FROM airlines.faaMASTER FA1 WHERE FA1.N_NUMBER='N298SW';
INSERT INTO airlines.faaMASTER VALUES('N69834','99999','13848A3');-
Fill the PAXDELAY.AIRLINE_INVENTORIES table from the source: http://www.transtats.bts.gov/Tables.asp?DB_ID=125&DB_Name=Airline%20Origin%20and%20Destination%20Survey%20%28DB1B%29&DB_Short_Name=Origin%20and%20Destination%20Survey and choose the columns : CARRIER, FIRST_YEAR, SERIAL_NUMBER, TAIL_NUMBER, AIRCRAFT_STATUS, OPERATING_STATUS, NUMBER_OF_SEATS, MANUFACTURER, MODEL, CAPACITY_IN_POUNDS, ACQUISITION_DATE
-
Fill the PAXDELAY.ACT2ICAO by loading the csv file ACT2ICAO.csv from patchplanner\dm\src\main\resources
-
Fill the PAXDELAY.AIRCRAFT_CODE_MAPPINGS by loading the csv file AIRCRAFT_CODE_MAPPINGS.csv from patchplanner\dm\src\main\resources
-
Fill the FLIGHTS table
--make final Flights table
DELETE FROM PAXDELAY.FLIGHTS;
INSERT INTO PAXDELAY.FLIGHTS SELECT DISTINCT flightID,
to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','yyyy')) as year,
case when to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','mm')) between '1' and '3' then 1
when to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','mm')) between '4' and '6' then 2
when to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','mm')) between '7' and '9' then 3
when to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','mm')) between '10' and '12' then 4
end quarter,
to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','mm')) as month,
DAYOFMONTH (plannedDepTime::timestamptz AT TIME ZONE 'UTC') as day_of_month,
DAYOFWEEK (plannedDepTime::timestamptz AT TIME ZONE 'UTC') as day_of_week,
to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','HH24')) as hour_of_day,
to_number(to_char(plannedDepTime::timestamptz AT TIME ZONE 'UTC','MI')) as minutes_of_hour,
P.Carrier,
ot.TailNum,
ot.flightNo,
departsFrom as origin,
arrivesAt as destination,
plannedDepTime::timestamptz as planned_departure_time,
plannedArrTime::timestamptz as planned_arrival_time,
plannedDepTime::timestamptz as actual_departure_time,
plannedArrTime::timestamptz as actual_arrival_time,
TIMESTAMPADD(SQL_TSI_MINUTE, TaxiOut, plannedDepTime::timestamptz) as wheelsOff,
TIMESTAMPADD(SQL_TSI_MINUTE, TaxiOut + Airtime, plannedDepTime::timestamptz) as wheelsOn,
null, null,
1 as NUM_FLIGHTS,distanceMiles as FLIGHT_DISTANCE,
null, null, null, null, null,
CASE WHEN ai.TAIL_NUMBER IS NULL THEN a2i.ICAO ELSE ACM.ICAO_CODE END, CASE WHEN ai.TAIL_NUMBER IS NULL THEN ACT.NO_SEATS ELSE ai.NUMBER_OF_SEATS END,
ot.Scenario
FROM (((((scenarioDB_USA.Flights ot JOIN scenarioDB_USA.Plane P ON ot.TailNum=P.planeID AND OT.Scenario=P.scenario) LEFT OUTER JOIN PAXDELAY.AIRLINE_INVENTORIES ai ON ot.tailNum=ai.TAIL_NUMBER)
LEFT OUTER JOIN airlines.faaMASTER FM ON ot.tailNum=FM.N_NUMBER)
left outer join PAXDELAY.AIRCRAFT_CODE_MAPPINGS ACM ON ai.MANUFACTURER=ACM.INVENTORY_MANUFACTURER AND ai.MODEL=ACM.INVENTORY_MODEL)
LEFT OUTER JOIN airlines.ACFTREF ACT ON FM.MFR_MDL_CODE = ACT._CODE) LEFT OUTER JOIN PAXDELAY.ACT2ICAO a2i ON ACT._CODE=a2i.ACT_CODE; INSERT INTO PAXDELAY.ASQP_CARRIERS (CODE) values ('UA');
INSERT INTO PAXDELAY.ASQP_CARRIERS (CODE) values ('EV');
INSERT INTO PAXDELAY.ASQP_CARRIERS (CODE) values ('OO');3. Load ROUTE_DEMAND table from csv / create route demand table form Seats, Markets and Coupons data
#####Clear DB1B tables before filling them
DELETE FROM PAXDELAY.DB1B_COUPONS;
DELETE FROM PAXDELAY.DB1B_MARKETS;instead, you can fill these tables from the source : http://www.transtats.bts.gov/Tables.asp?DB_ID=125&DB_Name=Airline%20Origin%20and%20Destination%20Survey%20%28DB1B%29&DB_Short_Name=Origin%20and%20Destination%20Survey
/*fill DB1B tables via talend (running)*/
SELECT count(*) FROM PAXDELAY.DB1B_MARKETS;
SELECT count(*) FROM PAXDELAY.DB1B_COUPONS; #####fill DB1B_UNIQUE_CARRIER_SEGMENTS
DELETE FROM PAXDELAY.DB1B_UNIQUE_CARRIER_SEGMENTS;
INSERT INTO PAXDELAY.DB1B_UNIQUE_CARRIER_SEGMENTS(QUARTER,TICKETING_CARRIER, OPERATING_CARRIER, ORIGIN, DESTINATION)
SELECT DISTINCT DBC1.QUARTER, TICKETING_CARRIER, OPERATING_CARRIER, ORIGIN, DESTINATION FROM PAXDELAY.DB1B_COUPONS DBC1;#####fill DB1B_ROUTE_DEMANDS
/*count number of stops*/
/*count coupons: single coupon is therefore non-stop*/
SELECT DBC1.NUMBER_COUPONS, sum(DBC1.PASSENGERS) FROM PAXDELAY.DB1B_COUPONS DBC1 WHERE (TICKETING_CARRIER='UA' OR OPERATING_CARRIER='UA') AND SEQUENCE_NUMBER=1 GROUP BY DBC1.NUMBER_COUPONS;
/*two coupons therefore if we have a break at 1 it must be two non-stop flights and if not, then its a one-stop route */
SELECT DBC1.NUMBER_COUPONS, sum(DBC1.PASSENGERS) FROM PAXDELAY.DB1B_COUPONS DBC1 WHERE (TICKETING_CARRIER='UA' OR OPERATING_CARRIER='UA') AND SEQUENCE_NUMBER=1 AND NUMBER_COUPONS=2 AND BREAK_CODE='X' GROUP BY DBC1.NUMBER_COUPONS;
--more than three coupons therefore if we have a break at 1 it must be a non-stop flight
SELECT DBC1.NUMBER_COUPONS, sum(DBC1.PASSENGERS) FROM PAXDELAY.DB1B_COUPONS DBC1 WHERE (TICKETING_CARRIER='UA' OR OPERATING_CARRIER='UA') AND SEQUENCE_NUMBER=1 AND NUMBER_COUPONS>2 AND BREAK_CODE='X' GROUP BY DBC1.NUMBER_COUPONS;
--more than three coupons onestop
SELECT DBC1.NUMBER_COUPONS, sum(DBC1.PASSENGERS) FROM PAXDELAY.DB1B_COUPONS DBC1
INNER JOIN PAXDELAY.DB1B_COUPONS DBC2 ON DBc1.ITINERARY_ID=DBC2.ITINERARY_ID AND DBC1.SEQUENCE_NUMBER+1=DBC2.SEQUENCE_NUMBER WHERE DBC1.BREAK_CODE!='X' AND DBC2.BREAK_CODE='X'AND (DBC1.TICKETING_CARRIER='UA' OR DBC1.OPERATING_CARRIER='UA') AND DBC1.SEQUENCE_NUMBER=1 AND DBC1.NUMBER_COUPONS>2 GROUP BY DBC1.NUMBER_COUPONS;
SELECT Scenario, NumLegs, count(*) FROM (SELECT Scenario,ItinID, count(*) as NumLegs FROM scenarioDB.ItineraryFlights GROUP BY Scenario,ItinID) MT GROUP BY Scenario, NumLegs ORDER BY Scenario, NumLegs ;nonstop route demand
DELETE FROM PAXDELAY.DB1B_ROUTE_DEMANDS;
INSERT INTO PAXDELAY.DB1B_ROUTE_DEMANDS
SELECT DBC1.YEAR, DBC1.QUARTER, 1, DBC1.ORIGIN, '', DBC1.DESTINATION, DBC1.OPERATING_CARRIER, '', SUM(DBC1.PASSENGERS) FROM PAXDELAY.DB1B_COUPONS DBC1
GROUP BY DBC1.YEAR, DBC1.QUARTER, DBC1.ORIGIN, DBC1.DESTINATION, DBC1.OPERATING_CARRIER;onestop route demand
INSERT INTO PAXDELAY.DB1B_ROUTE_DEMANDS
SELECT DBC1.YEAR, DBC1.QUARTER, 2, DBC1.ORIGIN, DBC1.DESTINATION, DBC2.DESTINATION, DBC1.OPERATING_CARRIER, DBC2.OPERATING_CARRIER, SUM(DBC1.PASSENGERS) FROM PAXDELAY.DB1B_COUPONS DBC1
INNER JOIN PAXDELAY.DB1B_COUPONS DBC2 ON DBc1.ITINERARY_ID=DBC2.ITINERARY_ID AND DBC1.SEQUENCE_NUMBER+1=DBC2.SEQUENCE_NUMBER
WHERE DBC1.BREAK_CODE!='X' --this ensures one-stop route
GROUP BY DBC1.YEAR, DBC1.QUARTER, 2, DBC1.ORIGIN, DBC1.DESTINATION, DBC2.DESTINATION, DBC1.OPERATING_CARRIER, DBC2.OPERATING_CARRIER;
--ORDER BY ITINERARY_ID, SEQUENCE_NUMBER LIMIT 200; -- WHERE DBC.TICKETING_CARRIER='UA' OR OPERATING_CARRIER='UA' LIMIT 100; DELETE FROM PAXDELAY.T100_MARKETS;#####Load via talend to T100MARKETS
validation:
SELECT count(*) FROM PAXDELAY.T100_MARKETS; --256540#####fill T100_DB1B_ROUTE_DEMANDS
--DB1B_T100_SEGMENT_FACTORS
/*For each carrier-segment s we calculate the monthly scaling factor */
DROP VIEW PAXDELAY.DB1B_CARRIER_SEGMENT_DEMANDS;
CREATE VIEW PAXDELAY.DB1B_CARRIER_SEGMENT_DEMANDS AS SELECT DBM.YEAR, DBM.QUARTER, DBM.ORIGIN, DBM.DESTINATION, DBM.OPERATING_CARRIER, sum(DBM.PASSENGERS) AS pass FROM PAXDELAY.DB1B_COUPONS DBM WHERE PASSENGERS>0 GROUP BY DBM.YEAR, DBM.QUARTER, DBM.ORIGIN, DBM.DESTINATION, DBM.OPERATING_CARRIER;
DROP VIEW PAXDELAY.T100_CARRIER_SEGMENT_DEMANDS;
CREATE VIEW PAXDELAY.T100_CARRIER_SEGMENT_DEMANDS AS SELECT TM.YEAR, TM.QUARTER, TM.MONTH, TM.ORIGIN, TM.DESTINATION, TM.CARRIER, sum(TM.PASSENGERS) AS pass FROM PAXDELAY.T100_MARKETS TM WHERE PASSENGERS>0 GROUP BY TM.YEAR, TM.QUARTER, TM.MONTH, TM.ORIGIN, TM.DESTINATION, TM.CARRIER;
DELETE FROM PAXDELAY.DB1B_T100_SEGMENT_FACTORS;
INSERT INTO PAXDELAY.DB1B_T100_SEGMENT_FACTORS SELECT T100.YEAR, T100.QUARTER, T100.MONTH, T100.CARRIER, T100.ORIGIN, T100.DESTINATION, DB1B.pass AS DB1BP, T100.pass AS T100P, CASE WHEN COALESCE(DB1B.pass,0)=0 THEN 3.33 ELSE T100.pass/DB1B.pass END as scale FROM PAXDELAY.T100_CARRIER_SEGMENT_DEMANDS T100
INNER JOIN PAXDELAY.DB1B_CARRIER_SEGMENT_DEMANDS DB1B
ON T100.YEAR=DB1B.YEAR AND T100.QUARTER = DB1b.QUARTER AND T100.ORIGIN=DB1B.ORIGIN AND T100.DESTINATION=DB1B.DESTINATION AND T100.CARRIER=DB1B.OPERATING_CARRIER;
--Scale one-stop DB1B
-- minimum alpha-s
CREATE VIEW PAXDELAY.MIN_ALPHA_S AS
SELECT SF.YEAR, SF.QUARTER, SF.CARRIER, SF.ORIGIN, SF.DESTINATION, MIN(CASE WHEN SF.SCALING_FACTOR<2 THEN 2 WHEN SF.SCALING_FACTOR>5 THEN 5 ELSE SF.SCALING_FACTOR END) minAlphaS
FROM PAXDELAY.DB1B_T100_SEGMENT_FACTORS SF GROUP BY SF.YEAR, SF.QUARTER, SF.CARRIER, SF.ORIGIN, SF.DESTINATION;
CREATE VIEW PAXDELAY.DB1B_ROUTEDEMANDS_MINSCALES AS
SELECT RD.YEAR, RD.QUARTER, RD.NUM_FLIGHTS, RD.ORIGIN, RD."CONNECTION", RD.DESTINATION, RD.FIRST_OPERATING_CARRIER, RD.SECOND_OPERATING_CARRIER ,RD.PASSENGERS,
CASE WHEN MAS1.minAlphaS IS NULL THEN (CASE WHEN MAS2.minAlphaS ISNULL THEN 3.33 ELSE MAS2.minAlphaS END) ELSE (CASE WHEN MAS2.minAlphaS ISNULL THEN MAS1.minAlphaS ELSE (CASE WHEN MAS1.minAlphaS <MAS2.minAlphaS THEN MAS1.minAlphaS ELSE MAS2.minAlphaS END) END) END AS scale
FROM (PAXDELAY.DB1B_ROUTE_DEMANDS RD
LEFT OUTER JOIN PAXDELAY.MIN_ALPHA_S MAS1 ON RD.YEAR=MAS1.YEAR AND RD.QUARTER=MAS1.QUARTER AND RD.ORIGIN = MAS1.ORIGIN AND RD."CONNECTION"=MAS1.DESTINATION AND RD.FIRST_OPERATING_CARRIER=MAS1.CARRIER)
LEFT OUTER JOIN PAXDELAY.MIN_ALPHA_S MAS2 ON RD.YEAR=MAS2.YEAR AND RD.QUARTER=MAS2.QUARTER AND RD."CONNECTION" = MAS2.ORIGIN AND RD.DESTINATION=MAS2.DESTINATION AND RD.SECOND_OPERATING_CARRIER=MAS2.CARRIER
WHERE RD.NUM_FLIGHTS=2;
--check original ratio vs new ratio of one stop / nonstop
SELECT RD.NUM_FLIGHTS,sum(Rd.PASSENGERS) FROM PAXDELAY.DB1B_ROUTE_DEMANDS RD GROUP BY RD.NUM_FLIGHTS; --1 62019116 2 16124041
SELECT sum(round(PASSENGERS*scale)) FROM PAXDELAY.DB1B_ROUTEDEMANDS_MINSCALES; --47508371.0000
--perform uniform scaling if result is inconsistent
INSERT INTO PAXDELAY.T100_DB1B_ROUTE_DEMANDS SELECT DB1B.YEAR, DB1B.QUARTER, QM.MONTH, DB1B.NUM_FLIGHTS, DB1B.ORIGIN, DB1B."CONNECTION", DB1B.DESTINATION, DB1B.FIRST_OPERATING_CARRIER, DB1B.SECOND_OPERATING_CARRIER, sum(round(DB1B.PASSENGERS*DB1B.scale))
FROM PAXDELAY.DB1B_ROUTEDEMANDS_MINSCALES DB1B INNER JOIN PAXDELAY.QUARTER_MONTH QM
ON DB1b.QUARTER=QM.QUARTER
GROUP BY DB1B.YEAR, DB1B.QUARTER, QM.MONTH, DB1B.NUM_FLIGHTS, DB1B.ORIGIN, DB1B."CONNECTION", DB1B.DESTINATION, DB1B.FIRST_OPERATING_CARRIER, DB1B.SECOND_OPERATING_CARRIER;#####Fill T100_SEATS Fill table T100_SEGMENTS from the source: http://www.transtats.bts.gov/Tables.asp?DB_ID=110&DB_Name=Air%20Carrier%20Statistics%20%28Form%2041%20Traffic%29-%20%20U.S.%20Carriers&DB_Short_Name=Air%20Carriers choosing the columns: year, quarter, month, carrier, origin, destination, aircraft_types, seats, departures_performed
DELETE FROM PAXDELAY.T100_SEATS;
insert into PAXDELAY.T100_SEATS
select t100.year, t100.quarter, t100.month, t100.carrier,
t100.origin, t100.destination,
t100.departures_performed, t100.num_aircraft_types,
t100.seats_mean, t100.seats_squared_mean,
sqrt(t100.seats_variance) as seats_std_dev,
decode(t100.seats_mean, 0, 0, sqrt(t100.seats_variance) / t100.seats_mean)
from
(select t100.year, t100.quarter, t100.month, t100.carrier,
t100.origin, t100.destination, t100.departures_performed,
t100.num_aircraft_types, t100.seats_mean,
t100.seats_squared_mean,
decode(t100.departures_performed, 1, 0,
round((t100.seats_squared_mean - power(t100.seats_mean, 2)) *
t100.departures_performed / (t100.departures_performed - 1), 3))
as seats_variance
from
(select t100.year, t100.quarter, t100.month, t100.carrier,
t100.origin, t100.destination, count(*) as num_aircraft_types,
sum(t100.seats) / sum(t100.departures_performed) as seats_mean,
sum(power(t100.seats, 2) / t100.departures_performed) /
sum(t100.departures_performed) as seats_squared_mean,
sum(t100.departures_performed) as departures_performed
from PAXDELAY.T100_SEGMENTS t100
where t100.departures_performed > 0
group by t100.year, t100.quarter, t100.month, t100.carrier,
t100.origin, t100.destination
) t100
) t100;
update PAXDELAY.t100_seats
set carrier = 'US'
where carrier = 'HP';
3.3 Load PAXDELAY.ROUTE_DEMANDS table from N:\Project\AirlineData\SimulatedItineraries2014\route_demand_2014.csv.
insert into PAXDELAY.unique_carrier_routes
select distinct year, num_flights, first_operating_carrier, second_operating_carrier,
origin, connection, destination
from PAXDELAY.route_demands
where exists
(
select code from PAXDELAY.asqp_carriers
where code = first_operating_carrier
)
and (num_flights = 1 or exists
(
select code from PAXDELAY.asqp_carriers
where code = second_operating_carrier
)
);This step fills the temp_itineraries table with all possible itineraries by routes table
5.1 Run ItineraryGenerator.java from pdata_sim project, itinerary simulator module for insert data to public.temp_itineraries table.
- The property file for this generator should contain the following entries:
JDBC_URL=jdbc:vertica://16.55.213.35:5433/ALP2
DATABASE_USERNAME=uname
DATABASE_PASSWORD=upasswd
DATABASE_SCHEMA=ScenarioDB_USA
OUTPUT_DIRECTORY=N:/Project/AirlineData/SimulatedItineraries2014
FIRST_DATE=01/01/2014
LAST_DATE=12/31/2014
CARRIER.01=UA
CARRIER.02=OO
CARRIER.03=EV
MAXIMUM_NUMBER_CONNECTIONS=100
MAXIMUM_CONNECTION_TIME=180
SCENARIOS=2,3- Program Argument Examples (1:logger props, 2:Generator props)
N:/Project/AirlineData/SimulatedItineraries2014/DebugLogger.properties N:/Project/AirlineData/SimulatedItineraries2014/ItineraryGenerator.properties
- validation Check for non-overlapping place/time for 2 flight itineraries
SELECT count(*) FROM public.temp_itineraries_y y, PAXDELAY.FLIGHTS x, PAXDELAY.FLIGHTS z
where y.num_flights=2
and y.first_flight_id = x.Id
and y.second_flight_id = z.Id
and x.destination <> z.origin; --0 records (02.08.2016)
SELECT count(*) FROM public.temp_itineraries_y y, PAXDELAY.FLIGHTS x, PAXDELAY.FLIGHTS z
where y.num_flights=2
and y.first_flight_id = x.Id
and y.second_flight_id = z.Id
and TIMESTAMPDIFF('minute',x.PLANNED_ARRIVAL_TIME, z.PLANNED_DEPARTURE_TIME) < 30; --0 records (02.08.2016)
SELECT count(*) FROM public.temp_itineraries_y y, PAXDELAY.FLIGHTS x, PAXDELAY.FLIGHTS z
where y.num_flights > 2; --0 records (02.08.2016) -
Fill PAXDELAY.ITINERARIES:
Use the following when recreating the whole itineraries table
DELETE FROM PAXDELAY.itineraries;
DROP SEQUENCE PAXDELAY.itinerary_id_seq;
CREATE SEQUENCE PAXDELAY.itinerary_id_seq; Otherwise use
DELETE FROM PAXDELAY.itineraries WHERE scenarios IN (2,3); In both cases do the following:
insert into PAXDELAY.itineraries
select PAXDELAY.itinerary_id_seq.nextval,
ft.year, ft.quarter, ft.month, ft.day_of_month,
ft.day_of_week, ft.hour_of_day, ft.minutes_of_hour, 1,
0, ft.carrier, null, ft.origin, null, ft.destination,
ft.planned_departure_time, ft.planned_arrival_time,
null, ft.id, null
from public.temp_itineraries ti
join PAXDELAY.flights ft
on ft.id = ti.first_flight_id
where ti.num_flights = 1;
insert into PAXDELAY.itineraries
select PAXDELAY.itinerary_id_seq.nextval,
ft1.year, ft1.quarter, ft1.month, ft1.day_of_month,
ft1.day_of_week, ft1.hour_of_day, ft1.minutes_of_hour, 2,
decode(ti.num_flights, 2,
decode(ft1.carrier, ft2.carrier, 0, 1), 0),
ft1.carrier, ft2.carrier, ft1.origin, ft1.destination,
ft2.destination, ft1.planned_departure_time, ft2.planned_arrival_time,
extract(hour from (ft2.planned_departure_time - ft1.planned_arrival_time)) * 60 +
extract(minute from(ft2.planned_departure_time - ft1.planned_arrival_time)),
ft1.id, ft2.id
from public.temp_itineraries ti
join PAXDELAY.flights ft1
on ft1.id = ti.first_flight_id
join PAXDELAY.flights ft2
on ft2.id = ti.second_flight_id
where ti.num_flights = 2;-
Run AutomatedPassengerAllocator.java from [pdata_sim project] to insert data to PAXDELAY.ITINERARY_ALLOCATION_SIMPLE
-
The property file for this generator should contain the following entries:
JDBC_URL=jdbc:vertica://16.55.213.35:5433/ALP2
DATABASE_USERNAME=uname
DATABASE_PASSWORD=upasswd
DATABASE_SCHEMA=ScenarioDB_USA
ALLOCATION_CARRIER1=UA
ALLOCATION_CARRIER2=EV
ALLOCATION_CARRIER3=OO
ALLOCATION_YEAR=2014
ALLOCATION_FIRST_MONTH=1
ALLOCATION_LAST_MONTH=12
ALLOCATION_PARAMETERS_DIRECTORY=src\\main\\resources\\
ALLOCATION_PARAMETERS_FILENAME=choiceparams.properties
FLIGHTS_TABLE=FLIGHTS
ALLOCATION_OUTPUT_DIRECTORY=N:/Project/AirlineData/SimulatedItineraries2014
SCENARIOS=2,3- Program Argument Examples (1:logger props, 2:Generator props)
src\\main\\resources\\log4j.properties src\\main\\resources\\props.properties
Delete from scenarios being filled, for example:
DELETE FROM scenarioDB_USA.Itineraries WHERE Scenario IN (2,3);
DELETE FROM scenarioDB_USA.ItineraryFlights WHERE Scenario IN (2,3);INSERT INTO scenarioDB_USA.Itineraries SELECT IAS.Scenario, ias.ITIN_ID,'A', 100+f.distanceMiles/5.0,IAS.psngrs
FROM PAXDELAY.ITINERARY_ALLOCATION_VIEW IAS INNER JOIN scenarioDB_USA.Flights F ON IAS.first_flight_id=F.flightID AND IAS.Scenario=F.Scenario
WHERE IAS.Second_Flight_ID IS NULL AND IAS.Scenario IN (2,3);INSERT INTO scenarioDB_USA.ItineraryFlights SELECT IAS.Scenario, IAS.ITIN_ID, IAS.first_flight_id,'Y'
FROM PAXDELAY.ITINERARY_ALLOCATION_VIEW IAS
INNER JOIN scenarioDB_USA.Flights F ON IAS.first_flight_id=F.flightID AND IAS.Scenario=F.Scenario
WHERE IAS.Second_Flight_ID IS NULL AND IAS.Scenario IN (2,3);INSERT INTO scenarioDB_USA.Itineraries SELECT IAS.Scenario, ias.ITIN_ID,'A', 100+f.distanceMiles/5.0,IAS.psngrs
FROM PAXDELAY.ITINERARY_ALLOCATION_VIEW IAS INNER JOIN scenarioDB_USA.Flights F ON IAS.first_flight_id=F.flightID AND F.Scenario=IAS.Scenario
WHERE IAS.Second_Flight_ID IS NOT NULL AND IAS.Scenario IN (2,3);
INSERT INTO scenarioDB_USA.ItineraryFlights SELECT IAS.Scenario, IAS.ITIN_ID, F.flightID,'Y'
FROM PAXDELAY.ITINERARY_ALLOCATION_VIEW IAS INNER JOIN scenarioDB_USA.Flights F ON IAS.first_flight_id=F.flightID AND F.Scenario=IAS.Scenario
WHERE IAS.Second_Flight_ID IS NOT NULL AND IAS.Scenario IN (2,3);
INSERT INTO scenarioDB_USA.ItineraryFlights SELECT IAS.Scenario, IAS.ITIN_ID, F.flightID,'Y'
FROM PAXDELAY.ITINERARY_ALLOCATION_VIEW IAS INNER JOIN scenarioDB_USA.Flights F ON IAS.Second_Flight_ID=F.flightID AND IAS.Scenario=F.Scenario
WHERE IAS.Second_Flight_ID IS NOT NULL AND IAS.Scenario IN (2,3);- Using the patchplanner-dm codebase, run ItineraryFixer.java with the following arguments:
ALLOCATE 2,3 1.25,1.5,2
The second argument represents the list of scenarios to be allocated to passenger classes and the second are the price multipliers for Economy-Plus, Business, and First class respectively from the economy class base price.