Как узнать историческую погоду для любого города с BigQuery?

Question

BigQuery содержит данные gsod NOAA, загруженные как общедоступный набор данных - начиная с 1929 года: https://www.reddit.com/r/bigquery/comments/2ts9wo/noaa_gsod_weather_data_loaded_into_bigquery/

Как я могу получить исторические данные для любого города?

Answer 1

Обновление 2017: стандартный SQL и новейшие таблицы:

SELECT TIMESTAMP(CONCAT(year,'-',mo,'-',da)) day, AVG(min) min, AVG(max) max, AVG(IF(prcp=99.99,0,prcp)) prcp
FROM `bigquery-public-data.noaa_gsod.gsod2016`
WHERE stn='722540' AND wban='13904'
GROUP BY 1
ORDER BY day

---

Чтобы получить историческую погоду для любого города, сначала нам нужно найти, какая станция сообщает в этом городе. Стол [fh-bigquery:weather_gsod.stations] содержит название известных станций, их штат (если в США), страну и другие подробности.

Таким образом, чтобы найти все станции в Остине, штат Техас, мы бы использовали такой запрос:

SELECT state, name, lat, lon
FROM [fh-bigquery:weather_gsod.stations] 
WHERE country='US' AND state='TX' AND name CONTAINS 'AUST'
LIMIT 10

У этого подхода есть 2 проблемы, которые необходимо решить:

• Не все известные станции присутствуют в этой таблице - мне нужно получить обновленную версию этого файла. Так что не сдавайтесь, если вы не найдете здесь нужную станцию.
• Не каждая станция, найденная в этом файле, работает каждый год, поэтому нам нужно найти станции, у которых есть данные за тот год, который мы ищем.

Чтобы решить вторую проблему, нам нужно объединить таблицу станций с актуальными данными, которые мы ищем. Следующий запрос ищет станции вокруг Остина и столбец c показывает, сколько дней в 2015 году имеют фактические данные:

SELECT state, name, FIRST(a.wban) wban, FIRST(a.stn) stn, COUNT(*) c, INTEGER(SUM(IF(prcp=99.99,0,prcp))) rain, FIRST(lat) lat, FIRST(lon) long
FROM [fh-bigquery:weather_gsod.gsod2015] a
JOIN [fh-bigquery:weather_gsod.stations] b 
ON a.wban=b.wban
AND a.stn=b.usaf
WHERE country='US' AND state='TX' AND name CONTAINS 'AUST'
GROUP BY 1,2
LIMIT 10

Это хорошо! Мы нашли 4 станции с данными для Остина в течение 2015 года.

Обратите внимание, что мы должны были относиться к "дождю" особым образом: когда станция не отслеживает дождь, а не null, это отмечает это как 99.99. Наш запрос фильтрует эти значения.

Теперь, когда мы знаем номера stn и wban для этих станций, мы можем выбрать любую из них и визуализировать результаты:

SELECT TIMESTAMP('2015'+mo+da) day, AVG(min) min, AVG(max) max, AVG(IF(prcp=99.99,0,prcp)) prcp
FROM [fh-bigquery:weather_gsod.gsod2015]
WHERE stn='722540' AND wban='13904'
GROUP BY 1
ORDER BY day

Answer 2

Теперь есть официальный набор данных NOAA по BigQuery в дополнение к Felipe Hoffa. Есть сообщение в блоге, описывающее это.

Пример получения минимальной температуры за 15 августа 2016 года:

SELECT
  name, 
  value/10 AS min_temperature,
  latitude,
  longitude
FROM
  [bigquery-public-data:ghcn_d.ghcnd_stations] AS stn
JOIN
  [bigquery-public-data:ghcn_d.ghcnd_2016] AS wx
ON
  wx.id = stn.id
WHERE
  wx.element = 'TMIN'
  AND wx.qflag IS NULL
  AND STRING(wx.date) = '2016-08-15'

Который возвращает:

Answer 3

Спасибо за то, что нашли данные и сделали их общедоступными. Вот BigQuery, который возвращает общее количество осадков в 2014 году для каждой станции в Техасе:

SELECT FIRST(name) AS station_name, stn, SUM(prcp) AS annual_precip
FROM [fh-bigquery:weather_gsod.gsod2014] gsod
JOIN [fh-bigquery:weather_gsod.stations] stations
ON gsod.wban=stations.wban AND gsod.stn=stations.usaf
WHERE state='TX' AND prcp != 99.99
GROUP BY stn

который возвращает:

Вычисление количества дождливых дней в каждом месте и сортировка результатов на основе этого:

SELECT FIRST(name) AS station_name, stn, SUM(prcp) AS annual_precip,     COUNT(prcp) AS rainy_days
FROM [fh-bigquery:weather_gsod.gsod2014] gsod
JOIN [fh-bigquery:weather_gsod.stations] stations
ON gsod.wban=stations.wban AND gsod.stn=stations.usaf
WHERE state='TX' AND prcp != 99.99 AND prcp > 0
GROUP BY stn
ORDER BY rainy_days DESC

приходит с ,

Answer 4

Использование названия станции ненадежно. Кроме того, сложно использовать геопространственный запрос с использованием новых возможностей больших запросов, поскольку границы городов не имеют четких фигур (таких как круг или прямоугольник).

Поэтому лучшее решение, которое я нашел для вашей проблемы, - это использовать обратное геокодирование, запрашивая API Карт Google для получения адреса, штата, города и округа для каждой станции, используя ее координаты широты / долготы.

Вот результирующий CSV (StationNumber,Lat,Lon,Address,State,City,County,Zip) для США (вы заметите, что там 98% станций существует): https://gist.github.com/orcaman/a3e23c47489705dff93aace2e35f57d3

Вот код на тот случай, если вы хотите перезапустить его на станциях за пределами США (Голанг): https://gist.github.com/orcaman/8de55f14f1c70ef5b0c124cf2fb7d9d1