Air Quality

Explore the data

Interactive dashboard built on the WHO air quality dataset. Use it as a starting point for your own Dives.

Loading live Dive preview...

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.

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.

Example query

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;

SQL Editor loading...

Login to connect

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.

Example query

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;

SQL Editor loading...

Login to connect

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