Bigquery - Given a geo point and distance how to get a polygon around it

I had a problem to solve where given a center geo point and distance, I needed to extract a polygon around it.

Imagine any geo point: say: 41.6748248, -83.6882749 and I need to get a say an octagon around it using a distance of 50 meters e.g.

Answering my own question as I did not find any other method that I can use directly in BQ as is. (So far there is no native function in BQ for this.)

Solution

CREATE TEMPORARY FUNCTION get_point_at_a_distance(latitude float64, longitude float64, d int64, brng int64) as (
    (select ST_GEOGPOINT(((longitude* (ACOS(-1)/180)) + atan2(sin(brng* (ACOS(-1)/180))*sin(d/6371000)*cos(latitude* (ACOS(-1)/180)),cos(d/6371000)-sin(latitude* (ACOS(-1)/180))*sin(latitude* (ACOS(-1)/180))))* (180/ACOS(-1)), 
    (asin(sin(latitude* (ACOS(-1)/180))*cos(d/6371000) + cos(latitude* (ACOS(-1)/180))*sin(d/6371000)*cos(brng* (ACOS(-1)/180))))* (180/ACOS(-1))))
); 

CREATE TEMPORARY FUNCTION get_polygon(latitude float64, longitude float64, distance int64) as (

(select ST_MAKEPOLYGON(ST_MAKELINE(ARRAY_AGG(geo_point))) 
from 
(
select get_point_at_a_distance(latitude, longitude, distance, 0) as geo_point union all 
select get_point_at_a_distance(latitude, longitude, distance, 45) as geo_point  union all 
select get_point_at_a_distance(latitude, longitude, distance, 90) as geo_point  union all 
select get_point_at_a_distance(latitude, longitude, distance, 135) as geo_point  union all 
select get_point_at_a_distance(latitude, longitude, distance, 180) as geo_point  union all 
select get_point_at_a_distance(latitude, longitude, distance, 225) as geo_point  union all 
select get_point_at_a_distance(latitude, longitude, distance, 270) as geo_point  union all 
select get_point_at_a_distance(latitude, longitude, distance, 315) as geo_point 
)));

select get_polygon(41.6748248, -83.6882749, 50)```


Confirm with: https://www.keene.edu/campus/maps/tool/
Try to plot the output: 
-83.6887005865171, 41.6751427574177
 -83.6888769116458, 41.6748247984293
 -83.6887005865171 ,41.6745068410117
 -83.6882749, 41.674375139197
 -83.6878492134828, 41.6745068410116
 -83.6876728883542, 41.6748247984293
-83.6878492134828, 41.6751427574177
 -83.6882749, 41.675274460803
 -83.6887005865171, 41.6751427574177