Some Example Queries to Show What You Can Do

Basic, global average temperature by source, experiment, and year

select
  concat(v.source_id,'-',v.experiment_id),
  v.year,
  sum(cell_area_m_sq * temp_f) / sum(cell_area_m_sq) as temp_f
from
  POLLEN_EXTERNAL.CMIP6_PROJECTIONS_BY_TIME_AND_PLACE v
where
  cell_area_m_sq is not null
  and temp_f is not null
group by
  concat(v.source_id,'-',v.experiment_id),v.year
order by v.year;

Average temperature for California by year and experiment

This one we’re limiting to only one source so we can be sure we’re getting high resolution data

select
  v.experiment_id,
  v.year,
  sum(cell_area_m_sq * temp_f) / sum(cell_area_m_sq) as temp_f
from POLLEN_EXTERNAL.CMIP6_PROJECTIONS_BY_TIME_AND_PLACE v
inner join POLLEN_EXTERNAL.CMIP6_PLACES p on p.place_id = v.place_id
inner join POLLEN_EXTERNAL.us_states s on st_intersects(s.geo_bounds, p.geo_bounds)
where
  (v.center_lat < 50 and v.center_lat > 20) and (v.center_lon < -60 and v.center_lon > -135)
  and s.state_code = 'CA'
  and v.source_id = 'CESM2-WACCM'
  and v.cell_area_m_sq is not null
  and v.temp_f is not null
group by v.experiment_id,v.year
order by v.year;

US states ranked by experiment_id and change in average winter temperature before 2030 vs after 2040

Here, we are excluding the lower resolution models, but otherwise taking an average across models. Strictly speaking, we should be doing a weighted average, like we did above, but because we’re grouping by state it happens to be the case that the geographies we’re talking about will be close enough to the same size, anyway, because they are all in basically the same place on the globe. So, to keep the example readable, we’re just using a straight average.

select
  s.state_code,
  v.experiment_id,
  array_agg(distinct v.source_id) as source_ids,
  avg(case when v.year < 2031 then v.temp_f else null end) as avg_before,
  avg(case when v.year >= 2040 then v.temp_f else null end) as avg_after,
  abs((avg(case when v.year >= 2040 then v.temp_f else null end) - avg(case when v.year < 2031 then v.temp_f else null end))) as abs_diff
from POLLEN_EXTERNAL.CMIP6_PROJECTIONS_BY_TIME_AND_PLACE v
inner join POLLEN_EXTERNAL.CMIP6_PLACES p on p.place_id = v.place_id
inner join POLLEN_EXTERNAL.us_states s on st_intersects(s.geo_bounds, p.geo_bounds)
where
  (v.center_lat < 50 and v.center_lat > 20) and (v.center_lon < -60 and v.center_lon > -135)
  and v.experiment_id not in ('historical','esm-hist', 'piControl')
  and v.source_id not in ('CanESM5','MPI-ESM1-2-LR')
  and v.month in (12,1,2)
  and v.temp_f is not null
  group by
    s.state_code, v.experiment_id
  order by
    6 desc;