0

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

Michael Entin
  • 7,189
  • 3
  • 21
  • 26
pushpendra pratap
  • 11
  • 2

1 Answers1

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.

Michael Entin
  • 7,189
  • 3
  • 21
  • 26
  • I'd like to have BQ support something like this Azure function - [https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/geo-polygon-to-s2cells-function()](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/geo-polygon-to-s2cells-function) - I have a use case where I want to get the cell IDs of a s2 grid (level 12) that overlap with a circle of radius X km centered at a location. Is there an easy way to do that currently in BQ? `jslibs` don't seem to support this (happy to be proven wrong here) – Sam Chats Aug 05 '21 at 12:43
  • Exactly. Also getting error when using carto-os (previously jslibs) in BQ. Currently, achieving the same functionality required me to write some JS UDF (have to do certain modifications in s2geometry.js from carto-os) and use it in BQ. – pushpendra pratap Aug 06 '21 at 17:16
  • 1
    There is now such a function, S2_COVERINGCELLIDS https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#s2_coveringcellids – Michael Entin Sep 27 '21 at 17:21
  • not in release notes yet? – Mikhail Berlyant Sep 27 '21 at 17:26
  • 1
    @MichaelEntin awesome!!! – pushpendra pratap Sep 30 '21 at 06:16