-- -- Filename : point_temporal.sql -- Author : John Haasbeek, ENVIRON International Corp. -- Version : 0.9 -- Description: -- -- This file contains the stored procedures for the point source temporal allocation process, -- including: -- -- PointTemporalAllocation(runStartDate, runNumDays, runTimeZone) -- This is the main procedure that does the temporal allocations. It calls the other -- functions included herein - they are not intended to be called directly. Inputs are: -- -- runStartDate The starting date for the run. The run will start at 00:00 on this date -- in the timezone zone indicated. -- -- runNumDays The number of consecutive days to run the model for. The model run will -- end at 00:00 runNumDays + 1 days after the start. -- -- runTimeZone The timezone zone for the model run. All dates/times reported in the EM records -- will be evaluated relative to this timezone zone. -- -- -- PointTemporal_CreateTempTables() -- This function creates the temporary tables required for the temporal allocation process. -- -- -- The temporal allocation is the first step in the point source model, and occurs after data import and -- QA. The primary output from this function is contained in the table -- point_temporal, and consists of emissions by source/pollutant for each hour of the model run. -- The temporal allocation process is executed in three steps: -- -- Step 1: Setup. This step prepares global variables and temporary tables for the run. -- -- Step 2: Retrieve Profiles. In this step, all potentially applicable emissions records -- are identified and available profile information is retrieved. The results of -- this step are saved in a temporary table tmp_point_profiles. At this point, the -- emissions data have not been analyzed by model day/hour. -- -- Step 3: Hourly Emissions. The candidate emissions records are analyzed for each model -- hour to select the best fit record and calculate the hourly allocation of -- the emissions total to the current model hour. This is done in two parts, -- the first of which selects records based on the period and shift values and -- saves them to tmp_point_em_records. The second part allocates the emissions -- to each hour and saves them to the final output table. -- -- Since it steps through the data for each hour of the model run, Step 3 is the most time-consuming -- in the process. The final record format is compressed with 24 hourly emission values per row. -- -- If the debug level is set to 2 or greater in the run control table, the second part of Step 3 will -- produce an additional table called point_temporal_debug. This table stores the detailed derivation -- of the emissions for each hour of the model run, including the profile data (the actual profile -- values as opposed to just the profile number which can be found in the tmp_point_profiles table), -- the factors that are calculated for the month, day of week, and hour of day temporal shifts, and -- other details. Generating this table adds to the database size and run time and is recommended -- only for subsets of the data when tracking a particular temporal allocation issue. -- -- Each step is documented in greater detail in the body of the code. -- -- General comment about dates, hours, and timestamps. The model assumes that the timestamp for a -- given model hour is at the beginning of the hour. Thus, for 01-01-2004 hour 1, the correct -- timestamp is 01-01-2004 00:00. Since a timestamp has zero width, the ending timestamp for that -- hour is the same as the starting timestamp for the next hour, 01-01-2004 01:00. We do not resolve -- timestamps to periods less than an hour. Readers are highly encouraged to examine the Date/Time -- Functions documentation in the PL/PgSQL manual. These routines make significant use of the -- date/time arithmetic and intrinsic functions provided by PL/PgSQL. -- -- Important note about table keys. The PointTemporal_PrepNEITables function creates integer keys -- that replace the lengthy multiple-column keys in the NEI tables. The keys are as follows: -- -- epKey Replaces country_code, state_county_fips, tribal_code, state_facility_id, emission_unit_id, -- and emission_process_id -- emKey Replaces country_code, state_county_fips, tribal_code, state_facility_id, emission_unit_id, -- emission_process_id, emission_point_id, and pollutant_code -- -- These keys are used to construct the temporary tables and the final output. They are added to -- the NEI tables so the user can join the original tables to review additional fields not included -- in the output table. -- -- Certain important items are demarcated in the code with the following tags: -- -- An item that requires additional attention. -- Notes code that is only correct for the given assumption -- Marks values that are hard-coded and could possibly be moved to a configuration file -- Denotes code logic that differs from the EMS SAS code on which this function is based -- -- Change Log: -- -- 2005-11-11 JFH: When calculating monthFactor, there is a possibility of having the -- monthlyProfileSum equal to zero which produces a divide by zero error. -- The problem occurs where the monthly activities in an otherwise valid -- profile happen to all be zero for the emission period. The correct -- answer is that there should be zero emissions for the period, so the -- code was changed to assign a monthFactor of zero. -- SET SEARCH_PATH=globals; CREATE OR REPLACE FUNCTION PointTemporalAllocation() RETURNS INTEGER AS ' DECLARE -- Input variables. runStartDate DATE; runNumDays INTEGER; runTimeZone VARCHAR(6); debugLevel INTEGER; -- Model run time (local to run timezone zone). runTzOffset INTEGER; currentDay DATE; currentDayNum INTEGER; currentHour INTEGER; currentTime TIMESTAMP; -- Model run time (GMT). runStartGmt TIMESTAMP; runEndGmt TIMESTAMP; currentTimeGmt TIMESTAMP; -- Emission record dates/times (local at source timezone zone or GMT as noted). emStartLocal TIMESTAMP; emStartGmt TIMESTAMP; emEndLocal TIMESTAMP; emEndGmt TIMESTAMP; -- Length of emission record period. periodSeconds INTEGER; -- Indicates if emission record period overlaps period of interest (model day or model hour). isCandidate BOOLEAN; -- The relative comparison between the emission reported and the model day as calculated by -- the GetTemporalShift function. See comments in code body for detailed explanation. shiftValue INTEGER; -- Operating parameters that may be used to calculate profiles. weeksPerPeriod INTEGER; hoursPerPeriod INTEGER; hoursPerWeek NUMERIC; -- Profile numbers (codes) retrieved from EP, PE, or RPO TS records. monthlyProfileNum INTEGER; weeklyProfileNum INTEGER; weekdayProfileNum INTEGER; weekendProfileNum INTEGER; -- Codes that indicate where the profile came from. Values are: -- UN unknown -- QT Calculated from quarterly operating data -- PE NEI PE record -- EP NEI EP record -- CP Calculated from the operating data on the NEI EP record -- RP RPO default profiles -- DF Default profile assigned -- FL Flat profile assigned monthlyProfileSrc VARCHAR(2); weeklyProfileSrc VARCHAR(2); weekdayProfileSrc VARCHAR(2); weekendProfileSrc VARCHAR(2); -- Quarterly operating data from the NEI EP record. winterPct INTEGER; springPct INTEGER; summerPct INTEGER; fallPct INTEGER; -- Variables used in normalizing the quarterly operating data. missingQtrs INTEGER; qtrlyProfile INTEGER[5]; -- Profile values. The last entry in each array is the normalizing factor. monthlyProfile INTEGER[13]; weeklyProfile INTEGER[8]; weekdayProfile INTEGER[25]; weekendProfile INTEGER[25]; dailyProfile INTEGER[25]; -- Sum of the profile values. These are used to check that the profiles are valid, -- despite what the normalizing factor might say. monthlySum INTEGER; weeklySum INTEGER; weekdaySum INTEGER; weekendSum INTEGER; -- Local timestamp, month number in year, day number in week, and hour number in day. These are set -- for each day/hour of the model run and are covnerted to the local timezone zone at each source. localTime TIMESTAMP; localMonth INTEGER; localDayWk INTEGER; localHour INTEGER; -- Staring month number in year and day number in month, ending month number in year and ending -- day number in month for the emissions record, local timezone at the source. emStartMonth INTEGER; emStartDay INTEGER; emStartHour INTEGER; emEndMonth INTEGER; emEndDay INTEGER; emEndHour INTEGER; -- Array with the number of days in each month of the year, adjusted for leap years. daysInMonth INTEGER[12]; -- Number of months (fractional) in the reported emission period. numMonths NUMERIC; -- Number of whole days in the reported emission period. numWholeDays NUMERIC; -- Sum of the monthly profile values for the months in the emission period. This value -- is adjusted for partial beginning and ending months. monthlyProfileSum NUMERIC; -- Fraction of first and last months of emission period. firstMonthFraction NUMERIC; lastMonthFraction NUMERIC; -- Fraction of first and last days of emission period. firstDayFraction NUMERIC; lastDayFraction NUMERIC; -- Number of days with activity in the weekly profile. numDaysInProfile INTEGER; -- For average weekday, average weekend day, and average day emissions, this variable -- holds the average profile value for the specified days (e.g., for an average weekend -- day emission, avgProfileValue is the average of the Saturday profile value and the -- Sunday profile value). avgProfileValue NUMERIC; -- The calculated factors that adjust an emission record from the reported day to the -- model day. The per day factor is used to apportion emissions on emission records of -- type ENTIRE PERIOD. The hour factor is the hourly allocation of emissions. monthFactor NUMERIC; perDayFactor NUMERIC; dayFactor NUMERIC; hourFactor NUMERIC; -- The calculated hourly emission value hourlyEmission NUMERIC; -- An array to hold the 24 hourly values prior to insertion to area_temporal emis FLOAT[24]; units VARCHAR(10)[24]; -- Temporary record holders for query loops. Prefix indicates type of record. emRow RECORD; peRow RECORD; epRow RECORD; tsRow RECORD; tmRow RECORD; twRow RECORD; tdRow RECORD; pfRow RECORD; tmpRow RECORD; epkRow RECORD; emkRow RECORD; -- Temporary variables with no specific meaning. tmpString TEXT; tmpInteger INTEGER; tmpTime TIME; tmpInterval INTERVAL; -- Temporary date variables used in determining if the current model day is in a leap year. y1 DATE; y2 DATE; -- Code execution timing variables. stepStart TIMESTAMP; partStart TIMESTAMP; executionTime INTERVAL; BEGIN ---------------------------------------- ---------------------------------------- -- Step 1: Set up the run. ---------------------------------------- ---------------------------------------- stepStart := timeofday()::TIMESTAMP; -- Read the run control table SELECT INTO runStartDate start_date FROM run_control; SELECT INTO runNumDays num_days FROM run_control; SELECT INTO runTimeZone time_zone FROM run_control; SELECT INTO debugLevel debug_level FROM run_control; IF (runStartDate = NULL) THEN RAISE EXCEPTION ''Error - Run start date not set in run control table''; END IF; IF (runNumDays = NULL) THEN RAISE EXCEPTION ''Error - Number of days to run not set in run control table''; END IF; IF ((runTimeZone = NULL) OR (runTimeZone = '''')) THEN RAISE EXCEPTION ''Error - Run time zone not set in run control table''; END IF; -- Establish the GMT offset of the run timezone runTzOffset := 0; SELECT INTO runTzOffset gmt_offset FROM time_zones WHERE time_zone = runTimeZone; -- Most time comparisons will be done in GMT. tmpInterval := TO_CHAR(runTzOffset, ''99'') || '' hours''; runStartGmt := runStartDate - tmpInterval; tmpInterval := TO_CHAR(runNumDays, ''999'') || '' days''; runEndGmt := runStartGmt + tmpInterval; -- Set up the temporary tables required for the temporal allocation. tmpInteger := PointTemporal_CreateTempTables(); -- Set up a log table for the run and record the run parameters. executionTime := elapsed_time(stepStart); RAISE NOTICE ''Step 1 Time: % '', executionTime; ---------------------------------------- ---------------------------------------- -- End Step 1. ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -- Step 2: For each EM record, see if the record may be a candidate for use in the run. -- If the EM record time period (adjusted to GMT) overlaps the run period (adjusted -- to GMT), then the record is a candidate. This does not guarantee that the record -- will be used since there may be other records with a shorter period that will be -- preferentially selected. However, we will not know which records will actually be -- used until we go through the run period hour by hour. It turns out to be more -- efficient to collect the profile information for some unnecesary records than to -- do it hour by hour. -- -- Once a record is identified as a candidate, search the PE, EP, and RPO Temporal -- cross-reference tables for profile information. Where necessary, use a flat profile. -- Each record requires a monthly profile, a weekly profile, and a daily profile, plus -- (if available) quarterly throughput percentages. Once the profile numbers are -- established, look up the actual profiles in the RPO profile tables. -- -- Candidate records are saved to the tmp_point_profiles table with all of the available -- profile information. -- -- Note - we also filter the nei_point_em records based on the run_control pollutant -- list in step 1 so that all subsequent steps will not include records for -- pollutants we do not want. ---------------------------------------- ---------------------------------------- stepStart := timeofday()::TIMESTAMP; -- Join the timezone zones table through the fips lookup table so we can convert all -- EM record times to GMT. -- There are no missing timezones. This is enforced in the QA code. -- Need to get timezone zone info for tribal codes. FOR emRow IN SELECT nei_point_em.*, gmt_offset FROM nei_point_em, fips, time_zones, run_pollutants WHERE time_zones.time_zone = fips.time_zone AND fips.country_code = nei_point_em.country_code AND fips.state_county_fips = nei_point_em.state_county_fips AND nei_point_em.pollutant_code = run_pollutants.pollutant_code LOOP -- We are going to need scc for the temporal profile search, but since it is not -- in the nei_point_em table we need to grab it. The easiest place is the -- point_ep_keys table. SELECT INTO epkRow * FROM point_ep_keys WHERE point_ep_keys.ep_key = emRow.ep_key; -- Convert to GMT. IF (emRow.start_time = 0) THEN tmpInterval := TO_CHAR(emRow.gmt_offset, ''99'') || '' hours''; emStartGmt := emRow.start_date - tmpInterval; emStartLocal := emRow.start_date::TIMESTAMP; -- Add one day since we assume the activity goes through the end of the day. emEndGmt := emRow.end_date - tmpInterval + INTERVAL ''1 day''; emEndLocal := emRow.end_date + INTERVAL ''1 day''; ELSE tmpInterval := TO_CHAR(emRow.gmt_offset, ''99'') || '' hours''; tmpString := TO_CHAR(emRow.start_time, ''9999''); tmpTime := SUBSTRING(tmpString FROM 1 FOR 2) || '':'' || SUBSTRING(tmpString FROM 3 FOR 2); emStartGmt := (emRow.start_date + tmpTime) - tmpInterval; emStartLocal := emRow.start_date + tmpTime; tmpString := TO_CHAR(emRow.end_time, ''9999''); tmpTime := SUBSTRING(tmpString FROM 1 FOR 2) || '':'' || SUBSTRING(tmpString FROM 3 FOR 2); -- Add one day since we assume the activity goes through the end of the day. emEndGmt := (emRow.end_date + tmpTime) - tmpInterval + INTERVAL ''1 day''; emEndLocal := emRow.end_date + tmpTime + INTERVAL ''1 day''; END IF; -- Calculate the length of the emission period. The EXTRACT(EPOCH FROM INTERVAL) function -- returns the number of seconds in the interval provided. periodSeconds := EXTRACT(EPOCH FROM (emEndGmt - emStartGmt)); -- This record is potentially applicable if the period overlaps the model run period. isCandidate := (emStartGmt, emEndGmt) OVERLAPS (runStartGmt, runEndGmt); -- If the record is a potential candidate, we are going to retrieve the profile information -- for it and save it to our temp table. IF (isCandidate) THEN -- default profile numbers to zero and sources to unknown winterPct := 0; springPct := 0; summerPct := 0; fallPct := 0; weeksPerPeriod := 0; hoursPerPeriod := 0; monthlyProfileNum := 0; monthlyProfileSrc := ''UN''; weeklyProfileNum := 0; weeklyProfileSrc := ''UN''; weekdayProfileNum := 0; weekdayProfileSrc := ''UN''; weekendProfileNum := 0; weekendProfileSrc := ''UN''; -- Just a note here. The variables in the EMS SAS code relate to what we have here as follows: -- -- These two are only used when calculating the weekly profile number based on the annual data -- prf_prwk -> weeksPerPeriod -- prf_prhr -> hoursPerPeriod -- -- These three are saved and used to ge tthe profiles from the RPO temporal profile tables -- prf_month -> monthlyProfileNum (profile of each month in the year) -- prf_week -> weeklyProfileNum (profile of each day of the week) -- prf_hour -> weekdayProfileNum (profile of each hour in a day) -- prf_hour -> weekendProfileNum (profile of each hour in a day) -- First preference for weekly and daily profile is the PE record. FOR peRow IN SELECT * FROM nei_point_pe WHERE ep_key = emRow.ep_key AND nei_point_pe.start_date = emRow.start_date AND nei_point_pe.end_date = emRow.end_date AND nei_point_pe.start_time = emRow.start_time AND nei_point_pe.end_time = emRow.end_time LIMIT 1 LOOP weeksPerPeriod := peRow.weeks_per_period; hoursPerPeriod := peRow.hours_per_period; weeklyProfileNum := peRow.days_per_week; weeklyProfileSrc := ''PE''; weekdayProfileNum := peRow.hours_per_day; weekdayProfileSrc := ''PE''; weekendProfileNum := peRow.hours_per_day; weekendProfileSrc := ''PE''; END LOOP; -- peRow -- Second preference for weekly and daily profile is the EP record. We must join the EP -- table anyway since we want the quarterly activity if available. FOR epRow IN SELECT * FROM nei_point_ep WHERE ep_key = emRow.ep_key LIMIT 1 LOOP IF (weeksPerPeriod = 0) THEN weeksPerPeriod := epRow.avg_weeks_per_year; END IF; IF (hoursPerPeriod = 0) THEN hoursPerPeriod := epRow.avg_hours_per_year; END IF; IF (weeklyProfileNum = 0) THEN weeklyProfileNum := epRow.avg_days_per_week; weeklyProfileSrc := ''EP''; END IF; IF (weekdayProfileNum = 0) THEN weekdayProfileNum := epRow.avg_hours_per_day; weekdayProfileSrc := ''EP''; END IF; IF (weekendProfileNum = 0) THEN weekendProfileNum := epRow.avg_hours_per_day; weekendProfileSrc := ''EP''; END IF; -- If the weekly profile was not provided, see if we can calculate one from the -- hours per period and weeks per period numbers. -- Number of days per week = 7 -- Number of hours of operation per day = 8 IF (weeklyProfileNum = 0) THEN IF (hoursPerPeriod != 0) THEN IF (weeksPerPeriod = 0) THEN weeksPerPeriod := periodSeconds / (60 * 60 * 24 * 7); END IF; hoursPerWeek := hoursPerPeriod / weeksPerPeriod; IF ((hoursPerWeek > 0) AND (hoursPerWeek <= 16)) THEN weeklyProfileNum := 2; weeklyProfileSrc := ''CP''; ELSIF ((hoursPerWeek > 16) AND (hoursPerWeek <= 24)) THEN weeklyProfileNum := 3; weeklyProfileSrc := ''CP''; ELSIF ((hoursPerWeek > 24) AND (hoursPerWeek <= 32)) THEN weeklyProfileNum := 4; weeklyProfileSrc := ''CP''; ELSIF ((hoursPerWeek > 32) AND (hoursPerWeek <= 40)) THEN weeklyProfileNum := 5; weeklyProfileSrc := ''CP''; ELSIF ((hoursPerWeek > 40) AND (hoursPerWeek <= 48)) THEN weeklyProfileNum := 6; weeklyProfileSrc := ''CP''; ELSIF (hoursPerWeek > 48) THEN weeklyProfileNum := 7; weeklyProfileSrc := ''CP''; END IF; END IF; END IF; -- Retain the quarterly throughput numbers. winterPct := epRow.winter_thruput_pct; springPct := epRow.spring_thruput_pct; summerPct := epRow.summer_thruput_pct; fallPct := epRow.fall_thruput_pct; END LOOP; -- epRow -- Capture the quarterly profile. Remember that the fifth element in the array is the normalizer (total). qtrlyProfile := ARRAY[winterPct, springPct, summerPct, fallPct, (winterPct + springPct + summerPct + fallPct)]; missingQtrs := 0; FOR i IN 1..4 LOOP IF (qtrlyProfile[i] = 0) THEN missingQtrs := missingQtrs + 1; END IF; END LOOP; -- i -- Normalize the quarters if all were reported. Remember that qtrlyProfile[5] is the array total. IF ((qtrlyProfile[5] > 100) OR ((qtrlyProfile[5] < 100) AND (missingQtrs = 0))) THEN --RAISE NOTICE ''Normalizing quarterly data for emKey %'', emRow.em_key; FOR i IN 1..4 LOOP qtrlyProfile[i] := ROUND(qtrlyProfile[i] / qtrlyProfile[5]); END LOOP; -- i qtrlyProfile[5] := 0; FOR i IN 1..4 LOOP qtrlyProfile[5] := qtrlyProfile[5] + qtrlyProfile[i]; END LOOP; -- i END IF; -- Fill in missing quarters if needed. IF ((qtrlyProfile[5] < 100) AND (missingQtrs > 0) AND (missingQtrs < 4)) THEN --RAISE NOTICE ''Filling in quarterly data for emKey %'', emRow.em_key; FOR i IN 1..4 LOOP IF (qtrlyProfile[i] = 0) THEN qtrlyProfile[i] := (100 - qtrlyProfile[5]) / missingQtrs; END IF; END LOOP; -- i FOR i IN 1..4 LOOP qtrlyProfile[5] := qtrlyProfile[5] + qtrlyProfile[i]; END LOOP; -- i END IF; -- Third preference for weekly and daily profiles is the RPO cross reference table. -- It is also the second preference for the monthly profile. -- See if we can find a match on both scc and state_county_fips. -- Include tribal code. -- Include start date and end date. FOR tsRow IN SELECT * FROM rpo_ts WHERE scc = epkRow.scc AND country_code = emRow.country_code AND state_county_fips = emRow.state_county_fips LOOP IF (UPPER(tsRow.profile_type) = ''MONTHLY'') THEN monthlyProfileNum := tsRow.profile_number; monthlyProfileSrc := ''RP''; ELSIF ((weeklyProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''WEEKLY'') ) THEN weeklyProfileNum := tsRow.profile_number; weeklyProfileSrc := ''RP''; ELSIF ((weekdayProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''DAILY'') AND (tsRow.weekday_type = ''27'') ) THEN weekdayProfileNum := tsRow.profile_number; weekdayProfileSrc := ''RP''; ELSIF ((weekendProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''DAILY'') AND (tsRow.weekday_type = ''28'') ) THEN weekendProfileNum := tsRow.profile_number; weekendProfileSrc := ''RP''; END IF; END LOOP; -- tsRow -- Only if we did not find a match on scc and state_county_fips, look for a match on scc only. -- US is the default country code. -- 00000 means no state_county_fips code provided. -- Include tribal code. -- Include start date and end date. IF ((monthlyProfileNum = 0) OR (weeklyProfileNum = 0) OR (weekdayProfileNum = 0) OR (weekendProfileNum = 0) ) THEN FOR tsRow IN SELECT * FROM rpo_ts WHERE scc = epkRow.scc AND country_code = ''US'' AND state_county_fips = ''00000'' LOOP IF ((monthlyProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''MONTHLY'')) THEN monthlyProfileNum := tsRow.profile_number; monthlyProfileSrc := ''RP''; ELSIF ((weeklyProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''WEEKLY'')) THEN weeklyProfileNum := tsRow.profile_number; weeklyProfileSrc := ''RP''; ELSIF ((weekdayProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''DAILY'') AND (tsRow.weekday_type = ''27'') ) THEN weekdayProfileNum := tsRow.profile_number; weekdayProfileSrc := ''RP''; ELSIF ((weekendProfileNum = 0) AND (UPPER(tsRow.profile_type) = ''DAILY'') AND (tsRow.weekday_type = ''28'') ) THEN weekendProfileNum := tsRow.profile_number; weekendProfileSrc := ''RP''; END IF; END LOOP; -- tsRow END IF; -- If the weekly or daily profile is still missing, assign a default value. -- Default profile numbers are 7 and 24 for weekly and daily, respectively. IF (weeklyProfileNum = 0) THEN weeklyProfileNum := 7; weeklyProfileSrc := ''DF''; END IF; IF (weekdayProfileNum = 0) THEN weekdayProfileNum := 24; weekdayProfileSrc := ''DF''; END IF; IF (weekendProfileNum = 0) THEN weekendProfileNum := 24; weekendProfileSrc := ''DF''; END IF; -- Retrieve the profile values from the RPO temporal profile tables. We are going to use -- arrays to do this (PostgreSQL conveniently stores and retrieves arrays in tables like -- scalar fields). -- First we need to reset the arrays to their default zero values. -- Remember that the last value in each array is the normalizer value! monthlyProfile := ARRAY[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]; weeklyProfile := ARRAY[0, 0, 0, 0, 0, 0, 0, 0]; weekdayProfile := ARRAY[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]; weekendProfile := ARRAY[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]; -- Create the monthly profile. Preference is to use the quarterly profile, if that is not -- available then use the RPO default profile. Remember that qtrlyProfile[5] is the array total. IF (qtrlyProfile[5] != 0) THEN -- NOTE that due to rounding in integer arithmetic, dividing by three here -- and keeping the normalizer at 100 would generally result in rounding losses. -- Therefore we have chosen to retain the existing quarterly numbers for each month -- in the quarter and increase the normalizer by a factor of 3 instead, which is -- mathematically identical. monthlyProfileSrc := ''QT''; monthlyProfile[12] := qtrlyProfile[1]; monthlyProfile[1] := qtrlyProfile[1]; monthlyProfile[2] := qtrlyProfile[1]; monthlyProfile[3] := qtrlyProfile[2]; monthlyProfile[4] := qtrlyProfile[2]; monthlyProfile[5] := qtrlyProfile[2]; monthlyProfile[6] := qtrlyProfile[3]; monthlyProfile[7] := qtrlyProfile[3]; monthlyProfile[8] := qtrlyProfile[3]; monthlyProfile[9] := qtrlyProfile[4]; monthlyProfile[10] := qtrlyProfile[4]; monthlyProfile[11] := qtrlyProfile[4]; monthlyProfile[13] := qtrlyProfile[5] * 3; ELSE FOR tmRow IN SELECT * FROM rpo_tm WHERE profile_number = monthlyProfileNum LOOP monthlyProfile[1] := tmRow.jan; monthlyProfile[2] := tmRow.feb; monthlyProfile[3] := tmRow.mar; monthlyProfile[4] := tmRow.apr; monthlyProfile[5] := tmRow.may; monthlyProfile[6] := tmRow.jun; monthlyProfile[7] := tmRow.jul; monthlyProfile[8] := tmRow.aug; monthlyProfile[9] := tmRow.sep; monthlyProfile[10] := tmRow.oct; monthlyProfile[11] := tmRow.nov; monthlyProfile[12] := tmRow.dec; monthlyProfile[13] := tmRow.norm; END LOOP; -- tmRow END IF; -- Retrieve the weekly rpo profile. FOR twRow IN SELECT * FROM rpo_tw WHERE profile_number = weeklyProfileNum LOOP weeklyProfile[1] := twRow.mon; weeklyProfile[2] := twRow.tue; weeklyProfile[3] := twRow.wed; weeklyProfile[4] := twRow.thu; weeklyProfile[5] := twRow.fri; weeklyProfile[6] := twRow.sat; weeklyProfile[7] := twRow.sun; weeklyProfile[8] := twRow.norm; END LOOP; -- twRow -- Retrieve the weekday rpo profile. FOR tdRow IN SELECT * FROM rpo_td WHERE profile_number = weekdayProfileNum LOOP weekdayProfile[1] := tdRow.hr01; weekdayProfile[2] := tdRow.hr02; weekdayProfile[3] := tdRow.hr03; weekdayProfile[4] := tdRow.hr04; weekdayProfile[5] := tdRow.hr05; weekdayProfile[6] := tdRow.hr06; weekdayProfile[7] := tdRow.hr07; weekdayProfile[8] := tdRow.hr08; weekdayProfile[9] := tdRow.hr09; weekdayProfile[10] := tdRow.hr10; weekdayProfile[11] := tdRow.hr11; weekdayProfile[12] := tdRow.hr12; weekdayProfile[13] := tdRow.hr13; weekdayProfile[14] := tdRow.hr14; weekdayProfile[15] := tdRow.hr15; weekdayProfile[16] := tdRow.hr16; weekdayProfile[17] := tdRow.hr17; weekdayProfile[18] := tdRow.hr18; weekdayProfile[19] := tdRow.hr19; weekdayProfile[20] := tdRow.hr20; weekdayProfile[21] := tdRow.hr21; weekdayProfile[22] := tdRow.hr22; weekdayProfile[23] := tdRow.hr23; weekdayProfile[24] := tdRow.hr24; weekdayProfile[25] := tdRow.norm; END LOOP; -- tdRow -- Retrieve the weekend rpo profile. FOR tdRow IN SELECT * FROM rpo_td WHERE profile_number = weekendProfileNum LOOP weekendProfile[1] := tdRow.hr01; weekendProfile[2] := tdRow.hr02; weekendProfile[3] := tdRow.hr03; weekendProfile[4] := tdRow.hr04; weekendProfile[5] := tdRow.hr05; weekendProfile[6] := tdRow.hr06; weekendProfile[7] := tdRow.hr07; weekendProfile[8] := tdRow.hr08; weekendProfile[9] := tdRow.hr09; weekendProfile[10] := tdRow.hr10; weekendProfile[11] := tdRow.hr11; weekendProfile[12] := tdRow.hr12; weekendProfile[13] := tdRow.hr13; weekendProfile[14] := tdRow.hr14; weekendProfile[15] := tdRow.hr15; weekendProfile[16] := tdRow.hr16; weekendProfile[17] := tdRow.hr17; weekendProfile[18] := tdRow.hr18; weekendProfile[19] := tdRow.hr19; weekendProfile[20] := tdRow.hr20; weekendProfile[21] := tdRow.hr21; weekendProfile[22] := tdRow.hr22; weekendProfile[23] := tdRow.hr23; weekendProfile[24] := tdRow.hr24; weekendProfile[25] := tdRow.norm; END LOOP; -- tdRow -- Before we save all of the data, check the profiles. If a profile is bad -- (i.e. the sum of the values is zero), make up a flat profile. -- We are not dropping bad weekly or daily profiles but replacing them -- with default flat profiles instead monthlySum := 0; weeklySum := 0; weekdaySum := 0; weekendSum := 0; FOR i IN 1..12 LOOP monthlySum := monthlySum + monthlyProfile[i]; END LOOP; -- i FOR i IN 1..7 LOOP weeklySum := weeklySum + weeklyProfile[i]; END LOOP; -- i FOR i IN 1..24 LOOP weekdaySum := weekdaySum + weekdayProfile[i]; END LOOP; -- i FOR i IN 1..24 LOOP weekendSum := weekendSum + weekendProfile[i]; END LOOP; -- i IF (monthlySum = 0) THEN --RAISE NOTICE ''Using flat monthly profile for emKey %'', emRow.em_key; monthlyProfileSrc := ''FL''; FOR i IN 1..12 LOOP monthlyProfile[i] := 1; END LOOP; -- i monthlyProfile[13] := 12; END IF; IF (weeklySum = 0) THEN --RAISE NOTICE ''Using flat weekly profile for emKey %'', emRow.em_key; weeklyProfileSrc := ''FL''; FOR i IN 1..7 LOOP weeklyProfile[i] := 1; END LOOP; -- i weeklyProfile[8] := 7; END IF; IF (weekdaySum = 0) THEN --RAISE NOTICE ''Using flat weekday profile for emKey %'', emRow.em_key; weekdayProfileSrc := ''FL''; FOR i IN 1..24 LOOP weekdayProfile[i] := 1; END LOOP; -- i weekdayProfile[25] := 24; END IF; IF (weekendSum = 0) THEN --RAISE NOTICE ''Using flat weekend profile for emKey %'', emRow.em_key; weekendProfileSrc := ''FL''; FOR i IN 1..24 LOOP weekendProfile[i] := 1; END LOOP; -- i weekendProfile[25] := 24; END IF; -- Finally, save all the profile information to the temporal profiles temp table. -- Note the syntax for saving arrays to a table in PostgreSQL (just treat them like fields). INSERT INTO tmp_point_profiles (ep_key, em_key, start_date, end_date, start_time, end_time, emission_value, emission_units_code, emission_type, start_local, end_local, start_gmt, end_gmt, gmt_offset, period_seconds, winter_pct, spring_pct, summer_pct, fall_pct, quarterly_profile, monthly_profile_num, monthly_profile_src, monthly_profile, weekly_profile_num, weekly_profile_src, weekly_profile, weekday_profile_num, weekday_profile_src, weekday_profile, weekend_profile_num, weekend_profile_src, weekend_profile) VALUES (emRow.ep_key, emRow.em_key, emRow.start_date, emRow.end_date, emRow.start_time, emRow.end_time, emRow.emission_value, emRow.emission_units_code, emRow.emission_type, emStartLocal, emEndLocal, emStartGmt, emEndGmt, emRow.gmt_offset, periodSeconds, winterPct, springPct, summerPct, fallPct, qtrlyProfile, monthlyProfileNum, monthlyProfileSrc, monthlyProfile, weeklyProfileNum, weeklyProfileSrc, weeklyProfile, weekdayProfileNum, weekdayProfileSrc, weekdayProfile, weekendProfileNum, weekendProfileSrc, weekendProfile); ELSE INSERT INTO tmp_point_dropped_temporal VALUES (emRow.em_key, ''Outside model run time range''); END IF; -- isCandidate END LOOP; -- emRow executionTime := elapsed_time(stepStart); RAISE NOTICE ''Step 2 Time: % '', executionTime; ---------------------------------------- ---------------------------------------- -- End Step 2. ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -- Step 3: Determine, for each candidate EM record found in Step 2, which hours -- in the model run it will contribute to. When there is more than one -- EM record that may apply to a given hour, the record with the shortest -- period and smallest shift (explained below) is selected. Thus the -- selection is a two-step process - first determine the period and shift -- for all of the candidates, then select the shortest period and smallest -- shift for each combination of country/state/tribe, process/stack, and pollutant. -- -- The shift is determined based on the emission type reported compared to -- the model day being evaluated. For example, if the current hour is on a -- Wednesday, the EM record preference is an Average Wednesday. If that is -- not available, pick an Average Weekday, and so forth. The details of the -- logic are documented in the code below. -- -- -- Note that this approach is slightly different that was originally implemented -- in EMS. This algorithm includes timezone zone differences when evaluating the -- shift hour-by-hour, whereas the original code did not. For example, -- consider a source in California contributing to a model run for Eastern -- Standard Time. The first three hours of each day (EST) are actually the -- prior day at the source (PST). During those first three hours, the EM -- record representing the day at the source should be selected, not the -- day of the model run timezone. So for a model run for a Wednesday (EST), the -- California source EM records for the first three hours should preferentially -- be Average Tuesday records. -- -- -- In the first part of this step, the period and shift values are saved by -- em_key and run day/hour to the tmp_point_em_records table. The second part of -- this step selects the records with the smallest period and shift, calculates -- the hourly emissions, and saves them to the final output table point_temporal. ---------------------------------------- ---------------------------------------- stepStart := timeofday()::TIMESTAMP; -- Part 1: Determine the applicability of the EM periods and the shift values for each hour -- of the model run. partStart := timeofday()::TIMESTAMP; -- Loop over each candidate EM record. FOR pfRow IN SELECT * FROM tmp_point_profiles LOOP -- Loop over each day in the model run. FOR currentDayNum IN 1..runNumDays LOOP -- Find the current date (run timezone zone) currentDay := runStartDate + (currentDayNum - 1); -- Loop over each hour in the day. FOR currentHour IN 1..24 LOOP -- Find the current timestamp (GMT). 1 is subtracted from current hour because all -- hourly timestamps are at the beginning of the hour (so hour 1 is 00:00, hour 2 is -- 01:00, etc). tmpInterval := TO_CHAR(currentHour - 1 - runTzOffset, ''99'') || '' hours''; currentTimeGmt := currentDay + tmpInterval; -- Determine if the emissions activity period overlaps this specific hour. IF ((pfRow.start_gmt <= currentTimeGmt) AND (pfRow.end_gmt >= currentTimeGmt)) THEN -- Calculate the shift value. This value is set as follows: -- 1 if emission type is a specific average day and is the same day as the current day. -- 2 if emission type is average weekday and current day is a weekday -- 2 if emission type is average weekend day and current day is a weekend day -- 3 if emission type is average day -- 4 if emission type is average weekday and current day is a weekend day -- 4 if emission type is average weekend day and current day is a weekday -- 5 if emission type is entire period -- NOTE - the evaluation is done using local time which is why the model timezone zone -- offset and the source timezone zone offset are required. shiftValue := GetTemporalShift(currentDay, currentHour, runTzOffset, pfRow.gmt_offset, pfRow.emission_type); INSERT INTO tmp_point_em_records (ep_key, em_key, start_date, end_date, start_time, end_time, emission_type, day, hour, period_seconds, shift_value, use_flag) VALUES (pfRow.ep_key, pfRow.em_key, pfRow.start_date, pfRow.end_date, pfRow.start_time, pfRow.end_time, pfRow.emission_type, currentDay, currentHour, periodSeconds, shiftValue, 0); END IF; END LOOP; -- currentHour END LOOP; -- currentDayNum END LOOP; -- pfRow executionTime := elapsed_time(partStart); RAISE NOTICE ''Step 3 Part 1 Time: % '', executionTime; -- Create needed indexes. partStart := timeofday()::TIMESTAMP; CREATE INDEX tmp_point_profiles_indx1 ON tmp_point_profiles (em_key, start_date, end_date, start_time, end_time, emission_type); CREATE INDEX tmp_point_em_records_indx1 ON tmp_point_em_records (day, hour, em_key, period_seconds, shift_value); executionTime := elapsed_time(partStart); RAISE NOTICE ''Step 3 Index Time : % '', executionTime; -- Part 2: Sort the records by period and shift and pick the smallest period/shift. Then allocate the -- emissions from that record to each model hour. partStart := timeofday()::TIMESTAMP; -- Later we will need to know the number of days in each month, adjusted for leap years. daysInMonth := ARRAY[31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]; tmpInteger := EXTRACT(YEAR FROM currentTime); tmpString := ''01-01-'' || TRIM(TO_CHAR(tmpInteger + 1, ''9999'')); y1 = tmpString::DATE; tmpString := ''01-01-'' || TRIM(TO_CHAR(tmpInteger, ''9999'')); y2 = tmpString::DATE; tmpInteger = y1 - y2; IF (tmpInteger = 366) THEN daysInMonth[2] := 29; END IF; -- Set up the arrays for keeping the 24 hourly emissions and units values. emis := ARRAY[0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]; units := ARRAY['''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''']; dailyProfile := ARRAY[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]; -- The first loop simply picks the unique emission/day/hour records. Each group of these records -- forms the candidate list to be sorted by period and shift. Only one of the records in each -- group will be selected. FOR tmpRow IN SELECT DISTINCT em_key FROM tmp_point_em_records LOOP -- Now loop over the days/hours for this em_key. FOR currentDayNum IN 1..runNumDays LOOP -- Find the current date (run timezone zone). currentDay := runStartDate + (currentDayNum - 1); -- Loop over each hour in the day. FOR currentHour IN 1..24 LOOP -- Note the LIMIT syntax below which will result in only the first record being selected. -- Since we sort by period and shift ascending, this will result in the record with the -- lowest period (lowest shift for tied periods) being used. SELECT INTO emRow * FROM tmp_point_em_records WHERE day = currentDay AND hour = currentHour AND em_key = tmpRow.em_key ORDER BY period_seconds ASC, shift_value ASC LIMIT 1; -- Later on, we will need the ep_key and em_key information to insert into the area_temporal table. SELECT INTO epkRow * FROM point_ep_keys WHERE ep_key = emRow.ep_key; SELECT INTO emkRow * FROM point_em_keys WHERE em_key = emRow.em_key; -- Determine the run timestamp (model timezone zone) for the record. tmpInterval := TO_CHAR(emRow.hour - 1, ''99'') || '' hours''; currentTime := emRow.day + tmpInterval; -- Now retrieve the profile information for this record. SELECT INTO pfRow * FROM tmp_point_profiles WHERE em_key = emRow.em_key AND start_date = emRow.start_date AND end_date = emRow.end_date AND start_time = emRow.start_time AND end_time = emRow.end_time AND emission_type = emRow.emission_type; -- We are going to need to apply factors to the EM record emission to reflect the -- month/day/hour we are currently modeling. These are the LOCAL month, day and hour -- at the current source location so we have to convert them. The EM record start/end -- dates and times are already local time. -- -- The interval adjustment will be added to the run day, and consists of the run hour -- (minus one because the time is at the beginning of the hour) minus the run time -- zone offset (brings the time back to GMT) plus the source timezone zone offset (brings -- the time back to the local time at the source). tmpInterval := TO_CHAR(emRow.hour - 1 - runTzOffset + pfRow.gmt_offset, ''99'') || '' hours''; localTime := emRow.day + tmpInterval; localMonth := EXTRACT(MONTH FROM localTime); localDayWk := EXTRACT(DOW FROM localTime); -- EXTRACT returns 0 (sun) - 6 (sat) localHour := EXTRACT(HOUR FROM localTime) + 1; -- EXTRACT returns 0-23 IF (localDayWk = 0) THEN localDayWk := 7; END IF; -- The emission start and end parameters are all LOCAL time. emStartMonth := EXTRACT(MONTH FROM pfRow.start_date); emStartDay := EXTRACT(DAY FROM pfRow.start_date); emStartHour := EXTRACT(HOUR FROM pfRow.start_local) + 1; -- EXTRACT returns 0-23 emEndMonth := EXTRACT(MONTH FROM pfRow.end_date); emEndDay := EXTRACT(DAY FROM pfRow.end_date); emEndHour := EXTRACT(HOUR FROM (pfRow.end_local - INTERVAL ''1 hour'')) + 1; -- EXTRACT returns 0-23 -- Calculate the month factor. The month factor reflects the relative intensity of emissions -- on an average day in the current month versus other months in the year. It is based on the -- monthly profile value for the current month, adjusted if the emissions period does not -- include the entire month, and normalized across the entire emissions period: -- -- month factor = monthly profile value for current month -- * fraction of current month covered by emission period -- / sum of monthly profile values for emissions period -- monthlyProfile := pfRow.monthly_profile; IF (emStartMonth = emEndMonth) THEN -- If the emissions period is one month or less there is no adjustment. monthFactor := 1.0; ELSE -- These fractions reflect the portion of the start and end months covered by the -- emissions period. firstMonthFraction := (daysInMonth[emStartMonth] - emStartDay + 1)::NUMERIC / daysInMonth[emStartMonth]::NUMERIC; lastMonthFraction := emEndDay::NUMERIC / daysInMonth[emEndMonth]::NUMERIC; -- Add up the monthly profile values for the emissions period monthlyProfileSum := 0.0; FOR i IN emStartMonth..emEndMonth LOOP IF (i = emStartMonth) THEN monthlyProfileSum := monthlyProfileSum + (monthlyProfile[i] * firstMonthFraction); ELSIF (i = emEndMonth) THEN monthlyProfileSum := monthlyProfileSum + (monthlyProfile[i] * lastMonthFraction); ELSE monthlyProfileSum := monthlyProfileSum + monthlyProfile[i]; END IF; END LOOP; -- i -- 2005-11-11 JFH -- We were seeing divide by zero errors below in calculating the monthFactor when -- there was a valid profile but the profile values for the current emission -- period happened to all be zero. For example, calculating the monthFactor -- for summer on a source that has zero activity in summer. -- IF (monthlyProfileSum = 0.0) THEN monthFactor := 0.0; ELSE -- Determine the number (fractional) of months in the emissions period. Start with the total -- number of months (end - start + 1) then subtract 2 for the fist and last month, then -- add the fractional parts. numMonths := (emEndMonth - emStartMonth + 1) - 2 + (firstMonthFraction + lastMonthFraction); -- Divide the monthly profile value by the sum and multiply by the number of months -- to get the final adjustment factor. IF (localMonth = emStartMonth) THEN monthFactor := monthlyProfile[localMonth] * firstMonthFraction * numMonths / monthlyProfileSum; ELSIF (localMonth = emEndMonth) THEN monthFactor := monthlyProfile[localMonth] * lastMonthFraction * numMonths / monthlyProfileSum; ELSE monthFactor := monthlyProfile[localMonth] * numMonths / monthlyProfileSum; END IF; END IF; -- END IF; -- Calculate the day factor. The day factor reflects the relative intensity of emissions -- on the day being modeled compared to the day reported in the emissions record. The -- calculation is dependent on the type of emissions record, but generally takes the form: -- -- dayFactor = factor for model day / factor for reported day -- -- The factors for the model day and the reported day are calculated in a manner similar -- to the month factor: -- -- factor = profile value * period length / sum of factors for period -- -- In the case of average weekday, average weekend day, and average day, the factors are -- averaged for all of the days in the average (e.g., mon-fri for average weekday). -- weeklyProfile := pfRow.weekly_profile; dayFactor := 1.0; IF (pfRow.emission_type = ''20'') THEN -- Average Sunday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[7]; ELSIF (pfRow.emission_type = ''21'') THEN -- Average Monday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[1]; ELSIF (pfRow.emission_type = ''22'') THEN -- Average Tuesday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[2]; ELSIF (pfRow.emission_type = ''23'') THEN -- Average Wednesday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[3]; ELSIF (pfRow.emission_type = ''24'') THEN -- Average Thursday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[4]; ELSIF (pfRow.emission_type = ''25'') THEN -- Average Friday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[5]; ELSIF (pfRow.emission_type = ''26'') THEN -- Average Saturday dayFactor := weeklyProfile[localDayWk] / weeklyProfile[6]; ELSIF (pfRow.emission_type = ''27'') THEN -- Average Weekday avgProfileValue := ( weeklyProfile[1] + weeklyProfile[2] + weeklyProfile[3] + weeklyProfile[4] + weeklyProfile[5] ) / 5; dayFactor := weeklyProfile[localDayWk] / avgProfileValue; ELSIF (pfRow.emission_type = ''28'') THEN -- Average Weekend Day avgProfileValue := ( weeklyProfile[6] + weeklyProfile[7] ) / 2; dayFactor := weeklyProfile[localDayWk] / avgProfileValue; ELSIF (pfRow.emission_type = ''29'') THEN -- Average Day avgProfileValue := ( weeklyProfile[1] + weeklyProfile[2] + weeklyProfile[3] + weeklyProfile[4] + weeklyProfile[5] + weeklyProfile[6] + weeklyProfile[7] ) / 7; dayFactor := weeklyProfile[localDayWk] / avgProfileValue; ELSIF (pfRow.emission_type = ''30'') THEN -- Entire Period -- My guess here is that the month factor applies as with any other emission type, -- but that we cannot apply a day factor ratio since the reported emission is not for -- any particular day or day type. However, the emission value is not a per-day total, -- but represents the total emissions for the period. Thus we will need to divide -- the emissions total by the number of days in the period to get the emissions on -- a per-day basis. We can then apply the weekly profile to adjust the daily emission. -- -- I shall assume that the emissions for the period are spread evenly -- across the period, and that the period is not necessarily an even number of days. -- If the period is one day or less, the daily total is equal to the reported -- emission value. If the period is more than one day, the reported emission value -- should be apportioned to each day and/or fraction of a day. IF (emRow.start_date = emRow.end_date) THEN perDayFactor := 1.0; ELSE firstDayFraction := (24.0 - emStartHour + 1.0) / 24.0; lastDayFraction := emEndHour / 24.0; numWholeDays := emRow.end_date - emRow.start_date - 1; IF (localTime::DATE = emRow.start_date) THEN perDayFactor := firstDayFraction / (numWholeDays + firstDayFraction + lastDayFraction); ELSIF (localTime::DATE = emRow.end_date) THEN perDayFactor := lastDayFraction / (numWholeDays + firstDayFraction + lastDayFraction); ELSE perDayFactor := 1.0 / (numWholeDays + firstDayFraction + lastDayFraction); END IF; END IF; numDaysInProfile := 0; FOR i in 1..7 LOOP IF (weeklyProfile[i] > 0) THEN numDaysInProfile := numDaysInProfile + 1; END IF; END LOOP; -- i dayFactor := perDayFactor * weeklyProfile[localDayWk] * numDaysInProfile / weeklyProfile[8]; END IF; -- The allocation for this hour is obtained from the daily profile. Which profile we -- should use (weekday or weekend) depends on the day type LOCAL at the source. IF ((localDayWk = 6) OR (localDayWk = 7)) THEN dailyProfile := pfRow.weekend_profile; ELSE dailyProfile := pfRow.weekday_profile; END IF; -- Remember that dailyProfile[25] is the normalization factor. The hour that indexes -- the profile value is the LOCAL hour at the source. hourFactor := dailyProfile[localHour]::NUMERIC / dailyProfile[25]::NUMERIC; hourlyEmission := pfRow.emission_value * monthFactor * dayFactor * hourFactor; -- Add the emission value and units to our arrays to be written to the area_temporal -- table after the hour loop is done. emis[currentHour] := hourlyEmission; units[currentHour] := pfRow.emission_units_code; -- Save the results and all the info to determine how they were derived. IF (debugLevel > 1) THEN INSERT INTO point_temporal_debug (ep_key, em_key, country_code, state_county_fips, tribal_code, state_facility_id, emission_unit_id, emission_process_id, emission_point_id, scc, pollutant_code, run_day, run_hour, run_time, local_time, em_start_time, em_end_time, em_start_month, em_start_day, em_start_hour, em_end_month, em_end_day, em_end_hour, first_month_fraction, last_month_fraction, num_months, monthly_profile, month_factor, emission_type, weekly_profile, num_whole_days, num_days_in_profile, first_day_fraction, last_day_fraction, per_day_factor, day_factor, daily_profile, hour_factor, emission_total, emission_value, emission_units_code) VALUES (pfRow.ep_key, pfRow.em_key, epkRow.country_code, epkRow.state_county_fips, epkRow.tribal_code, epkRow.state_facility_id, epkRow.emission_unit_id, epkRow.emission_process_id, emkRow.emission_point_id, epkRow.scc, emkRow.pollutant_code, emRow.day, emRow.hour, currentTime, localTime, emStartLocal, emEndLocal, emStartMonth, emStartDay, emStartHour, emEndMonth, emEndDay, emEndHour, firstMonthFraction, lastMonthFraction, numMonths, monthlyProfile, monthFactor, pfRow.emission_type, weeklyProfile, numWholeDays, numDaysInProfile, firstDayFraction, lastDayFraction, perDayFactor, dayFactor, dailyProfile, hourFactor, pfRow.emission_value, hourlyEmission, pfRow.emission_units_code); END IF; END LOOP; -- currentHour -- We have collected the 24 hourly values for this day/em_key, so insert them to the -- area_temporal table. INSERT INTO point_temporal (ep_key, em_key, country_code, state_county_fips, tribal_code, state_facility_id, emission_unit_id, emission_process_id, emission_point_id, scc, pollutant_code, run_day, run_hour, emis_01, emis_02, emis_03, emis_04, emis_05, emis_06, emis_07, emis_08, emis_09, emis_10, emis_11, emis_12, emis_13, emis_14, emis_15, emis_16, emis_17, emis_18, emis_19, emis_20, emis_21, emis_22, emis_23, emis_24, units_01, units_02, units_03, units_04, units_05, units_06, units_07, units_08, units_09, units_10, units_11, units_12, units_13, units_14, units_15, units_16, units_17, units_18, units_19, units_20, units_21, units_22, units_23, units_24) VALUES (emRow.ep_key, emRow.em_key, emkRow.country_code, emkRow.state_county_fips, emkRow.tribal_code, emkRow.state_facility_id, emkRow.emission_unit_id, emkRow.emission_process_id, emkRow.emission_point_id, epkRow.scc, emkRow.pollutant_code, emRow.day, emRow.hour, emis[1], emis[2], emis[3], emis[4], emis[5], emis[6], emis[7], emis[8], emis[9], emis[10], emis[11], emis[12], emis[13], emis[14], emis[15], emis[16], emis[17], emis[18], emis[19], emis[20], emis[21], emis[22], emis[23], emis[24], units[1], units[2], units[3], units[4], units[5], units[6], units[7], units[8], units[9], units[10], units[11], units[12], units[13], units[14], units[15], units[16], units[17], units[18], units[19], units[20], units[21], units[22], units[23], units[24]); END LOOP; -- currentDay END LOOP; -- tmpRow executionTime := elapsed_time(partStart); RAISE NOTICE ''Step 3 Part 2 Time: % '', executionTime; executionTime := elapsed_time(stepStart); RAISE NOTICE ''Step 3 Time: % '', executionTime; ---------------------------------------- ---------------------------------------- -- End Step 3. ---------------------------------------- ---------------------------------------- stepStart := timeofday()::TIMESTAMP; -- This index is used in a select distinct in the speciation module. CREATE INDEX point_temporal_indx1 ON point_temporal(em_key, country_code, state_county_fips, tribal_code, scc, pollutant_code); -- These indexes are used in the output reports CREATE INDEX point_temporal_indx2 ON point_temporal(country_code, state_county_fips, tribal_code, pollutant_code, run_day, run_hour); CREATE INDEX point_temporal_indx3 ON point_temporal(pollutant_code); executionTime := elapsed_time(stepStart); RAISE NOTICE ''Index Time: % '', executionTime; -- Return the number of records generated. SELECT INTO tmpInteger COUNT(*) FROM point_temporal; RETURN tmpInteger; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION PointTemporal_CreateTempTables() RETURNS INTEGER AS ' BEGIN -- Table tmp_point_dropped_temporal lists dropped emissions in this module IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = ''tmp_point_dropped_temporal'') > 0) THEN DROP TABLE tmp_point_dropped_temporal; END IF; CREATE TABLE tmp_point_dropped_temporal ( em_key INTEGER, reason VARCHAR(400) ); -- Table tmp_point_profiles will hold the temporal profile information -- for each EM record applicable to the run period IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = ''tmp_point_profiles'') > 0) THEN DROP TABLE tmp_point_profiles; END IF; CREATE TABLE tmp_point_profiles ( ep_key INTEGER, em_key INTEGER, start_date DATE, end_date DATE, start_time INTEGER, end_time INTEGER, emission_value NUMERIC, emission_units_code VARCHAR(10), emission_type VARCHAR(2), start_local TIMESTAMP, end_local TIMESTAMP, start_gmt TIMESTAMP, end_gmt TIMESTAMP, gmt_offset INTEGER, period_seconds INTEGER, winter_pct NUMERIC, spring_pct NUMERIC, summer_pct NUMERIC, fall_pct NUMERIC, quarterly_profile INTEGER[], monthly_profile_num INTEGER, monthly_profile_src VARCHAR(2), monthly_profile INTEGER[], weekly_profile_num INTEGER, weekly_profile_src VARCHAR(2), weekly_profile INTEGER[], weekday_profile_num INTEGER, weekday_profile_src VARCHAR(2), weekday_profile INTEGER[], weekend_profile_num INTEGER, weekend_profile_src VARCHAR(2), weekend_profile INTEGER[] ); -- Table tmp_point_em_records will hold the keys for the candidate EM records -- plus the period and shift values. This table is used to sort the records -- and pick one for each unique combination of country/state/county/tribe, -- process/stack, and pollutant. IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = ''tmp_point_em_records'') > 0) THEN DROP TABLE tmp_point_em_records; END IF; CREATE TABLE tmp_point_em_records ( ep_key INTEGER, em_key INTEGER, start_date DATE, end_date DATE, start_time INTEGER, end_time INTEGER, emission_type VARCHAR(2), day DATE, hour INTEGER, period_seconds INTEGER, shift_value INTEGER, use_flag INTEGER ); IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = ''point_temporal_debug'') > 0) THEN DROP TABLE point_temporal_debug; END IF; CREATE TABLE point_temporal_debug ( ep_key INTEGER, em_key INTEGER, country_code VARCHAR(2), state_county_fips VARCHAR(5), tribal_code VARCHAR(4), state_facility_id VARCHAR(15), emission_unit_id VARCHAR(6), emission_process_id VARCHAR(6), emission_point_id VARCHAR(6), scc VARCHAR(10), pollutant_code VARCHAR(9), run_day DATE, run_hour INTEGER, run_time TIMESTAMP, local_time TIMESTAMP, em_start_time TIMESTAMP, em_end_time TIMESTAMP, em_start_month INTEGER, em_start_day INTEGER, em_start_hour INTEGER, em_end_month INTEGER, em_end_day INTEGER, em_end_hour INTEGER, first_month_fraction NUMERIC, last_month_fraction NUMERIC, num_months NUMERIC, monthly_profile INTEGER[], month_factor NUMERIC, emission_type VARCHAR(2), weekly_profile INTEGER[], num_whole_days INTEGER, num_days_in_profile INTEGER, first_day_fraction NUMERIC, last_day_fraction NUMERIC, per_day_factor NUMERIC, day_factor NUMERIC, daily_profile INTEGER[], hour_factor NUMERIC, emission_total NUMERIC, emission_value NUMERIC, emission_units_code VARCHAR(10) ); IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = ''point_temporal'') > 0) THEN DROP TABLE point_temporal; END IF; CREATE TABLE point_temporal ( ep_key INTEGER, em_key INTEGER, country_code VARCHAR(2), state_county_fips VARCHAR(5), tribal_code VARCHAR(4), state_facility_id VARCHAR(15), emission_unit_id VARCHAR(6), emission_process_id VARCHAR(6), emission_point_id VARCHAR(6), scc VARCHAR(10), pollutant_code VARCHAR(9), run_day DATE, run_hour INTEGER, emis_01 FLOAT, emis_02 FLOAT, emis_03 FLOAT, emis_04 FLOAT, emis_05 FLOAT, emis_06 FLOAT, emis_07 FLOAT, emis_08 FLOAT, emis_09 FLOAT, emis_10 FLOAT, emis_11 FLOAT, emis_12 FLOAT, emis_13 FLOAT, emis_14 FLOAT, emis_15 FLOAT, emis_16 FLOAT, emis_17 FLOAT, emis_18 FLOAT, emis_19 FLOAT, emis_20 FLOAT, emis_21 FLOAT, emis_22 FLOAT, emis_23 FLOAT, emis_24 FLOAT, units_01 VARCHAR(10), units_02 VARCHAR(10), units_03 VARCHAR(10), units_04 VARCHAR(10), units_05 VARCHAR(10), units_06 VARCHAR(10), units_07 VARCHAR(10), units_08 VARCHAR(10), units_09 VARCHAR(10), units_10 VARCHAR(10), units_11 VARCHAR(10), units_12 VARCHAR(10), units_13 VARCHAR(10), units_14 VARCHAR(10), units_15 VARCHAR(10), units_16 VARCHAR(10), units_17 VARCHAR(10), units_18 VARCHAR(10), units_19 VARCHAR(10), units_20 VARCHAR(10), units_21 VARCHAR(10), units_22 VARCHAR(10), units_23 VARCHAR(10), units_24 VARCHAR(10) ); RETURN 0; END; ' LANGUAGE plpgsql;