Hexagon Grid Problems

[jgoizueta]

Recently there was some surprise because hexagon grid maps that worked with CARTO.js caused problems with CARTO VL when ported by @elenatorro

This is a case of what Builder (and the old Editor) does to cluster spatially into hexagon or square grids by using complex SQL to generate a grid and then join the data to it.

They used this kind of queries:

WITH hgrid AS (
  SELECT CDB_HexagonGrid(
    ST_Expand(!bbox!, CDB_XYZ_Resolution(8) * 3),
    CDB_XYZ_Resolution(8) * 3
  ) AS cell
),
grid AS (
    SELECT
      ST_SetSRID(hgrid.cell, 3857) AS the_geom_webmercator,
      count(1) AS agg_value,
      avg(agg_value_int) AS agg_value_density,
      min(cartodb_id) AS cartodb_id
    FROM hgrid, (SELECT *, CAST(${aggColumnName} AS INT) AS agg_value_int FROM ${tableName}) i
    WHERE ST_Intersects(i.the_geom_webmercator, ST_SetSRID(hgrid.cell, 3857)) GROUP BY hgrid.cell
  )
SELECT *, the_geom_webmercator as the_geom FROM grid

The problem is that CARTO VL (unlike CARTO.js) requests global metadata from the tiler, and to compute it, the tiler executes the query in the context of the whole dataset extents (which affects the !bbox!, etc.) and fails because a huge grid would need to be created (the CDB_HexagonGrid and CDB_RectangleGrid abort trying to generate such grids that would cause memory problems to PG and would be impossible to join with the data).

CARTO.js would also fail if trying to fetch a low zoom level tile, but it doesn't fail instantiating the map because it doesn't request metadata stats.

Note also a more general problem: this query is zoom-level dependent, so whatever stats are used to adjust visualization (e.g. ramps, percentiles, ...) will not be valid for all zoom levels. Note also that the relation of zoom level with the displayed map is not as predictable in CARTO VL as it's in CARTO.js, because of the use of overzooming.

There's a workaround for the problem that prevents this visualization: we can change the SQL code so that we use a coarser grid for low zoom levels:

WITH hgrid AS (
  SELECT CASE
    WHEN CDB_ZoomFromScale(!scale_denominator!) > 4 THEN
      CDB_HexagonGrid(
        ST_Expand(!bbox!, CDB_XYZ_Resolution(8) * 3),
        CDB_XYZ_Resolution(8) * 3
      )
    ELSE
      CDB_HexagonGrid(
          ST_Expand(!bbox!, CDB_XYZ_Resolution(2) * 3),
          CDB_XYZ_Resolution(2) * 3
        )
  END AS cell
),
grid as (
    SELECT
      ST_SetSRID(hgrid.cell, 3857) as the_geom_webmercator,
      count(1) as agg_value,
      avg(agg_value_int) as agg_value_density,
      min(cartodb_id) as cartodb_id
    FROM hgrid, (SELECT *, CAST(${aggColumnName} as INT) as agg_value_int FROM ${tableName}) i
    WHERE ST_Intersects(i.the_geom_webmercator, ST_SetSRID(hgrid.cell, 3857)) GROUP BY hgrid.cell
  )
SELECT *, the_geom_webmercator as the_geom FROM grid

But one thing to consider is to augment the aggregations of the Maps API so that we can use them to create this kind of grids. We would need to add two new placements: square and hexagon, to generate polygon aggregate geometries instead of points. (the first would be easy with the current implementation, the second is trickier). We'd also need a way to handle this in the Viz language, similarly to resolution. This would have a number of benefits:

• It would be more efficient: we would save the intersect join in the queries, and, more importantly, empty cells would be omitted.
• Metadata would not be zoom-dependent (we use pre-aggregation metadata)
• User wouldn't need to write complex SQL

So, in summary:

• [ ] Let's discuss/evaluate the convenience of implementing the hexagon/rectangle aggregations
• [ ] Should we add some information about the utility and perils of CDB_HexagonGrid in the documentation?

[jgoizueta]

Note: there is and additional unrelated problem with the complex queries above, caused by a backend bug, which is fixed by https://github.com/CartoDB/Windshaft-cartodb/pull/981

[davidmanzanares]

Hey, so... The original issue is solved? Has it been tested? I can test it otherwise.