Create an API to query in situ data for summary page

[amehta-scottlogic]

Description

The first screen in the dashboard is going to show 10 cities where the forecasted and in situ values are most different. This will be done for a specific forecast_base_time and forecast_valid_time

To do this we need to actually calculate this from the data in our database.

Acceptance Criteria

• An API exists with details: GET /air-pollutant/measurements/summary
• location_type and measurement_base_time & measurement_time_range are validated and if not valid we'll return 422
• Success will return 200
• Required query parameters should be location_type, measurement_base_time, measurement_time_range
• Measurements should be:
  • grouped together for each location where they are +/- 1.5 hours around the forecast valid time
  • averages will use the mean
  • an average pollutant value should be taken for these and AQI calculated accordingly (reuse current code)
  • overall AQI is then calculated for each of these (reuse current code)
  • the time assigned is the measurement_base_time
• Response should be JSON array with items of structure:

  {
  "measurement_base_time": "2024-06-06T09:00:00+00:00",
  "location_type": "city",
  "location_name": "Brussels",
  "overall_aqi_level": {
    "mean": 2,
    "median": 2
  },
  "no2": {
    "mean": {
      "aqi_level": 1,
      "value": 26.439393939393938
    },
    "median": {
      "aqi_level": 1,
      "value": 24
    }
  },
  "o3": {
    "mean": {
      "aqi_level": 1,
      "value": 49.69047619047619
    },
    "median": {
      "aqi_level": 1,
      "value": 47
    }
  },
  "pm2_5": {
    "mean": {
      "aqi_level": 2,
      "value": 18.806401519968052
    },
    "median": {
      "aqi_level": 2,
      "value": 17.39
    }
  },
  "pm10": {
    "mean": {
      "aqi_level": 2,
      "value": 32.496022749428796
    },
    "median": {
      "aqi_level": 2,
      "value": 30
    }
  },
  "so2": {
    "mean": {
      "aqi_level": 1,
      "value": 4.211111111111111
    },
    "median": {
      "aqi_level": 1,
      "value": 4.85
    }
  }
  },

  Test Checklist:

• [x] Test Analysis & discussion with developer
• [x] Write Test Cases / Charters
• [x] Test Cases & Charters Reviewed
• [x] Test Cases Executed
• [x] Test Charters Executed
• [x] Regression tests executed
• [x] Automation Tests added to project

[mwalker-scottlogic]

Test Analysis

How this should work in practice, having confirmed with Ajay

• GET request made to /air-pollutant/measurements/summary with params:

• location_type - city

• measurement_base_time - 2024-06-06T00:00:00+00:00

• measurement_time_range - 90

• expecting in the response any measurements from 2024-06-05T22:30:00+00:00 to 2024-06-06T01:30:00+00:00 from any city in the database

• UPDATE - median no longer included in ticket

• Undertake an initial sense check using Postman

• Create automation using Pytest with Request library for functional test cases

• POISED testing heuristic applied

• Seed data in database as ETL is tested elsewhere, more repeatable outcomes

Parameters:

• Verify location_type is required
• Verify measurement_base_time is required
• Verify measurement_time_range is required
• Verify a call with valid location_type, measurement_time_range and measurement_base_time is status 200
• Verify a call with invalid location_type, measurement_time_range and measurement_base_time is status 422
• All automated :white_check_mark:

Output:

Seed data for a specific city

• BVA -1.5hrs

  • Verify seeded data with measurement_date -91 mins from measurement_time is not affecting mean / median value returned
  • Verify seeded data with measurement_date -90 mins from measurement_time is affecting mean / median value returned
  • Verify seeded data with measurement_date -89 mins from measurement_time is affecting mean / median value returned

• BVA +1.5hrs

  • Verify seeded data with measurement_date +89 mins from measurement_time is affecting mean / ~~median value returned ~~
  • Verify seeded data with measurement_date +90 mins from measurement_time is affecting mean / median value returned
  • Verify seeded data with measurement_date +91 mins from measurement_time is not affecting mean / median value returned
  • all automated (with some different boundaries to fit test data :white_check_mark:

Manually calculate pollutant level and overall AQI means and medians for a test dataset.

• Verify mean value is correct for within-boundary test data
• Verify median value is correct for within-boundary test data
• Verify pollutant level mean AQI value is correct for test data
• Verify pollutant level median AQI value is correct for test data
• Verify overall AQI level mean value is correct for test data
• Verify overall AQI level median value is correct for test data
• Verify measurement_base_time is correctly assigned to response
• All automated :white_check_mark:

Interoperability:

Security:

• Having enquired, API does not need to be locked down

Errors:

• Verify if error messages are clear and helpful
  • Verify behaviour if database is down
  • Verify if parameters fail validation error messages are useful
  • To be tested via exploratory testing

Data:

• No data in database
• 1 document in database
• many documents in database

Test Charter:

• Charter 9: Explore Summary In Situ API with creative parameters and headers to discover surprises :white_check_mark:

Regression candidate:

• Yes

[mwalker-scottlogic]

Finding - Pollutant median not calculated correctly on even dataset

Description:

• On an odd dataset (eg 3 documents in database) correct median is calculated
• On an even dataset (eg 2 documents in database), the median displayed is the lower number rather than median. According to the statistic.median() docs it should be:
  • "When the number of data points is even, the median is interpolated by taking the average of the two middle values"

Steps to reproduce:

1. Design a Postman API query such as GET /air-pollutant/measurements/summary?measurement_base_time=2024-07-20T15%3A00%3A00.000%2B00%3A00&measurement_time_range=90&location_type=city
2. Delete the data in the in_situ_data database
3. Seed specific test data that in conjunction with the params of the API call above would provide 2 documents for one city to calculate the median
4. Make the call and assess the pollutant level median

Expected:

• Median is median

Actual:

• Median is not median for two values

Test Data:

API Response:

[mwalker-scottlogic]

forecast_api_seeded_data_tests.py, forecast_api_validation_tests.py, cams_known_grib_test.py, forecast_calibration_tests.py & open_aq_etl_tests.py tests all passed.

Executed manual test cases, had to make some changes to test assertions, but expected behaviour was observed

[mwalker-scottlogic]

Findings - Charter 9 Test Execution

• for details of what was explored see here
• if 'location_type` is a long paragraph, "msg": "Input should be 'city'". If always needs to be city, why have it as a param if it is always the same and required
• measurement_time_range:
  • can be negative
  • decimal problem 1.0 is 200OK vs 1.5 is a 422
  • whitespace permitted (e.g.' 1' works)
• you can send a body with the API call e.g {"foo": "bar"}