Giving a Spatial GEOGPOINT in BigQuery, How can we plot X points surrounded equally from the center point

In BigQuery assume I have a GEOPOINT Datatype. Say for eg ST_GEOGPOINT(-122, 37) How can I find say 10 GEOGPOINTS that make the Outer Circle with the radius as 100 km.

The requirement is for a Looker Dashboard, where I have the GPS Data of Devices at the lowest level and we are tracking it's activity.

Now we will create an aggregated table at City Level as following data with

• City & Age Bucket as the Grain the table.
• Here we calculate an aggregated central GPS point of all Devices under the Activity Bucket using ST_CENTROID_AGG function in BigQuery

Here while plotting, the I would like to see all Florida being plotted at the central point with concentric circles around it. For Texas, USA here it needs 6 points in the outside circle For Florida, USA here it needs 7 points in the outside circle

How can we have a BigQuery function where I can get these GEOGPOINTS (either pre-calcualted in agg table or as a runtime function) to plot in a better way

I tired to play around the following article

https://towardsdatascience.com/spatial-binning-with-google-bigquery-d118afba6273

And could get some approximate cirlces, which will be looking oval due to how Lat & Long is shown in Map and get skewed at either north or south poles

--
-- Table Function to Generate Circular Points for an input (Will be Oval in Shape)
--
CREATE OR REPLACE TABLE FUNCTION looker_iot_model.approx_circle_points(
  latitude FLOAT64, longitude FLOAT64, circle FLOAT64, points INTEGER)
AS (
SELECT
  seq,
  ST_GEOGPOINT(longitude + ((SIN(ACOS(-1)*seq/points))*circle/9), latitude + ((COS(ACOS(-1)*seq/points))*circle/10)) AS geo_point
FROM UNNEST(GENERATE_ARRAY(-1*(points-1),(points-1),2)) as seq
);

Now If I run

1. For near equator, it looks as follows

select * FROM looker_iot_model.approx_circle_points(37.7749, -122.4194, 12, 12)

2. For near poles, it looks as follows

select * FROM looker_iot_model.approx_circle_points(71, 42, 12, 12)

ST_Buffer(central_point, 100*1000, 10/4) builds a polygonized loop around the central point. The second argument is radius (in meters), the third one is number of points per quarter. It returns POLYGON with 10 vertices (that look like 11 due to first and last one repeated in POLYGON loop)

To convert it to list of points we would need ST_DumpPoints that is not available in BigQuery, let's build poor-man version of it using text manipulation:

select st_geogfromtext(concat("POINT(", p, ")")) AS point
from unnest(split(
  trim(
    st_astext(st_buffer(ST_GEOGPOINT(-122, 37), 100e3,
                        num_seg_quarter_circle => 10/4)),
    "POLYGON()"),
  ",")) p with offset o
where o <> 0;

This builds a buffer, converts it to text, strips "polygon((..))" wrappings, splits the remaining points, and converts them back to geographies omitting the first point (that is repeated at the end of polygon definition).