Google Cloud Platform
U.S. EPA and OpenAQ air quality data now available in BigQuery
Using these new public datasets in BigQuery is a great way to understand air quality in your community.
Take a deep breath: The average person takes between 17,000 and 23,000 breaths a day. But how often do you breathe in poor quality air? Do you know if the air in your town is clean?
- OpenAQ, which includes real-time air quality from 47 countries around the world
- EPA, which includes the last 27 years of air quality from around the United States
OpenAQ: Real-time air qualityThe OpenAQ dataset is updated hourly1 to show a nearly live look at government-reported air quality around the world. With this dataset, you can answer questions like:
- Where are the global hotspots for poor air quality right now?
- How does one city compare to others?
Answer: Hualpén, Chile and 2 locations in Turkey currently have the highest concentrations of PM10.
You can try this query yourself in BigQuery using the following standard SQL:
location, city, country, value,
CONCAT(CAST(latitude AS STRING), ', ', CAST(longitude AS STRING)) AS latlong
pollutant = "pm10"
EPA: Historical air qualityThe EPA dataset contains over 25GB of data, ranging from annual summaries to hourly particulate measurements from around the country. With BigQuery, you can query these vast archives in seconds, helping to answer questions like:
Montana had the lowest PM2.5 concentrations. 2015 is the last year for which we have an entire year of data, but the trend in 2016 looks similar.
Try this query yourself in BigQuery using the following standard SQL statement:
state_name, avg(arithmetic_mean) as avg_value
sample_duration = "24 HOUR"
AND poc = 1
AND EXTRACT(YEAR FROM date_local) = 2015
It’s getting better! You can replace the city/state name with your location and use the query below in BigQuery. For smaller areas, you can leverage the city, county, or cbsa_name fields (or you can always use latitude/longitude).
Try it yourself with the following standard SQL:
EXTRACT(YEAR FROM date_local) as year, avg(arithmetic_mean) as avg_value
poc = 1
AND sample_duration = "24 HOUR"
AND city_name = "Pittsburgh"
AND state_name = "Pennsylvania"
Take actionMaking air quality data available is one more way that Google organizes the world’s information and makes it universally accessible and useful. But we hope that you try these datasets (EPA, OpenAQ) out for yourself and learn something about your own community. If your area doesn’t have any data available, work with your local leaders to publish or share this information. You can even contribute data or code to the OpenAQ open-source project.
1 While we update the dataset in BigQuery hourly, the individual locations may send updates less frequently. We display the latest information we have.