历史气象资料BigQuery

Question

我试着在给定日期之前7天获取天气数据，并接近一些坐标(lat，lon)。大约20公里半径。如果有多个站点，我可能会想要平均按日分组的数据。

有什么方法可以直接用BigQuery计算所有这些吗？为了进行测试，我计算了min和max坐标，并创建了以下查询

SELECT
  *
FROM
  [bigquery-public-data:noaa_gsod.gsod2016] a
JOIN
  [bigquery-public-data:noaa_gsod.stations] b
ON
  a.stn=b.usaf
  AND a.wban=b.wban
WHERE
  (b.lat >= 46.248332
    AND b.lat <= 47.147654)
  AND (b.lon >= 5.689853
    AND b.lon <= 7.001115)
  AND a.mo='03'

我对这个问题还不太满意

• 它不平均每天通过多个站点来选择给定月份的所有数据。
• 我怎样才能超过某一特定日期的7天？
• 能否通过查询直接计算max和min lat/lon？
• 通常情况下，它找不到任何数据，因为半径20公里的半径太小，找不到台站。如果查询在半径20公里范围内找不到，如何修改查询以找到最近的站点？
• 我能得到更好的、免费的历史天气数据吗？

这就是我计算最小最大坐标的方法：

maxLat = lat + math.degrees(searchRadius / earthRadius)
minLat = lat - math.degrees(searchRadius / earthRadius)
maxLon = lon + math.degrees(searchRadius / earthRadius) / math.cos(math.radians(lat))
minLon = lon - math.degrees(searchRadius / earthRadius) / math.cos(math.radians(lat))

Answer 1

这是我能想到的最好的解决方案：

#standardSQL
CREATE TEMP FUNCTION distance(lat1 FLOAT64, lat2 FLOAT64, lon1 FLOAT64, lon2 FLOAT64) AS((
WITH data AS(
SELECT POW(SIN((ACOS(-1) / 180 * (lat1 -lat2)) / 2), 2) + COS(ACOS(-1) / 180 * (lat1)) * COS(ACOS(-1) / 180 * (lat2)) * POW(SIN((ACOS(-1) / 180 * (lon1 -lon2)) / 2), 2) a
)
SELECT 6371 * 2 * ATAN2(SQRT((SELECT a FROM data)), SQRT(1 - (SELECT a FROM data)))
));

WITH temperature_data AS(
SELECT
  CONCAT(year, mo, da) date,
  temp,
  b.lat lat,
  b.lon lon
FROM `bigquery-public-data.noaa_gsod.gsod2016` a
JOIN `bigquery-public-data.noaa_gsod.stations` b
ON a.stn = b.usaf AND a.wban = b.wban
WHERE concat(year, mo, da) BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(PARSE_DATE('%Y%m%d', '20160725'), INTERVAL 7 DAY)) AND '20160725'
)

SELECT
  date,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 20, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 20, temp, NULL)) AS std_temp) data_20km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 50, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 50, temp, NULL)) AS std_temp) data_50km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 100, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 100, temp, NULL)) AS std_temp) data_100km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 200, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 200, temp, NULL)) AS std_temp) data_200km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 500, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 500, temp, NULL)) AS std_temp) data_500km
FROM temperature_data t
WHERE
distance(t.lat, 10.1, t.lon, 10.2) < 2000
GROUP BY date
ORDER BY date

我试着解释一下你们的问题：

我怎样才能超过某一特定日期的7天？

在查询temperature_data中，注意WHERE子句有以下条件：

WHERE concat(year, mo, da) BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(PARSE_DATE('%Y%m%d', '20160725'), INTERVAL 7 DAY)) AND '20160725'

这是从给定日期中选择最后7天的地方。您可以通过更改“20160725”值来选择要分析的日期。

能否通过查询直接计算max和min lat/lon？

是。我想您的意思是，如果可以在给定的范围内选择空间点(例如，20公里)。这样做的一种方法是定义一个临时函数来计算所需点和站点点之间的距离，该函数在查询中通过以下方式表示：

CREATE TEMP FUNCTION distance(lat1 FLOAT64, lat2 FLOAT64, lon1 FLOAT64, lon2 FLOAT64) AS((
WITH data AS(
SELECT POW(SIN((ACOS(-1) / 180 * (lat1 -lat2)) / 2), 2) + COS(ACOS(-1) / 180 * (lat1)) * COS(ACOS(-1) / 180 * (lat2)) * POW(SIN((ACOS(-1) / 180 * (lon1 -lon2)) / 2), 2) a
)
SELECT 6371 * 2 * ATAN2(SQRT((SELECT a FROM data)), SQRT(1 - (SELECT a FROM data)))
));

例如，您可以使用并测试此函数：

SELECT distance(50, 60, 30, 10) # result is ~ 1680km

这个函数在这里使用：

WHERE
distance(t.lat, 10.1, t.lon, 10.2) < 2000

滤出距离(10.1°，10.2°)超过2000公里的点。在查询中，可以选择不同的输入值，而不是(10.1°，10.2°)。

通常情况下，它找不到任何数据，因为半径20公里的半径太小，找不到台站。如果查询在半径20公里范围内找不到，如何修改查询以找到最近的站点？

一个可能的解决方案是一次查询几个不同的距离：

SELECT
  date,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 20, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 20, temp, NULL)) AS std_temp) data_20km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 50, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 50, temp, NULL)) AS std_temp) data_50km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 100, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 100, temp, NULL)) AS std_temp) data_100km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 200, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 200, temp, NULL)) AS std_temp) data_200km,
  STRUCT(AVG(IF(distance(t.lat, 10.1, t.lon, 10.2) < 500, temp, NULL)) AS avg_temp, STDDEV_SAMP(IF(distance(t.lat, 10.1, t.lon, 10.2) < 500, temp, NULL)) AS std_temp) data_500km
FROM temperature_data t
WHERE
distance(t.lat, 10.1, t.lon, 10.2) < 2000
GROUP BY date

请注意，此查询正在提取从输入点(10.1°，10.2°)到2000公里以外的站点点。然后应用滤波器对20 is、50 is、100 is、200 is和500 is范围内的点进行选择。

您可以根据需要更改这些值。如果你想从另一个点得到平均温度，比如说(40°，30°)，只要把这个值(10.1，10.2)改为(40，30)，你就可以了。此外，如果您希望从这一点开始有不同的距离，您可以将表达式IF(distance(t.lat, 10.1, t.lon, 10.2) < 200更改为更适合您的需要的范围。

请注意，WHERE子句具有以下条件：

distance(t.lat, 10.1, t.lon, 10.2) < 2000

因此，这是过滤出所有更远的站(10.1，10.2)，超过2000公里。您还可以根据需要更改此值。

最后注意:我还带来了STDDEV_SAMP，也就是抽样标准差。这可能对你有一定的价值，同时也能让你了解平均值在平均值上的分布程度(用抽样数据大小效应来修正)。如果我们不知道我们离正确的价值有多近，那么平均值本身就没有那么有价值。

我能得到更好的、免费的历史天气数据吗？

我也不知道。希望这个公共数据集对您来说是足够好的。

Answer 2

对于您所提供的信息，我不确定您是否能够计算查询中的最大/分钟数据。在Legacy SQL中工作，我可能会尝试嵌套多个查询，或者加入一个计算它们的查询，或者两者兼而有之。

您可能还能够在必要时编写一些调整搜索查询的内容，但我只是不知道您所做的工作的结构是否足以编写建议。

关于其他问题：

获取平均值--而不是使用*调用所有您需要单独调用的列，哪些列要平均，哪些列要忽略或分组。

选择某一特定日期的过去7天，这是非常不幸的，似乎没有时间戳列，所以你将不得不强制一个。

在LegacySQL中，我会写这样的东西：

SELECT dte, avg_temp, avg_cnt_temp
FROM 
(SELECT CAST(CONCAT(a.year, '-', a.mo, '-', a.da) AS timestamp) AS dte,
/* This is calling the separate year, month, and day strings as a 
datetime funtion so I can use date_add later */ 
AVG(a.temp) AS avg_temp, AVG(a.count_temp) AS avg_cnt_temp /* You'll 
want to include all of the data you're wanting to call here, I 
only tested with these two */
FROM [bigquery-public-data:noaa_gsod.gsod2016] AS a
JOIN [bigquery-public-data:noaa_gsod.stations] AS b
ON a.stn=b.usaf AND a.wban=b.wban
GROUP BY dte, mo, da)
WHERE dte >= (DATE_ADD('2016-12-31 00:00:00', -7, "DAY")) AND dte <= 
TIMESTAMP('2016-12-31 00:00:00') /* replace with your date */

我认为在标准SQL中，嵌套方式不同。

如果您想要跨站组合数据，请不要调用站点标识符，等等。