BigQuery 将 NOAA 的 gsod 数据加载为公共数据集 - 开始于 1929 年:https://www.reddit.com/r/bigquery/comments/2ts9wo/noaa_gsod_weather_data_loaded_into_bigquery/
我如何检索任何城市的历史数据?
BigQuery 将 NOAA 的 gsod 数据加载为公共数据集 - 开始于 1929 年:https://www.reddit.com/r/bigquery/comments/2ts9wo/noaa_gsod_weather_data_loaded_into_bigquery/
我如何检索任何城市的历史数据?
更新于2019年:为了方便起见
SELECT *
FROM `fh-bigquery.weather_gsod.all`
WHERE name='SAN FRANCISCO INTERNATIONAL A'
ORDER BY date DESC
每日更新 - 如若未更新,请在此处报告
例如,要获取自1980年以来旧金山站点最热的日子:
SELECT name, state, ARRAY_AGG(STRUCT(date,temp) ORDER BY temp DESC LIMIT 5) top_hot, MAX(date) active_until
FROM `fh-bigquery.weather_gsod.all`
WHERE name LIKE 'SAN FRANC%'
AND date > '1980-01-01'
GROUP BY 1,2
ORDER BY active_until DESC
请注意,由于使用了聚类表,此查询仅处理了28MB。
同样地,但不是使用车站名称,而是使用位置和一个按位置聚类的表:
WITH city AS (SELECT ST_GEOGPOINT(-122.465, 37.807))
SELECT name, state, ARRAY_AGG(STRUCT(date,temp) ORDER BY temp DESC LIMIT 5) top_hot, MAX(date) station_until
FROM `fh-bigquery.weather_gsod.all_geoclustered`
WHERE EXTRACT(YEAR FROM date) > 1980
AND ST_DISTANCE(point_gis, (SELECT * FROM city)) < 40000
GROUP BY name, state
HAVING EXTRACT(YEAR FROM station_until)>2018
ORDER BY ST_DISTANCE(ANY_VALUE(point_gis), (SELECT * FROM city))
LIMIT 5
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
额外的例子,展示芝加哥在这个十年中最冷的几天:
#standardSQL
SELECT year, FORMAT('%s%s',mo,da) day ,min
FROM `fh-bigquery.weather_gsod.stations` a
JOIN `bigquery-public-data.noaa_gsod.gsod201*` b
ON a.usaf=b.stn AND a.wban=b.wban
WHERE name='CHICAGO/O HARE ARPT'
AND min!=9999.9
AND mo<'03'
ORDER BY 1,2
要获取任何城市的历史天气数据,首先我们需要找到在该城市报告的气象站。表格[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
这种方法有两个问题需要解决:
为了解决第二个问题,我们需要将站点表与我们正在寻找的实际数据连接起来。以下查询查找奥斯汀周围的站点,列 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
太好了!我们找到了2015年奥斯汀市有数据的四个气象站。
请注意,我们必须以特殊方式处理“降雨”:当一个气象站不监测降雨时,它会将其标记为99.99而非null
。我们的查询过滤掉了这些数值。
现在我们知道这些气象站的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
现在除了Felipe的“官方”公共数据集之外,还有一个官方的NOAA BigQuery数据集。这里有一篇描述它的博客文章。
以下是获取2016年8月15日最低温度的示例:
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'
返回:
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
出现了 。
使用车站名称是不可靠的。此外,使用新的bigquery功能进行地理空间查询很困难,因为城市边界没有清晰的形状(如圆形或矩形)。
因此,我找到了解决您问题的最佳方案,即使用反向地理编码,请求Google Maps API根据每个车站的纬度/经度坐标生成地址、州、城市和县。
以下是美国的结果CSV文件(StationNumber、Lat、Lon、Address、State、City、County、Zip
),您会注意到98%的车站都在美国:
https://gist.github.com/orcaman/a3e23c47489705dff93aace2e35f57d3
以下是代码,如果您想在美国以外的站点上重新运行它(使用golang): https://gist.github.com/orcaman/8de55f14f1c70ef5b0c124cf2fb7d9d1
gsod1929
到gsod2016
)是一项相当冗长的任务:( - iTurkifh-bigquery
项目和数据仍然存在,但我认为它将永远不会更新。 - Preston Marshall