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).

  • This is the solution to my question: – Kuki Jan 5 at 7:00

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.

  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) )

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.