在组中查找第一个行和其余行之间的距离。

I am trying to find out the distance between first record and the remaining 3 records of a group that is grouped by Id1. For example, distance between (33.6008949,-83.83803) and (33.604248,-83.86729); distance between (33.6008949,-83.83803) and (33.60586,-83.8711) and distance between (33.6008949,-83.83803) and (33.6043777,-83.86624).

我正在尝试找出第一个记录和被Id1分组的一个组的其余3个记录之间的距离。例如，(33.6008949，-83.83803)和(33.604248，-83.86729);(33.6008949，-83.83803)和(33.60586，-83.8711)和(33.6008949，-83.83803)和(33.6043777，-83.86624)之间的距离。

    Row  Id1     Id2          StartTime               StopTime                Latitude    Longitude  DateTime        
    1     71     34         2016-11-21 00:41:05 UTC 2016-11-21 00:47:06 UTC 33.6008949  -83.83803    2016-11-21 00:43:42 UTC     
    2     71     44         2016-11-21 00:54:55 UTC 2016-11-21 00:56:28 UTC 33.604248     -83.86729  2016-11-21 00:55:18 UTC     
    3     71     45         2016-11-21 02:08:17 UTC 2016-11-21 02:09:52 UTC 33.60586      -83.8711   2016-11-21 02:09:03 UTC     
    4     71     67         2016-11-21 02:16:02 UTC 2016-11-21 02:17:21 UTC 33.6043777  -83.86624    2016-11-21 02:16:28 UTC

For distance calculation between the points, I use the below function:

对于点之间的距离计算，我使用下面的函数:

  from math import radians, cos, sin, asin, sqrt
  def haversine(lon1, lat1, lon2, lat2):
      lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])
      dlon = lon2 - lon1 
      dlat = lat2 - lat1 
      a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
      c = 2 * asin(sqrt(a)) 
      km = 6367 * c
      return km

1 个解决方案

#1

Try below

试试下面的

#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 12742 * ASIN(SQRT(
  0.5 - COS((lat2 - lat1) * p)/2 + 
  COS(lat1 * p) * COS(lat2 * p) * 
  (1 - COS((lon2 - lon1) * p))/2))
FROM constants
));

WITH YourTable AS (
  SELECT 1 AS Row, 71 AS Id1, 34 AS Id2, '2016-11-21 00:41:05 UTC' AS StartTime, '2016-11-21 00:47:06 UTC' AS StopTime, 33.6008949 AS Latitude, -83.83803 AS Longitude, '2016-11-21 00:43:42 UTC' AS DateTime UNION ALL     
  SELECT 2 AS Row, 71 AS Id1, 44 AS Id2, '2016-11-21 00:54:55 UTC' AS StartTime, '2016-11-21 00:56:28 UTC' AS StopTime, 33.604248 AS Latitude, -83.86729 AS Longitude, '2016-11-21 00:55:18 UTC' AS DateTime UNION ALL     
  SELECT 3 AS Row, 71 AS Id1, 45 AS Id2, '2016-11-21 02:08:17 UTC' AS StartTime, '2016-11-21 02:09:52 UTC' AS StopTime, 33.60586 AS Latitude, -83.8711 AS Longitude, '2016-11-21 02:09:03 UTC' AS DateTime UNION ALL     
  SELECT 4 AS Row, 71 AS Id1, 67 AS Id2, '2016-11-21 02:16:02 UTC' AS StartTime, '2016-11-21 02:17:21 UTC' AS StopTime, 33.6043777 AS Latitude, -83.86624 AS Longitude, '2016-11-21 02:16:28 UTC' AS DateTime 
)
SELECT *, 
  distance(Latitude, Longitude, FIRST_VALUE(Latitude) OVER(PARTITION BY Id1 ORDER BY Id2), FIRST_VALUE(Longitude) OVER(PARTITION BY Id1 ORDER BY Id2)) AS dist
FROM YourTable
ORDER BY Id1, Id2