B Example SQL queries

B.1 Station locations

Please note the table include latitude and longitude (EPSG:4283) fields. Geometry is stored using the PostGIS extension in the geom_gda94 (EPSG:4283) and geom_albers (EPSG:3577) columns.

To get specific state/territory information, query the individual state/territory table.

SELECT * 
FROM air_pollution_monitors.ap_monitor_locations_nsw
LIMIT 10;

Or query for all states and territories, retrieving common fields

SELECT * 
FROM air_pollution_monitors.ap_monitor_locations_master
LIMIT 10;

B.2 Query for selected station locations

Get all stations by variable:

SELECT l.* 
FROM air_pollution_monitors.ap_monitor_locations_master l
WHERE l.station_id IN 
(SELECT d.station_id 
 FROM air_pollution_monitors.ap_monitor_data_master d
 WHERE d.variable ='co');

Get all stations by year and variable:

SELECT l.* 
FROM air_pollution_monitors.ap_monitor_locations_master l
WHERE l.station_id IN 
(SELECT d.station_id 
 FROM air_pollution_monitors.ap_monitor_data_master d
 WHERE d.year = 2010
 AND d.variable ='co');

Get stations by variable, year and state

SELECT l.* 
FROM air_pollution_monitors.ap_monitor_locations_sa l
WHERE EXISTS 
    (SELECT 1 FROM air_pollution_monitors.ap_monitor_data_sa_2015 d
     WHERE l.station_id = d.station_id
     AND d.variable ='pm25')
ORDER BY l.station DESC;

B.3 Observation data

Get hourly data by state and year (fastest to query the child table):

SELECT * FROM air_pollution_monitors.ap_monitor_data_wa_2020;

Get hourly data by year and variable:

SELECT * FROM air_pollution_monitors.ap_monitor_data_master
WHERE year = 2020
AND variable ='no2';

Get hourly data by variable and state:

SELECT * FROM air_pollution_monitors.ap_monitor_data_master
WHERE state = 'SA'
AND variable ='pm10';

Get hourly data by variable for multiple states:

SELECT * FROM air_pollution_monitors.ap_monitor_data_master
WHERE state in ('SA','VIC')
AND variable ='pm10';

Get hourly data by state for multiple variables:

SELECT * FROM air_pollution_monitors.ap_monitor_data_master
WHERE state = 'ACT'
AND variable in ('no2','o3');")