[postgis-users] PostGIS heatmap generation

[Paul Norman]

I'm looking at heatmap generation with PostGIS and was wondering if anyone
had any suggestions.

To give some specifics to the problem

- I have a dataset of 100 million to 500 million points 
  across the US

- Each point has a date associated with it

- The data covers the US

- The data is to be loaded in from text files

- Everything has to be able to run from the command line 
  without user interaction

- The data will be viewed on a state level down to a local 
  level through a webmap

I want to make heatmaps for questions like "points in the 
last week" or "points in the last month"

I've looked at a few common suggestions for heatmaps

Leaflet.heatmap: Millions of points is out of the question

QGIS heatmap plugin: As far as I can tell, this requires user 
interaction and is out of the question.

R: I don't know R, but I think it'd suffer the same problems as 
Mapnik does, below.

Mapnik (https://www.mapbox.com/blog/colorize-alpha-image-filter/): 
I got this working with some test data (OSM nodes table) and it 
works up to a point. When I start requesting areas with hundreds 
of thousands of points it starts to bog down. I'm not sure if this 
exclusively on the query side or also on the rendering side.

One potential problem is that at low zooms I'd be returning millions 
of rows from PostgreSQL.

Has anyone got any suggestions for how to efficiently render heatmaps 
from datasets this large?  I was thinking of two tables, one with the 
raw data for high zooms and one pre-aggregated into space and time 
bins (e.g. requests in a given box in a given time range) for use 
in lower zooms. The aggregation operation would obviously be slow, but
I'd only have to do it once.