sql - 查找多个点之间的距离 - 纬度/经度

Question

我有一个大表，其中包含上车和下车的纬度/经度数据。这张表有几十万条记录，我想求出每次上下车的距离。

这可以在 BigQuery 中使用 SQL 来完成吗？

Answer 1

试试下面，对你来说应该是个好的开始

SELECT 
  orderid, 
  car_number, 
  ROUND(distance) AS distance, 
  ROUND(next_distance) AS next_distance
FROM JS(
  (
  // input table
  SELECT
    orderid,
    car_number,
    pickup_lon, 
    pickup_lat, 
    dropoff_lon, 
    dropoff_lat,
    LEAD(pickup_lon) OVER(PARTITION BY car_number ORDER BY orderid) AS next_pickup_lon,
    LEAD(pickup_lat) OVER(PARTITION BY car_number ORDER BY orderid) AS next_pickup_lat
  FROM 
    (SELECT 1 AS orderid, 1 AS car_number, -121.23200000000001 AS pickup_lon, 38.1964 AS pickup_lat, -117.48 AS dropoff_lon, 34.5894 AS dropoff_lat ),
    (SELECT 2 AS orderid, 1 AS car_number, -118.76 AS pickup_lon, 34.1445 AS pickup_lat, -122.26 AS dropoff_lon, 37.7606 AS dropoff_lat), 
    (SELECT 3 AS orderid, 2 AS car_number, -117.736 AS pickup_lon, 33.5761 AS pickup_lat, -117.19333333333333 AS dropoff_lon, 34.47484444444444 AS dropoff_lat)      
  ) ,
// input columns
orderid, car_number, pickup_lon, pickup_lat, dropoff_lon, dropoff_lat, next_pickup_lon, next_pickup_lat,
// output schema
"[{name: 'orderid', type: 'integer'},
{name: 'car_number', type: 'integer'},
{name: 'distance', type: 'float'},
{name: 'next_distance', type: 'float'}]",
// function
"function(r, emit){

  emit({
    orderid: r.orderid, car_number: r.car_number, 
    distance: dist(r.pickup_lon, r.pickup_lat, r.dropoff_lon, r.dropoff_lat),
    next_distance: dist(r.dropoff_lon, r.dropoff_lat, r.next_pickup_lon, r.next_pickup_lat)
    });

  function deg2rad(deg) {
    return deg * (Math.PI/180)
  }

  function dist(pickup_lon, pickup_lat, dropoff_lon, dropoff_lat) {
    var R = 3959; // Radius of the earth in miles
    var dLat = deg2rad(dropoff_lat-pickup_lat);  
    var dLon = deg2rad(dropoff_lon-pickup_lon); 
    var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
      Math.cos(deg2rad(pickup_lat)) * Math.cos(deg2rad(dropoff_lat)) * 
      Math.sin(dLon/2) * Math.sin(dLon/2); 
    var c = 2 * R * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
    return c;
  }

}"
)

结果是:

orderid car_number  distance    next_distance    
1       1           325.0       79.0     
2       1           317.0       NaN  
3       2            69.0       NaN  

From comments: My table schema is as such:

car_number     (string)
pu_datetime    (timestamp)
do_datetime    (timestamp)
pu_lat         (float) 
pu_long        (float) 
do_lat         (float) 
do_long        (float)   

Added to match your table schema

SELECT 
  pu_datetime, 
  car_number, 
  ROUND(distance) AS distance, 
  ROUND(next_distance) AS next_distance
FROM JS(
  (
  // input table
  SELECT
    pu_datetime,
    car_number,
    pu_lon, 
    pu_lat, 
    do_lon, 
    do_lat,
    LEAD(pu_lon) OVER(PARTITION BY car_number ORDER BY pu_datetime) AS next_pu_lon,
    LEAD(pu_lat) OVER(PARTITION BY car_number ORDER BY pu_datetime) AS next_pu_lat
  FROM 
    (SELECT timestamp('2016-07-01 13:00:00') AS pu_datetime, '1' AS car_number, -121.23200000000001 AS pu_lon, 38.1964 AS pu_lat, -117.48 AS do_lon, 34.5894 AS do_lat ),
    (SELECT timestamp('2016-07-02 10:00:00') AS pu_datetime, '1' AS car_number, -118.76 AS pu_lon, 34.1445 AS pu_lat, -122.26 AS do_lon, 37.7606 AS do_lat), 
    (SELECT timestamp('2016-07-03 11:00:00') AS pu_datetime, '2' AS car_number, -117.736 AS pu_lon, 33.5761 AS pu_lat, -117.19333333333333 AS do_lon, 34.47484444444444 AS do_lat)      
  ) ,
// input columns
pu_datetime, car_number, pu_lon, pu_lat, do_lon, do_lat, next_pu_lon, next_pu_lat,
// output schema
"[{name: 'pu_datetime', type: 'timestamp'},
{name: 'car_number', type: 'string'},
{name: 'distance', type: 'float'},
{name: 'next_distance', type: 'float'}]",
// function
"function(r, emit){

  emit({
    pu_datetime: r.pu_datetime, car_number: r.car_number, 
    distance: dist(r.pu_lon, r.pu_lat, r.do_lon, r.do_lat),
    next_distance: dist(r.do_lon, r.do_lat, r.next_pu_lon, r.next_pu_lat)
    });

  function deg2rad(deg) {
    return deg * (Math.PI/180)
  }

  function dist(pu_lon, pu_lat, do_lon, do_lat) {
    var R = 3959; // Radius of the earth in miles
    var dLat = deg2rad(do_lat-pu_lat);  
    var dLon = deg2rad(do_lon-pu_lon); 
    var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
      Math.cos(deg2rad(pu_lat)) * Math.cos(deg2rad(do_lat)) * 
      Math.sin(dLon/2) * Math.sin(dLon/2); 
    var c = 2 * R * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
    return c;
  }

}"
)