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).

Aggregating data to a regular grid has been inspired by the work of Javier Goizueta for Carto. Please read Javier’s blog post on aggregating data for faster map tiles.

Based on the current XYZ zoom level (_z) of the viewport we can define the grid resolution based on the length of the equator (40075016.68m). An additional _resolution factor can be introduced in order to control the spacing of grid cells at the given zoom level.

With a calculated grid cell size _r (width as well as height of a square grid cell) we can round sample point coordinates to the centroid of the containing grid cell.

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 have loaded sample points from the HM Land Registry Price Paid Dataset into a PostGIS database for testing. Using a cloud hosted instance of GEOLYTIX’ XYZ engine for which this algorithm was developed we are able to aggregate a large set of points to a grid.

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.

At first we create a surface tessallation from rectangles which represent hexagonal grid cells. The resolution _r is the width of a grid cell and the height is defined as:

_r — ( ( _r * 2 / sqrt(3) ) — _r) / 2;

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.