[postgis-users] PostGIS heatmap generation

Paul Ramsey pramsey at cleverelephant.ca
Mon Mar 17 16:26:20 PDT 2014

Server-side heat-mapping will get you closer, but still might find
"millions" to be a tall order to do interactively.


Depends on how much you can filter those 500M records down. You won't
be doing them all, that's for sure!


On Mon, Mar 17, 2014 at 3:41 PM, Paul Norman <penorman at mac.com> wrote:
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

More information about the postgis-users mailing list