Support hexadecimal format for S2

[francois-baptiste]

Kepler.gl among other tools use hexadecimal format for s2_token. See: https://github.com/keplergl/kepler.gl/pull/800 https://raw.githubusercontent.com/uber-common/deck.gl-data/master/website/sf.s2cells.json

Would be nice if carto-spatial-extension S2 fonction generate directly hexadecimal formated s2_token.

For now on I use:

CREATE TEMP FUNCTION my_to_hex(x INT64) AS 
(
  (
    SELECT 
      STRING_AGG(FORMAT('%02x', x >> (byte * 8) & 0xff), '' ORDER BY byte DESC)
    FROM 
      UNNEST(GENERATE_ARRAY(0, 4)) AS byte
  )
);

CREATE TEMP FUNCTION latLngToId(latitude FLOAT64, longitude FLOAT64, level NUMERIC) RETURNS INT64 DETERMINISTIC LANGUAGE js
OPTIONS (library=["gs://bigquery-geolib/s2geometry.js"]) AS """
return S2.keyToId(S2.S2Cell.latLngToKey(latitude, longitude, level));
""";

SELECT my_to_hex(latLngToId(-74.006, 40.7128, 12))

[francois-baptiste]

Done! #106