Bug: ERG requested changes for the four (reported) emissions API endpoints

[JanellC]

Context

Erg reported issues with the following endpoints: \

• hourly operating
• hourly derived
• emissions summary values
• emissions supplemental operating

See below for details

Communication from ERG

Each of the four (reported) emissions API endpoints still have issues.

Issue 1

https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/operating?orisCode=3&beginDate=2022-05-31&endDate=2022-07-01

Does not return the HRLY_OP_DATA.HOUR_ID as “id”.

The results include duplicate rows based on locationId, beginDate, and beginHour combinations. The format of the beginDate element is “YYYY/MM/DD HH24:mi:ss”. Based on the apportioned API, I believe the standard for a date without time is “YYYY-MM-DD”.

Issue 2

https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/operating?orisCode=3&locationName=1%7C2&beginDate=2022-05-31&endDate=2022-07-01

Seems to return location “1” or “2” for any ORIS code, not just ORIS code 3, and not limited to the date range.. i. The resulting volume of data might explain the issue with the Swagger page failing.

Issue 3

https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/derived-values?orisCode=3&beginDate=2022-05-31&endDate=2022-07-01

The results include duplicate rows based on id. https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/derived-values orisCode=3&locationName=1%7C2&beginDate=2022-05-31&endDate=2022-07-01

Seems to return location “1” or “2” for any ORIS code, not just ORIS code 3. i. The resulting volume of data might explain the issue with the Swagger page failing.

Issue 4

https://api.epa.gov/easey/dev/streaming-services/emissions/summary-values?orisCode=3&beginYear=2022&beginQuarter=2&endYear=2022&endQuarter=3

The results include duplicate rows based on id.

Issue 5

https://api.epa.gov/easey/dev/streaming-services/emissions/supplemental-operating?orisCode=3&beginYear=2022&beginQuarter=2&endYear=2022&endQuarter=3

The results include duplicate rows based on id. Based on SQL that Abu previously provided, I believe the duplicate data for each endpoint is a result of joins to MONITOR_PLAN_LOCATION. MONITOR_PLAN_LOCATION will have multiple rows for a MON_LOC_ID when the location has been part of multiple monitoring plans (MON_PLAN_ID).

Issue 6

https://api.epa.gov/easey/dev/streaming-services/emissions/supplemental-operating?orisCode=3&beginYear=2022&beginQuarter=2&endYear=2022&endQuarter=3 The results include duplicate rows based on id.

[JanellC]

Issue 1 is resolved. Hourly operating endpoint is returning the elements below

{ "id":"L3FY866-2CCECD1B9B694B7F81F8C67F4D1D9757", "locationId":"2295", "reportPeriodId":113, "beginDate":"2021-01-05", "beginHour":7, "operatingTime":0, "hourLoad":null, "loadUnitsOfMeasureCode":null, "userId":"CRandle", "addDate":"2021/04/07 07:34:06", "updateDate":null }

[djw4erg]

Issue #1: Successfully tested.

[djw4erg]

Issue #2: Returns all units for Barry (ORIS 3) instead of just units 1 and 2, and apparently all units 1 and 2 at other facilities. The endpoint should only return units 1 and 2 for Barry.

Kyle, Abu and I discussed this issue and Kyle completed a correction, but needs to push that change. I will retest afterward.

[djw4erg]

Issue #2: Successfully tested.

[djw4erg]

Issue #3:

https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/derived-values?orisCode=3&beginDate=2022-05-31&endDate=2022-07-01

• Does not return data even though corresponding data seems to exist in the database.

https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/derived-values?orisCode=3&locationName=1|2&beginDate=2022-05-31&endDate=2022-07-01

• Currently returns HTML with processing icon.

[djw4erg]

Issue #4: Successfully tested. Issue #5: I believe this was a comment about all of the other duplicate issues, but was not an issue itself. Issue #6: Successfully tested.

[JanellC]

Issues have been tested and verified by ERG @djw4erg. Closing the ticket out

[djw4erg]

10/24/2023 Direct API Testing:

Issue 2:

1. Requested stack or pipe locations are not returned in the data.

Issue 3:

1. Requested stack or pipe locations are not returned in the data.

[djw4erg]

2023-10-24 Testing Results:

• URL: https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/operating?orisCode=3&locationName=1|2|CS0AAN&beginDate=2022-05-31&endDate=2022-07-01
  • Returns locations 1 and 2, but does not return CS0AAN.
• URL: https://api.epa.gov/easey/dev/streaming-services/emissions/hourly/derived-values?orisCode=3&locationName=1|2|CS0AAN&beginDate=2022-05-31&endDate=2022-07-01
  • Returns locations 1 and 2, but does not return CS0AAN.

Note that for the third and forth bullets using the following to link to CAMD.UNIT or CAMDECMPS.STACK_PIPE might be useful. select fac.Oris_Code, fac.Facility_Name, coalesce( unt.Unitid, stp.Stack_Name) as Location_Name, loc.Mon_Loc_Id from camdecmps.MONITOR_LOCATION loc left join camd.UNIT unt on unt.Unit_Id = loc.Unit_Id left join camdecmps.STACK_PIPE stp on stp.Stack_Pipe_Id = loc.Stack_Pipe_Id join camd.PLANT fac on fac.Fac_Id in ( unt.Fac_Id, stp.Fac_Id ) where fac.Oris_Code = 3 and coalesce( unt.Unitid, stp.Stack_Name) in ( '1', '2', 'CS0AAN' ) order by Oris_Code, Location_Name;

[JanellC]

@djw4erg Abu has started work on this. Based on priority discussion. Abu will not finish this and will continue to work on bugs for ECMPS. Below I have provided the PR for this ticket. FYI- @chrisgitsit @maheese

https://github.com/US-EPA-CAMD/easey-streaming-services/pull/96

[djw4erg]

Corrected failure to include stacks and pipes in following API calls:

• HOD
• DHV

Pull Request fix/5693-Reported_Emissions-API-Not-Returning-Stack-Pipe

[djw4erg]

Moved to Ready for QA because pull request was merged.

[djw4erg]

Testing Information: The QA testing for this will occur in conjunction with programming the CBS consumer(s) of the API endpoints.