S2 Geo Functions in BigQuery?

Question

I'm just wondering if there is any plans to add functions for the S2 geo library into BigQuery?

Seems like surely lots of people have lat-long data in BQ - some s2 functions to got to cells and back etc would be super useful surely - happy to file a feature request if there is not already one.

Best links i could find as fyi:

http://blog.christianperone.com/2015/08/googles-s2-geometry-on-the-sphere-cells-and-hilbert-curve/ https://godoc.org/github.com/golang/geo/s2 https://docs.google.com/presentation/d/1Hl4KapfAENAOf4gv-pSngKwvS_jwNVHRPZTTDzXXn6Q/view

I do see some javascript libraries but not sure if they can be used in UDF's

https://www.npmjs.com/package/s2-geometry https://github.com/mapbox/node-s2

I wonder is it the sort of thing that one could do just in UDF's?

Answer 1

BigQuery team is interested in adding some sort of support for geo-spatial operations - so the more information you can provide about your use cases - will better inform the planning and prioritization. In the meantime, S2 library is indeed used extensively inside Google with much success, including as UDFs inside BigQuery. I was not aware of public port to JavaScript that you linked above - but I believe it should be possible to take advantage of it inside BigQuery JS UDFs.

Update: BigQuery GIS has shipped: https://cloud.google.com/bigquery/docs/gis-intro

Answer 2

As Mosha said, it is possible to take advantage of BigQuery JS UDFs.

You can take a look at the github project jslibs or use directly the code below as an example.

SELECT `jslibs.s2.ST_S2`(longitude_centroid, latitude_centroid,5)
FROM `bigquery-public-data.worldpop.population_grid_1km`
WHERE last_updated = "2017-01-01"
LIMIT 1000

disclaimer: I am one of the contributors to this project.