[postgis-users] fishnet for counting points

[Matthias Ludwig]

> Matthias,

> This is a different approach but it might be useful. This is untested, 
> but hopefully it will give you the idea:

> select
>     floor((st_x(a.the_geom)-b.minx)/1000)::integer as col,
>     floor((st_y(a.the_geom)-b.miny)/1000)::integer as row,
>     count(*) as cnt
>   from
>     (
>         select floor(min(st_x(the_geom)))::integer as minx,
>                floor(min(st_y(the_geom)))::integer as miny
>           from points ) as b,
>     points a
>   group by col, row
>   order by cnt desc;

> With the col, row, values and the count you can create the polygons 
> after the fact.

> -Steve W

Thanks for your idea Steve! Now it runs very very fast: ~500000 points with 10m polygons in ~30 seconds

DELETE FROM point_fishnet;
INSERT INTO point_fishnet(the_geom, count)
(
	SELECT	ST_SetSRID(
			ST_MakePolygon(
				ST_MakeLine(
					ARRAY[
						ST_MakePoint(minx + col * 10.0, miny + row * 10.0),
						ST_MakePoint(minx + (col + 1) * 10.0, miny + row * 10.0), 
						ST_MakePoint(minx + (col + 1) * 10.0, miny + (row + 1) * 10.0),
						ST_MakePoint(minx + col * 10.0, miny + (row + 1) * 10.0),
						ST_MakePoint(minx + col * 10.0, miny + row * 10.0)
					])
			), 900913
		) AS the_geom,
		cnt
	FROM
		(
			SELECT	floor((ST_X(points.the_geom) - min.minx) / 10.0)::integer AS col,
				floor((ST_Y(points.the_geom) - min.miny) / 10.0)::integer AS row,
				count(*) AS cnt
			FROM
				(
					SELECT 	floor(ST_XMin(the_geom))::integer AS minx,
						floor(ST_YMin(the_geom))::integer AS miny
					FROM	gars_900913_convex_hull
				) AS min,
				SELECT 	the_geom
				FROM 	points
			GROUP BY col, row
			ORDER BY cnt desc
		) AS koords,
		(
			SELECT 	floor(ST_XMin(the_geom))::integer AS minx,
				floor(ST_YMin(the_geom))::integer AS miny
			FROM	area_convex_hull
		) AS min
);