UDFs

BigQuery User Defined Functions

The following walks through useful geographical User Defined Functions (UDFs) for BigQuery.

• Calculate Bearing
• Generate Coordinate Linestring

Note: you can find other BQ UDFs in the BigQuery Repository, such as how to estimate current balance on a mortgage.

---

Bearing

This function takes two input coordinates and outputs the bearing in degrees between the starting latlong and the ending latlong, also referred to as the forward azimuth.

• Inputs: (LAT1 <FLOAT64>, LONG1 <FLOAT64>, LAT2 <FLOAT64>, LONG2 <FLOAT64>)

  SELECT
    `udf.udf_bearing`
      (33.820794, -84.581918, 34.443415, -82.982993)

  64.35905306271832

  

---

Generate Coordinate Linestring

This function takes two input coordinates with a distance in miles and generates all points between the coordinates by input distance.

• Inputs: (LAT1 <FLOAT64>, LONG1 <FLOAT64>, LAT2 <FLOAT64>, LONG2 <FLOAT64>, DIST_MI FLOAT64)

  SELECT 
    `udf.udf_intrpl_latlon`
      (33.820794, -84.581918, 34.013506, -83.795106, 5) INTRPL

  ROW	INTRPL.lat	INTRPL.long
  1	33.820794 33.841516 33.862238 33.88296 33.903683 33.924405 33.945127 33.965849 33.986571 34.007293	-84.581918 -84.498451 -84.414963 -84.331456 -84.247928 -84.164379 -84.080811 -83.997222 -83.913612 -83.829983

  

---

---

Other Functions

See more functions located in the BigQuery Repository:

• Calculating Interpolated Median
• Programmatically Finding Potential Abbreviations
• Calculating Mortgage Balance
  • Fixed Rate
  • Adjustable Rate
• Subsequent Substrings
• String within String
• Character Occurrence Count