postgis-squaregrid-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 geometries SRID is assumed to be 3857

SELECT
  count(1) count,
  SUM(_size) size,
  AVG(_cat) cat,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY x) x,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY y) y`
FROM (
  SELECT
    _cat AS cat,
    _size AS size,
    ST_X(_geom) AS x,
    ST_Y(_geom) AS y,
    round(ST_X(_geom) / _r) * _r AS x_round,
    round(ST_Y(_geom) / _r) * _r AS y_round
  FROM
    _table
  WHERE
    ST_DWithin(ST_MakeEnvelope(_west, _south, _east, _north, 3857), _geom, 0.00001)
) agg GROUP BY x_round, y_round;