在gdelt-bq.full dataset，BigQuery中找到组中事件之间的最大距离

Question

我需要找到跨越的最长距离，所有，每个国家的事件点，在gdelt-bq.full:events数据集中。要想获得有关国家的信息，就需要与gdelt-bq:extra.countryinfo合作。现在我有了一张桌子：

SELECT *
FROM [gdelt-bq:full.events] events JOIN
     [gdelt-bq:extra.countryinfo] countries
     ON events.Actor1CountryCode = countries.iso3

困难是，总共大约有50k个事件，而一个组内的最大活动是15K (对于美国)，我需要首先计算一个组内的所有距离(一个国家的事件)，但是所有事件的纬度和经度都在一列中。所以我需要创建所有的事件对来计算它们，并找到最长的，对于最大的组来说，是15k集的两个组合的数目，大约是11 15k。

顺便说一下，我在这里找到了一个计算距离的haversine函数，HAVERSINE distance in BigQuery?

也许我的方法出了什么问题？任何帮助都很感激。

Answer 1

下面是用于BigQuery标准SQL的

在这里，代码不是专注于事件，而是脱离了lat，这大大减少了需要处理的量，从而避免了著名的“资源超过.”

#standardSQL
CREATE TEMPORARY FUNCTION distance(lat1 FLOAT64, lon1 FLOAT64, lat2 FLOAT64, lon2 FLOAT64)
RETURNS FLOAT64 AS ((
WITH constants AS (
  SELECT 0.017453292519943295 AS p
) 
SELECT ROUND(12742 * ASIN(SQRT(
  0.5 - COS((lat2 - lat1) * p)/2 + 
  COS(lat1 * p) * COS(lat2 * p) * 
  (1 - COS((lon2 - lon1) * p))/2)), 2)
FROM constants
));
WITH events AS (
  SELECT * 
  FROM `gdelt-bq.full.events` 
  WHERE NOT(IFNULL(ActionGeo_Lat,0)=0 OR IFNULL(ActionGeo_Long,0)=0)
),
geos AS (
  SELECT DISTINCT ActionGeo_CountryCode code, ActionGeo_Lat lat, ActionGeo_Long long
  FROM events
)
SELECT c.code code, country, geo[safe_offset(0)].*
FROM (
  SELECT code, ARRAY_AGG(STRUCT(dist, lat1, long1, lat2, long2) ORDER BY dist DESC LIMIT 1) AS geo
  FROM (
    SELECT e1.code code, e1.Lat lat1, e1.Long long1, e2.Lat lat2, e2.Long long2,
      distance(e1.Lat, e1.Long, e2.Lat, e2.Long) dist
    FROM geos e1
    JOIN geos e2
    ON  e1.code = e2.code
    AND e1.Lat > e2.Lat
  )
  GROUP BY code
) c
JOIN `gdelt-bq.extra.countryinfo` countries
ON c.code = countries.iso
-- ORDER BY dist DESC

作为输出的一个例子(“耐心-我花了12到13分钟才完成以上查询)-按距离计算的前5位国家如下：

code    country                 dist        lat1    long1       lat2    long2    
US  United States               13468.78    18.1131 -65.3531    8.7318  167.74   
MP  Northern Mariana Islands    10508.24    16.0    146.0       -20.0   57.0     
PF  French Polynesia             9403.5     15.7833 111.2   6.339869976043701   -162.6750030517578   
LS  Lesotho                      8741.97    47.2333 9.51667     -29.5   28.5     
RS  Serbia                       8075.75    54.4922 168.12      43.4151 39.9248  

注意:这里使用的是距离函数，您可以使用您选择的任何函数-例如，您可以使用您在问题中提到的HAVERSINE距离函数(无关紧要)。

此外，还可以通过将ARRAY_AGG()中的限制1更改为限制3或任何您想要的数字来控制输出中所需的最高距离。