Air Quality

About the dataset

The WHO Ambient Air Quality Database (6th edition, released in May 2023) compiles annual mean concentrations of nitrogen dioxide (NO2) and particulate matter (PM10, PM2.5) from ground measurements across over 8600 human settlements in more than 120 countries. This data, updated every 2-3 years since 2011, primarily represents city or town averages and is used to monitor the Sustainable Development Goal Indicator 11.6.2, Air quality in cities.

Here's the schema :

column_name	column_type	null	key	default	extra
who_region	VARCHAR	YES
iso3	VARCHAR	YES
country_name	VARCHAR	YES
city	VARCHAR	YES
year	BIGINT	YES
version	VARCHAR	YES
pm10_concentration	BIGINT	YES
pm25_concentration	BIGINT	YES
no2_concentration	BIGINT	YES
pm10_tempcov	BIGINT	YES
pm25_tempcov	BIGINT	YES
no2_tempcov	BIGINT	YES
type_of_stations	VARCHAR	YES
reference	VARCHAR	YES
web_link	VARCHAR	YES
population	VARCHAR	YES
population_source	VARCHAR	YES
latitude	FLOAT	YES
longitude	FLOAT	YES
who_ms	BIGINT	YES

To read from the sample_data database, please refer to attach the sample datasets database

Example queries

Annual city air quality rating

This query assesses the average annual air quality in different cities per year based on WHO guidelines. It calculates the average concentrations of PM2.5, PM10, and NO2, then assigns an air quality rating of 'Good', 'Moderate', or 'Poor'. 'Good' indicates all pollutants are within WHO recommended levels, 'Poor' indicates all pollutants exceed WHO recommended levels, and 'Moderate' refers to any other scenario. The results are grouped and ordered by city and year.

SELECT
    city,
    year,
    CASE
        WHEN
            AVG(pm25_concentration) <= 10
            AND AVG(pm10_concentration) <= 20
            AND AVG(no2_concentration) <= 40
            THEN 'Good'
        WHEN
            AVG(pm25_concentration) > 10
            AND AVG(pm10_concentration) > 20
            AND AVG(no2_concentration) > 40
            THEN 'Poor'
        ELSE 'Moderate'
    END AS airqualityrating
FROM
    sample_data.who.ambient_air_quality
GROUP BY
    city,
    year
ORDER BY
    city,
    year;

Yearly average pollutant concentrations of a city

This query calculates the yearly average concentrations of PM2.5, PM10, and NO2 in a given city, here Berlin.

SELECT
    year,
    AVG(pm25_concentration) AS avg_pm25,
    AVG(pm10_concentration) AS avg_pm10,
    AVG(no2_concentration) AS avg_no2
FROM sample_data.who.ambient_air_quality 
WHERE city = 'Berlin'
GROUP BY year
ORDER BY year DESC;