[postgis-users] PostGIS heatmap generation

[Sandro Santilli]

On Thu, Mar 20, 2014 at 04:04:25PM -0700, Paul Norman wrote:
> > Sent: Tuesday, March 18, 2014 12:41 AM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] PostGIS heatmap generation
> > 
> > Pre-aggregation would surely help, big problem being amount
> > of records that need to be read from disk. You could also look
> > at postgis raster as the aggregation type.
> 
> Any suggestions on how to use postgis raster for aggregation?
> 
> To aggregate with SQL I was thinking I'd use something like this:
> 
> CREATE TABLE agg1 AS 
>   SELECT 
>     COUNT(*), ST_SnapToGrid(ST_Transform(geom, 3857), 10, 10),
>     date_trunc('hour', tstamp)
>   FROM points
>   GROUP BY ST_SnapToGrid(ST_Transform(geom, 3857), 10, 10),
>     date_trunc('hour', tstamp);
> 
> Of course, I need to then render it with something

Forget raster, for a start. Your query is almost there, only
needs an aggregate for the geometry. What about the centroid
of the collection ?

 CREATE TABLE agg1 AS
   SELECT
     COUNT(*), ST_Transform(ST_Centroid(ST_Collect(geom)), 3857),
     date_trunc('hour', tstamp)
   FROM points
   GROUP BY ST_SnapToGrid(ST_Transform(geom, 3857), 10, 10),
     date_trunc('hour', tstamp);


--strk;