[postgis-users] grouing points on a grid

[Sandro Santilli]

On Wed, Mar 20, 2013 at 12:58:08PM +0100, toni hernández wrote:
> Hello,
> 
> I have 1 million points and I need to group them on a grid.
> I also need to know how many of thouse points are on each cell of the grid.
> 
> 
> My first thought was some SQL like this one:
> 
> select count(*) as n , st_snaptogrid(geom, size)
> from myPoints group by st_snaptogrid(geom, size)
> 
> I thought as well about indexing myPoints table and even do some
> clustering (Cluster geom on myPoints)
> 
> Which is the best way to do this query?

I think the SnapToGrid way is the fastest you can currently run
to cluster your points. Index won't help as you're going to scan
the whole table anyway.

With limited performance penalty you may return something nicer
than the snapped point, for example a convex hull or the centroid
of the collection (to avoid a "griddy" look).

--strk;