Add Support for Bing Tile functions

[atiannicelli]

It would be super helpful to add functionality to support the bing tile functions to this API like Presto has as listed here: https://prestodb.io/docs/current/functions/geospatial.html#bing-tiles

As part of an effort to detect overlap of 2.9 buildings with water globally I find I need to bucket my queries on quadkey. This has worked great, but unfortunately it only works if the dataset already has quadkeys for all features. I would love the ability to generate bing tiles and quadkeys natively in the Sedona API.

The highest priority function for me is the geometry_to_bing_tiles function that produces a list of quadkeys that the geometry passed in touches.

For now I am having to define a UDF to generate quadkeys and call it to convert geometry to quadkeys.

[jiayuasu]

@atiannicelli Do you have an example of the UDF you implement? We can help you import that to Sedona.

[atiannicelli]

once I finalize it and test it I will post what I have

[mahic]

@atiannicelli any progress on this?

[atiannicelli]

I didn't implement it as a UDF. Instead I used geometry_to_bing_tiles to cut up the geometries into smaller pieces. This is the Athena (trino) query that I use to split the water bodies up into smaller geometries:

  SELECT
      id,
      TRY(ST_ASBINARY(st_intersection(bing_tile_polygon(tile), ST_GeomFromBinary(geometry)))) as geometry,
      TRY(bing_tile_quadkey(tile)) as quadkey
  FROM release.v{overture_table}
  CROSS JOIN UNNEST(TRY(geometry_to_bing_tiles(ST_GeomFromBinary(geometry), 13))) AS t(tile)
  WHERE
      theme = 'base'
      AND type = 'water'
      AND ST_ISVALID(ST_GeomFromBinary(geometry))

Unfortunately I still need to tweak this as some water body geometries (like lake Michigan) are too complex to apply to geometry_to_bing_tiles at that zoom level.