Heading rate/bearing from GPS points in BigQuery

Question

I want to calculate heading rate / bearing from GPS points in Big Query. I've check all of the geography functions in BQ but found nothing helpful. Has anyone done it in BQ? My data consist of GpsLongitude, GpsLatitude and DateTime. I want to calculate change in heading rate / angle / bearing between each current and previous GPS point (each GPS point is 10 seconds apart from previous GPS point).

Answer 1

[Update] ST_Azimuth is available now https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_azimuth

There are no native functions in BigQuery yet, but there are some UDFs unofficially maintained by BigQuery GIS team:

gislib.sp.ST_Azimuth matches common ST_Azimuth, and gislib.sp.ST_InitialBearing returns same value in degrees.

SELECT
  gislib.sp.ST_InitialBearing( ST_GeogPoint(45,35), ST_GeogPoint(135,35) ),
  gislib.sp.ST_InitialBearing( ST_GeogPoint(-94.58, 39.1), ST_GeogPoint(-90.2, 38.63) )

Answer 2

This solved my problem:

 ATAN2(COS(GpsLatitude)*SIN(LAG(GpsLatitude) OVER (PARTITION BY Day ORDER BY DateTime)) -SIN(GpsLatitude)*COS(LAG(GpsLatitude) OVER (PARTITION BY Day ORDER BY DateTime))*COS(LAG(GpsLongitude) OVER (      PARTITION BY Day ORDER BY DateTime)-GpsLongitude),
  SIN(LAG(GpsLongitude) OVER (PARTITION BY Day ORDER BY DateTime)-GpsLongitude)*COS(LAG(GpsLatitude) OVER (PARTITION BY Day ORDER BY DateTime)))  AS Heading_radians