What the hex? Grid aggregations with PostGIS

I have previously written about cluster algorithms and hex grid algorithms for PostGIS. Today I want to present on the fly data aggregation algorithm for regular tessellation grids (square and hexagonal).

Sample points within the same grid cell are grouped and the position of the cluster coordinate can either be defined as the centroid, a point on the surface of a union geometry, or in this case as the 0.5 percentile of an ordered array for x and y values.

We use a bi-variate theme with the size of symbols representing the count of sample points in each cluster and the colour representing the average house price.

Aggregation to a grid with a regular hexagonal tessellation is somewhat more involved.

Every second row is shifted by half the rectangles width.

By checking whether the remainder of the round y coordinate is an odd number we know whether the row should be shifted.

The rounding defines a p0 coordinate which must be checked for neighbouring grid cells.

Even row p0 must be checked for neighbouring cells at the four quadrants.

Odd row p0 (shifted) must be checked for the adjacent cell to the left as well as the two adjacent cells which touch these.

Using PostgreSQL case statements we first check the odd rows in the following order. The cluster coordinate is the grid cell with the closest distance to the sample point coordinate.

Next we check the four adjacent quadrant cells of the even rows.

The complete select statement can be found in this GIST.

We implemented the algorithm in v2 of the XYZ spatial data interface and present a similar themed hex grid aggregation in the this fiddle.

I hope some of you will find this helpful.

Please check back for detailed XYZ v2 release notes soon.

Special thanks again to Javier for doing the ground work and to GEOLYTIX for funding this research and keeping XYZ a FOSS project.

Related posts:

During my years as a teacher, I did not watch much television other than the ABC morning line up of talk shows I recorded and caught up with when there was time. It’s been years since I followed a…

There is something about my DNA that abhors fads. I remember when I was working in Costa Rica and it felt like overnight, my people started showing up eating kale chips. I’m still not convinced that…

There was once a little boy, one who listened keenly and shouted incrementally. He gave a sense of personhood whilst still being a child. He had lost the path to himself as he found listless joys to…