4

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?

Michael Entin
  • 7,189
  • 3
  • 21
  • 26
andrewm4894
  • 1,451
  • 4
  • 17
  • 37

3 Answers3

6

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

Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
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.

Francois BAPTISTE
  • 153
  • 9
  • This is what I need exactly. May I learn how can I implement the JS file in the BQ, and use the `jslibs.s2.ST_S2` as you shown? – emily.mi Nov 27 '20 at 18:36
  • 1
    `jslibs.s2.ST_S2` is a public JS UDF, so it should run direcly from your BigQuery account. To include a JS file in bigquery, look at the following documentation. https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions#including-javascript-libraries – Francois BAPTISTE Nov 28 '20 at 02:30
2

BigQuery geospatial functions added two native functions for working with S2 cells:

S2_CELLIDFROMPOINT(point_geography[, level => cell_level])

S2_COVERINGCELLIDS(
    geography
    [, min_level => cell_level]
    [, max_level => cell_level]
    [, max_cells => max_cells]
    [, buffer => buffer])

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

That said, one typically should use regular ST_* set of functions for most geospatial tasks, but S2 functions could be helpful for advanced workload or interoperability with other systems using S2.

Michael Entin
  • 7,189
  • 3
  • 21
  • 26