Calculated field
HEXBINX([amount], [other])
Sample data
| id | amount | units | score | other |
|---|---|---|---|---|
| 1 | -2.5 | 2 | -0.8 | 3 |
| 2 | 0 | 0 | 0 | 4 |
| 3 | 1.5 | 3 | 0.25 | 2 |
| 4 | 4 | 4 | 0.5 | 8 |
| 5 | 9 | 5 | 0.8 | 11 |
| 6 | 16 | 6 | 1 | 14 |
| 7 | null | 7 | null | 9 |
| 8 | 4 | 8 | -0.5 | null |
The same calculation as SQL
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN amount IS NULL THEN NULL WHEN pow(ux-x,2)+3*pow(uy-y,2)<=pow(sx-x,2)+3*pow(sy-y,2) THEN ux ELSE sx END AS result FROM centers ORDER BY id
Runs the example above.
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN amount IS NULL THEN NULL WHEN power(ux-x,2)+3*power(uy-y,2)<=power(sx-x,2)+3*power(sy-y,2) THEN ux ELSE sx END AS result FROM centers ORDER BY id
Written for this dialect; not run here.
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN amount IS NULL THEN NULL WHEN power(ux-x,2)+3*power(uy-y,2)<=power(sx-x,2)+3*power(sy-y,2) THEN ux ELSE sx END AS result FROM centers ORDER BY id
Written for this dialect; not run here.
Result
| id | amount | units | score | result |
|---|---|---|---|---|
| 1 | -2.5 | 2 | -0.8 | -3 |
| 2 | 0 | 0 | 0 | 0 |
| 3 | 1.5 | 3 | 0.25 | 1.5 |
| 4 | 4 | 4 | 0.5 | 4.5 |
| 5 | 9 | 5 | 0.8 | 9 |
| 6 | 16 | 6 | 1 | 16.5 |
| 7 | null | 7 | null | null |
| 8 | 4 | 8 | -0.5 | 3 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Rounding an axis independently does not select the nearest hexagonal center.
Naive SQL
SELECT round(amount) AS result FROM input ORDER BY id
id amount units score result 1 -2.5 2 -0.8 -3 2 0 0 0 0 3 1.5 3 0.25 2 4 4 4 0.5 4 5 9 5 0.8 9 6 16 6 1 16 7 null 7 null null 8 4 8 -0.5 4 Matches the calculation
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN amount IS NULL THEN NULL WHEN pow(ux-x,2)+3*pow(uy-y,2)<=pow(sx-x,2)+3*pow(sy-y,2) THEN ux ELSE sx END AS result FROM centers ORDER BY id
id amount units score result 1 -2.5 2 -0.8 -3 2 0 0 0 0 3 1.5 3 0.25 1.5 4 4 4 0.5 4.5 5 9 5 0.8 9 6 16 6 1 16.5 7 null 7 null null 8 4 8 -0.5 3
Signature
HEXBINX(number, number)