diff --git a/moardata.sql b/moardata.sql new file mode 100644 index 0000000000000000000000000000000000000000..803c60346ef7dff7222cf5b7a5f43ad31c6baa04 --- /dev/null +++ b/moardata.sql @@ -0,0 +1,402 @@ +-- ACS + + +CREATE TABLE acs3yr ( + "serialno" VARCHAR(15), + "rt" VARCHAR(3), + "sporder" INTEGER, + "puma" INTEGER, + "st" INTEGER, + "adjinc" DOUBLE, + "pwgtp" INTEGER, + "agep" INTEGER, + "cit" INTEGER, + "citwp" INTEGER, + "cow" INTEGER, + "ddrs" INTEGER, + "dear" INTEGER, + "deye" INTEGER, + "dout" INTEGER, + "dphy" INTEGER, + "drat" INTEGER, + "dratx" INTEGER, + "drem" INTEGER, + "eng" INTEGER, + "fer" INTEGER, + "gcl" INTEGER, + "gcm" INTEGER, + "gcr" INTEGER, + "hins1" INTEGER, + "hins2" INTEGER, + "hins3" INTEGER, + "hins4" INTEGER, + "hins5" INTEGER, + "hins6" INTEGER, + "hins7" INTEGER, + "intp" INTEGER, + "jwmnp" INTEGER, + "jwrip" INTEGER, + "jwtr" INTEGER, + "lanx" INTEGER, + "mar" INTEGER, + "marhd" INTEGER, + "marhm" INTEGER, + "marht" INTEGER, + "marhw" INTEGER, + "marhyp" INTEGER, + "mig" INTEGER, + "mil" INTEGER, + "mlpa" INTEGER, + "mlpb" INTEGER, + "mlpc" INTEGER, + "mlpd" INTEGER, + "mlpe" INTEGER, + "mlpf" INTEGER, + "mlpg" INTEGER, + "mlph" INTEGER, + "mlpi" INTEGER, + "mlpj" INTEGER, + "mlpk" INTEGER, + "nwab" INTEGER, + "nwav" INTEGER, + "nwla" INTEGER, + "nwlk" INTEGER, + "nwre" INTEGER, + "oip" INTEGER, + "pap" INTEGER, + "relp" INTEGER, + "retp" INTEGER, + "sch" INTEGER, + "schg" INTEGER, + "schl" INTEGER, + "semp" INTEGER, + "sex" INTEGER, + "ssip" INTEGER, + "ssp" INTEGER, + "wagp" INTEGER, + "wkhp" INTEGER, + "wkl" INTEGER, + "wkw" INTEGER, + "yoep" INTEGER, + "anc" INTEGER, + "anc1p" INTEGER, + "anc2p" INTEGER, + "decade" INTEGER, + "dis" INTEGER, + "drivesp" INTEGER, + "esp" INTEGER, + "esr" INTEGER, + "hicov" INTEGER, + "hisp" INTEGER, + "indp" INTEGER, + "jwap" INTEGER, + "jwdp" INTEGER, + "lanp" INTEGER, + "migpuma" INTEGER, + "migsp" INTEGER, + "msp" INTEGER, + "naicsp" VARCHAR(15), + "nativity" INTEGER, + "nop" INTEGER, + "oc" INTEGER, + "occp02" VARCHAR(15), + "occp10" VARCHAR(15), + "paoc" INTEGER, + "pernp" INTEGER, + "pincp" INTEGER, + "pobp" INTEGER, + "povpip" INTEGER, + "powpuma" INTEGER, + "powsp" INTEGER, + "privcov" INTEGER, + "pubcov" INTEGER, + "qtrbir" INTEGER, + "rac1p" INTEGER, + "rac2p" INTEGER, + "rac3p" INTEGER, + "racaian" INTEGER, + "racasn" INTEGER, + "racblk" INTEGER, + "racnhpi" INTEGER, + "racnum" INTEGER, + "racsor" INTEGER, + "racwht" INTEGER, + "rc" INTEGER, + "sfn" INTEGER, + "sfr" INTEGER, + "socp00" VARCHAR(15), + "socp10" VARCHAR(15), + "vps" INTEGER, + "waob" INTEGER, + "fagep" INTEGER, + "fancp" INTEGER, + "fcitp" INTEGER, + "fcitwp" INTEGER, + "fcowp" INTEGER, + "fddrsp" INTEGER, + "fdearp" INTEGER, + "fdeyep" INTEGER, + "fdoutp" INTEGER, + "fdphyp" INTEGER, + "fdratp" INTEGER, + "fdratxp" INTEGER, + "fdremp" INTEGER, + "fengp" INTEGER, + "fesrp" INTEGER, + "fferp" INTEGER, + "fgclp" INTEGER, + "fgcmp" INTEGER, + "fgcrp" INTEGER, + "fhins1p" INTEGER, + "fhins2p" INTEGER, + "fhins3c" INTEGER, + "fhins3p" INTEGER, + "fhins4c" INTEGER, + "fhins4p" INTEGER, + "fhins5c" INTEGER, + "fhins5p" INTEGER, + "fhins6p" INTEGER, + "fhins7p" INTEGER, + "fhisp" INTEGER, + "findp" INTEGER, + "fintp" INTEGER, + "fjwdp" INTEGER, + "fjwmnp" INTEGER, + "fjwrip" INTEGER, + "fjwtrp" INTEGER, + "flanp" INTEGER, + "flanxp" INTEGER, + "fmarhdp" INTEGER, + "fmarhmp" INTEGER, + "fmarhtp" INTEGER, + "fmarhwp" INTEGER, + "fmarhyp" INTEGER, + "fmarp" INTEGER, + "fmigp" INTEGER, + "fmigsp" INTEGER, + "fmilpp" INTEGER, + "fmilsp" INTEGER, + "foccp" INTEGER, + "foip" INTEGER, + "fpap" INTEGER, + "fpobp" INTEGER, + "fpowsp" INTEGER, + "fracp" INTEGER, + "frelp" INTEGER, + "fretp" INTEGER, + "fschgp" INTEGER, + "fschlp" INTEGER, + "fschp" INTEGER, + "fsemp" INTEGER, + "fsexp" INTEGER, + "fssip" INTEGER, + "fssp" INTEGER, + "fwagp" INTEGER, + "fwkhp" INTEGER, + "fwklp" INTEGER, + "fwkwp" INTEGER, + "fyoep" INTEGER, + "pwgtp1" INTEGER, + "pwgtp2" INTEGER, + "pwgtp3" INTEGER, + "pwgtp4" INTEGER, + "pwgtp5" INTEGER, + "pwgtp6" INTEGER, + "pwgtp7" INTEGER, + "pwgtp8" INTEGER, + "pwgtp9" INTEGER, + "pwgtp10" INTEGER, + "pwgtp11" INTEGER, + "pwgtp12" INTEGER, + "pwgtp13" INTEGER, + "pwgtp14" INTEGER, + "pwgtp15" INTEGER, + "pwgtp16" INTEGER, + "pwgtp17" INTEGER, + "pwgtp18" INTEGER, + "pwgtp19" INTEGER, + "pwgtp20" INTEGER, + "pwgtp21" INTEGER, + "pwgtp22" INTEGER, + "pwgtp23" INTEGER, + "pwgtp24" INTEGER, + "pwgtp25" INTEGER, + "pwgtp26" INTEGER, + "pwgtp27" INTEGER, + "pwgtp28" INTEGER, + "pwgtp29" INTEGER, + "pwgtp30" INTEGER, + "pwgtp31" INTEGER, + "pwgtp32" INTEGER, + "pwgtp33" INTEGER, + "pwgtp34" INTEGER, + "pwgtp35" INTEGER, + "pwgtp36" INTEGER, + "pwgtp37" INTEGER, + "pwgtp38" INTEGER, + "pwgtp39" INTEGER, + "pwgtp40" INTEGER, + "pwgtp41" INTEGER, + "pwgtp42" INTEGER, + "pwgtp43" INTEGER, + "pwgtp44" INTEGER, + "pwgtp45" INTEGER, + "pwgtp46" INTEGER, + "pwgtp47" INTEGER, + "pwgtp48" INTEGER, + "pwgtp49" INTEGER, + "pwgtp50" INTEGER, + "pwgtp51" INTEGER, + "pwgtp52" INTEGER, + "pwgtp53" INTEGER, + "pwgtp54" INTEGER, + "pwgtp55" INTEGER, + "pwgtp56" INTEGER, + "pwgtp57" INTEGER, + "pwgtp58" INTEGER, + "pwgtp59" INTEGER, + "pwgtp60" INTEGER, + "pwgtp61" INTEGER, + "pwgtp62" INTEGER, + "pwgtp63" INTEGER, + "pwgtp64" INTEGER, + "pwgtp65" INTEGER, + "pwgtp66" INTEGER, + "pwgtp67" INTEGER, + "pwgtp68" INTEGER, + "pwgtp69" INTEGER, + "pwgtp70" INTEGER, + "pwgtp71" INTEGER, + "pwgtp72" INTEGER, + "pwgtp73" INTEGER, + "pwgtp74" INTEGER, + "pwgtp75" INTEGER, + "pwgtp76" INTEGER, + "pwgtp77" INTEGER, + "pwgtp78" INTEGER, + "pwgtp79" INTEGER, + "pwgtp80" INTEGER +); + + +copy into acs3yr from '/scratch/hannes/acs3yr.csv' locked; + + +-- ontime + +CREATE TABLE "ontime" ( + "Year" smallint DEFAULT NULL, + "Quarter" tinyint DEFAULT NULL, + "Month" tinyint DEFAULT NULL, + "DayofMonth" tinyint DEFAULT NULL, + "DayOfWeek" tinyint DEFAULT NULL, + "FlightDate" date DEFAULT NULL, + "UniqueCarrier" char(7) DEFAULT NULL, + "AirlineID" decimal(8,2) DEFAULT NULL, + "Carrier" char(2) DEFAULT NULL, + "TailNum" varchar(50) DEFAULT NULL, + "FlightNum" varchar(10) DEFAULT NULL, + "OriginAirportID" integer, + "OriginAirportSeqID" integer, + "OriginCityMarketID" integer, + "Origin" char(5) DEFAULT NULL, + "OriginCityName" varchar(100) DEFAULT NULL, + "OriginState" char(2) DEFAULT NULL, + "OriginStateFips" varchar(10) DEFAULT NULL, + "OriginStateName" varchar(100) DEFAULT NULL, + "OriginWac" decimal(8,2) DEFAULT NULL, + "DestAirportID" integer, + "DestAirportSeqID" integer, + "DestCityMarketID" integer, + "Dest" char(5) DEFAULT NULL, + "DestCityName" varchar(100) DEFAULT NULL, + "DestState" char(2) DEFAULT NULL, + "DestStateFips" varchar(10) DEFAULT NULL, + "DestStateName" varchar(100) DEFAULT NULL, + "DestWac" decimal(8,2) DEFAULT NULL, + "CRSDepTime" decimal(8,2) DEFAULT NULL, + "DepTime" decimal(8,2) DEFAULT NULL, + "DepDelay" decimal(8,2) DEFAULT NULL, + "DepDelayMinutes" decimal(8,2) DEFAULT NULL, + "DepDel15" decimal(8,2) DEFAULT NULL, + "DepartureDelayGroups" decimal(8,2) DEFAULT NULL, + "DepTimeBlk" varchar(20) DEFAULT NULL, + "TaxiOut" decimal(8,2) DEFAULT NULL, + "WheelsOff" decimal(8,2) DEFAULT NULL, + "WheelsOn" decimal(8,2) DEFAULT NULL, + "TaxiIn" decimal(8,2) DEFAULT NULL, + "CRSArrTime" decimal(8,2) DEFAULT NULL, + "ArrTime" decimal(8,2) DEFAULT NULL, + "ArrDelay" decimal(8,2) DEFAULT NULL, + "ArrDelayMinutes" decimal(8,2) DEFAULT NULL, + "ArrDel15" decimal(8,2) DEFAULT NULL, + "ArrivalDelayGroups" decimal(8,2) DEFAULT NULL, + "ArrTimeBlk" varchar(20) DEFAULT NULL, + "Cancelled" tinyint DEFAULT NULL, + "CancellationCode" char(1) DEFAULT NULL, + "Diverted" tinyint DEFAULT NULL, + "CRSElapsedTime" decimal(8,2) DEFAULT NULL, + "ActualElapsedTime" decimal(8,2) DEFAULT NULL, + "AirTime" decimal(8,2) DEFAULT NULL, + "Flights" decimal(8,2) DEFAULT NULL, + "Distance" decimal(8,2) DEFAULT NULL, + "DistanceGroup" tinyint DEFAULT NULL, + "CarrierDelay" decimal(8,2) DEFAULT NULL, + "WeatherDelay" decimal(8,2) DEFAULT NULL, + "NASDelay" decimal(8,2) DEFAULT NULL, + "SecurityDelay" decimal(8,2) DEFAULT NULL, + "LateAircraftDelay" decimal(8,2) DEFAULT NULL, + "FirstDepTime" varchar(10) DEFAULT NULL, + "TotalAddGTime" varchar(10) DEFAULT NULL, + "LongestAddGTime" varchar(10) DEFAULT NULL, + "DivAirportLandings" varchar(10) DEFAULT NULL, + "DivReachedDest" varchar(10) DEFAULT NULL, + "DivActualElapsedTime" varchar(10) DEFAULT NULL, + "DivArrDelay" varchar(10) DEFAULT NULL, + "DivDistance" varchar(10) DEFAULT NULL, + "Div1Airport" varchar(10) DEFAULT NULL, + "Div1AiportID" integer, + "Div1AirportSeqID" integer, + "Div1WheelsOn" varchar(10) DEFAULT NULL, + "Div1TotalGTime" varchar(10) DEFAULT NULL, + "Div1LongestGTime" varchar(10) DEFAULT NULL, + "Div1WheelsOff" varchar(10) DEFAULT NULL, + "Div1TailNum" varchar(10) DEFAULT NULL, + "Div2Airport" varchar(10) DEFAULT NULL, + "Div2AirportID" integer, + "Div2AirportSeqID" integer, + "Div2WheelsOn" varchar(10) DEFAULT NULL, + "Div2TotalGTime" varchar(10) DEFAULT NULL, + "Div2LongestGTime" varchar(10) DEFAULT NULL, + "Div2WheelsOff" varchar(10) DEFAULT NULL, + "Div2TailNum" varchar(10) DEFAULT NULL, + "Div3Airport" varchar(10) DEFAULT NULL, + "Div3AirportID" integer, + "Div3AirportSeqID" integer, + "Div3WheelsOn" varchar(10) DEFAULT NULL, + "Div3TotalGTime" varchar(10) DEFAULT NULL, + "Div3LongestGTime" varchar(10) DEFAULT NULL, + "Div3WheelsOff" varchar(10) DEFAULT NULL, + "Div3TailNum" varchar(10) DEFAULT NULL, + "Div4Airport" varchar(10) DEFAULT NULL, + "Div4AirportID" integer, + "Div4AirportSeqID" integer, + "Div4WheelsOn" varchar(10) DEFAULT NULL, + "Div4TotalGTime" varchar(10) DEFAULT NULL, + "Div4LongestGTime" varchar(10) DEFAULT NULL, + "Div4WheelsOff" varchar(10) DEFAULT NULL, + "Div4TailNum" varchar(10) DEFAULT NULL, + "Div5Airport" varchar(10) DEFAULT NULL, + "Div5AirportID" integer, + "Div5AirportSeqID" integer, + "Div5WheelsOn" varchar(10) DEFAULT NULL, + "Div5TotalGTime" varchar(10) DEFAULT NULL, + "Div5LongestGTime" varchar(10) DEFAULT NULL, + "Div5WheelsOff" varchar(10) DEFAULT NULL, + "Div5TailNum" varchar(10) DEFAULT NULL + ) ; + + +copy into ontime from '/data24r1/hannes/ontime/10m.csv' using delimiters ',','\n','"' NULL AS '' LOCKED; + +