How do I calculate the centroid point of multiple longitude and latitude data points in BigQuery?

Question

I have a dataset with various longitude and latitude datapoints. I would like to develop a centroid or "average" of those longitude and latitude coordinates in bigquery at a specific level of granularity.

Example of Current Data:

ID	LONG	LAT
101	-71.23403	42.01979
101	-91.469621	44.867211
102	78.8952716	38.4022661
102	80.8518668	35.3152386

Desired Output (output centroid is made up)

ID	CENTROID_LONG	CENTROID_LAT
101	-71.23403	42.01979
102	-91.469621	44.867211

Where the values above are aggregated to a centroid lat and long number.

Answer 1

Consider below

select id, st_union_agg(st_geogpoint(long, lat)) points,
  st_centroid(st_union_agg(st_geogpoint(long, lat))) centroid
from your_table
group by id    

if applied to sample data in your question - output is

which is visualized as