In BigQuery, how can I find the S2_ID at level 16 corresponding to each (latitude, longitude)?

Question

I am working with the Chicago-taxi-trips dataset and I want to find the average fare per pick up location S2_ID at level 16 for the given date, based on the pickup time of the trip

S2_ID

Chicago-taxi-trips dataset

Answer 1

Update:

There is now a native BigQuery function,

S2_CELLIDFROMPOINT(point_geography[, level => cell_level])

https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#s2_cellidfrompoint

---

Carto provides a set of UDF functions for geospatial projects, including working with S2. See their introduction here: https://carto.com/blog/spatial-functions-bigquery-uber/

The UDFs are available in a public jslibs project. To compute S2 cellid from a point use code like

SELECT jslibs.s2.ST_S2(
           ST_GeogPoint(<longitude>, <latitude>),
           <cell_level>);

Note that you might need to replace s2 dataset with a regional version, depending on where you want to run the queries.

That said, it would be great if you could describe the use case for S2 cells in BigQuery? BigQuery team is interested in how these are used and might consider adding native S2 functionality in the future.