postgis-hexgrid-aggregation.sql

# _z is the XYZ tiles zoom level of the request
# _resolution is a resolution factor applied to determine the grid spacing
# _r = parseInt(40075016.68 / Math.pow(2, _z) * _resolution)
# _size is a numeric value for the calculation of a cell's size property
# _cat is a numeric value for the calculation of a cell's graduated colour property
# _table is the table name
# _geom is the geometry field of points in the table
# The _width of a hexagon is equal to _r
# The _height of a hexagon envelope is _r - ((_r * 2 / sqrt(3)) - _r) / 2;
# The geometries SRID is assumed to be 3857

WITH
first as (
  SELECT
    id,
    _cat AS cat,
    _size AS size,
    _geom AS geom,
    ST_X(_geom) x,
    ST_Y(_geom) y,
    ((ST_Y(_geom) / _height)::integer % 2) odds,
    CASE
      WHEN ((ST_Y(_geom) / _height)::integer % 2) = 0 THEN
        ST_Point(
          round(ST_X(_geom) / _width) * _width,
          round(ST_Y(_geom) / _height) * _height)
      ELSE
        ST_Point(
          round(ST_X(_geom) / _width) * _width + _width/2,
          round(ST_Y(_geom) / _height) * _height)
      END p0 
  FROM
    _table
  WHERE
    ST_DWithin(ST_MakeEnvelope(_west, _south, _east, _north, 3857), _geom, 0.00001)
),
second as (
  SELECT
    id,
    cat,
    size,
    
    CASE
      WHEN odds = 0 THEN
        CASE
          WHEN x < ST_X(p0) THEN
            CASE
              WHEN y < ST_Y(p0) THEN
                CASE
                  WHEN (geom <#> ST_Translate(p0, -_width/2, -_height)) < (geom <#> p0)
                    THEN ST_SnapToGrid(ST_Translate(p0, -_width/2, -_height), 1)
                  ELSE ST_SnapToGrid(p0, 1)
                END
              ELSE
                CASE
                  WHEN (geom <#> ST_Translate(p0, -_width/2, _height)) < (geom <#> p0)
                    THEN ST_SnapToGrid(ST_Translate(p0, -_width/2, _height), 1)
                  ELSE ST_SnapToGrid(p0, 1)
                END
            END
          ELSE
            CASE
              WHEN y < ST_Y(p0) THEN
                CASE
                  WHEN (geom <#> ST_Translate(p0, _width/2, -_height)) < (geom <#> p0)
                    THEN ST_SnapToGrid(ST_Translate(p0, _width/2, -_height), 1)
                  ELSE ST_SnapToGrid(p0, 1)
                END
              ELSE
                CASE
                  WHEN (geom <#> ST_Translate(p0, _width/2, _height)) < (geom <#> p0)
                    THEN ST_SnapToGrid(ST_Translate(p0, _width/2, _height), 1)
                  ELSE ST_SnapToGrid(p0, 1)
                END
            END
        END
      ELSE
        CASE
          WHEN x < (ST_X(p0) - _width/2) THEN
            CASE
              WHEN y < ST_Y(p0) THEN
                CASE
                  WHEN (geom <#> ST_Translate(p0, -_width/2, -_height)) < (geom <#> ST_Translate(p0, -_width, 0))
                    THEN ST_SnapToGrid(ST_Translate(p0, -_width/2, -_height), 1)
                  ELSE ST_SnapToGrid(ST_Translate(p0, -_width, 0), 1)
                END
              ELSE
                CASE
                  WHEN (geom <#> ST_Translate(p0, -_width/2, _height)) < (geom <#> ST_Translate(p0, -_width, 0))
                    THEN ST_SnapToGrid(ST_Translate(p0, -_width/2, _height), 1)
                  ELSE ST_SnapToGrid(ST_Translate(p0, -_width, 0), 1)
                END
            END
          ELSE
            CASE
              WHEN y < ST_Y(p0) THEN
                CASE
                  WHEN (geom <#> ST_Translate(p0, -_width/2, -_height)) < (geom <#> p0)
                    THEN ST_SnapToGrid(ST_Translate(p0, -_width/2, -_height), 1)
                  ELSE ST_SnapToGrid(p0, 1)
                END
              ELSE
            CASE
              WHEN (geom <#> ST_Translate(p0, -_width/2, _height)) < (geom <#> p0)
                THEN ST_SnapToGrid(ST_Translate(p0, -_width/2, _height), 1)
              ELSE ST_SnapToGrid(p0, 1)
            END
        END
    END
    END as point    
    
  FROM first
)

SELECT
  count(1) count,
  SUM(size) size,
  AVG(cat) cat,
  ST_X(point) x,
  ST_Y(point) y`
FROM second GROUP BY point;