carto.H3_BOUNDARY returns null for some valid cell IDs

[lurifaxel]

Hi! Thank you so much for the toolbox, it really makes my life easier! :)

Now, on to the bug:

The carto.H3_BOUNDARY function returns null for some valid cell IDs.

Steps to reproduce the behavior:

1. Use Snowflake
2. Run these three queries:
   a. SELECT carto.carto.H3_BOUNDARY('870813269ffffff');
   b. SELECT carto.carto.H3_BOUNDARY('8708132c9ffffff');
   c. SELECT carto.carto.H3_BOUNDARY('870811a69ffffff');
3. All of these queries return NULL.

I was expecting all the queries above to return a GEOGRAPHy representing the H3 cell with the corresponding ID. I have inspected the cells with these IDs here, and they seem perfectly fine.

Thanks!

[Jesus89]

Hi @lurifaxel, thanks.

H3 library is returning a geography that throws an error in Snowflake's TO_GEOGRAPHY function:

select to_geography('POLYGON((14.711648031146114 63.277882025346415,14.712440922705934 63.26861190442848,14.73446836421859 63.26494482126122,14.755709705688332 63.27054633635922,14.755317756693326 63.27518180749664,14.752695851168275 63.28064279423593,14.732891492101386 63.28348535730845,14.732891492101393 63.28348535730845,14.711648031146114 63.277882025346415))')

Geography validation failed: Edge (14.752696,63.280643) -- (14.732891,63.283485) crosses edge (14.732891,63.283485) -- (14.711648,63.277882)

The current implementation returns NULL instead of the error, because TRY_TO_GEOGRAPHY is used instead.

In BigQuery, they provide the nice parameter make_valid, but Snowflake has no make-valid option so far. This is how it looks the polygon from BigQuery:

We will report this to the Snowflake team.

[Jesus89]

Hi.

Snowflake team mentioned that they will roll out the support of invalid shapes in Q1 next year. Until then, we need to fix the shapes before ingesting.

These two points are really close:

  [
    63.28348535730845,
    14.732891492101386
  ],
  [
    63.28348535730845,
    14.732891492101393
  ]

So a cleanup of approx duplicated points can make it work.