Ufdjrw

I have a table in Bigquery with tracking data for Nascar drivers (dummy data for a project I am working on). The x and y coordinates are taken 10 times a second. The capture_frame signifies the current frame, and each sequential capture_frame should be 100 milliseconds apart because the data is taken every 100 ms.

I want to calculate each driver's speed per lap. I know how to do this in pandas but I think this is possible in bigquery. To calculate speed, I am looking at 2 rows before capture_frame and 2 rows after and then dividing by the difference in epoch time, which should be 400 milliseconds.

Here is an example of a few capture frames for 1 race for one driver for the first lap. There are a few hundred capture frames per lap and then 20 drivers mixed in as well, but it is easier to understand if we look at just one driver/race/lap.

+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| Race | Capture | Lap | Driver | … | X | Y | Epoch_time | Delta_dist | Curr_speed |
| | _frame | | | | | | | | |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 1 | 1 | Logano | …. | 2.1 | 1 | 1552089720 | NULL | Null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 2 | 1 | Logano | … | 2.2 | 1.1 | 1552089820 | NULL | Null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 3 | 1 | Logano | … | 2.22 | 1.2 | 1552089920 | 2.265921446 | 0.005664804 |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 4 | 1 | Logano | .. | 3.22 | 1.5 | 1552090020 | 3.124163888 | 0.00781041 |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 5 | 1 | Logano | .. | 4.22 | 1.8 | 1552090120 | NULL | null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 6 | 1 | Logano | .. | 5.22 | 1.9 | 1552090220 | NULL | null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+

The delta_dist for frame 3 is calculated by sqrt((4.22-2.1)^2 + (1.8-1)^2)/1 and the the curr_speed is that number divided by 400. The first/last 2 distances and speeds of the race will be null because there are no prior x or y coordinates which is okay as there isn't really any speed when you are .1 second from starting or stopping.

In pandas I would do (this is not great code as I am just bringing each driver and race in on its own):

#laps_per_race dictionary with num laps per race
for driver in driver_list:
 for race in race_list:
 driver_race_query = “SELECT * from nascar_xyz where driver=driver and Race=race”.format(driver=driver, race=race)
 df_entire_race = client.query(driver_race_query).to_dataframe()
 num_laps = laps_per_race[race]
 for lap in num_laps: 
 #get subset of dataframe just for this lap 
 df = df_entire_race.loc[df_entire_race['Lap'] == lap]
 df.sort_values(‘Epoch_time’, inplace=True)
 df[‘prev_x’] = df[‘X’].shift(2)
 df[‘next_x’] = df[‘X’].shift(-2)
 df[‘prev_y’] = df[‘Y’].shift(2)
 df[‘next_y’] = df[‘Y’].shift(-2)
 #this is just distance function sqrt((x2-x1)^2 + (y2-y1)^2)
 df['delta_dist'] = np.sqrt((df[‘X’].shift(-2) - df[‘X’].shift(2))**2 + (df[‘Y’].shift(-2) - df[‘Y’].shift(2))**2))

 #400.0 is the time actual difference
 df['Curr_speed'] = df['delta_dist']/400.0

I think in my sql query I either have to do a group by or partition by to because I want to look in each race by driver_id, then lap (if that level of abstraction makes sense). Maybe for the speed and looking capture_frames ahead I can do something with windowing (https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts) or something called lag which seems like the equivalent of .shift() in pandas.

You are in the right path. I'll take a public dataset of buses moving around Staten Island - and I'll use the geographical distance by looking at their lat,lon:

WITH data AS (
 SELECT bus, ST_GeogPoint(longitude, latitude) point
 , PARSE_TIMESTAMP('%Y%m%d %H%M%S',FORMAT('%i %06d', day, time)) ts
 FROM `fh-bigquery.mta_nyc_si.201410_bustime`
 WHERE day=20141014
 AND bus IN (7043, 7086, 7076, 2421, 7052, 7071)
)


SELECT * 
FROM (
 SELECT bus, ts, distance/time speed
 FROM (
 SELECT bus, ts
 , ST_DISTANCE(point, LAG(point, 3) OVER(PARTITION BY bus ORDER BY ts)) distance
 , TIMESTAMP_DIFF(ts, LAG(ts, 3) OVER(PARTITION BY bus ORDER BY ts), SECOND) time
 FROM data
 )
 WHERE time IS NOT null 
)
WHERE speed < 500