[postgis-users] fishnet for counting points

[Matthias Ludwig]

Hello,

for a statistical analysis I need to create a polygon grid to count the points inside. The analysis area measures about 15x15km. At the moment I use the code below. It's functioning but is slow for high point counts(>5000-100000 points) and small polygon sizes (5-20m). Because the points are clustered and the big area size it's not possible to insert all polygons and delete those which are empty. Presently one polygon is inserted for every point in the first step. In the second step dublicated polygons where deleted. How can I insert one polygon in a specific place?

CREATE TABLE point_fishnet (gid serial not null primary key, area text, count integer default 0);
SELECT addgeometrycolumn('point_fishnet','the_geom', 900913, 'POLYGON', 2);

INSERT INTO point_fishnet(the_geom, area)
	(
		SELECT ST_Translate(bb.the_geom, x_koord, y_koord) AS the_geom, 
			'germany'
		FROM	generate_series(
				(
					SELECT 	floor(min(ST_X(the_geom)))::integer
					FROM 	points
				),
				(
					SELECT 	ceil(max(ST_X(the_geom)))::integer
					FROM 	points			
				),
				1000	--Offset = Polygonsize
			) AS x_koord,
			generate_series(
				(
					SELECT 	floor(min(ST_Y(the_geom)))::integer 
					FROM 	points
				),
				(
					SELECT 	ceil(max(ST_Y(the_geom)))::integer
					FROM 	points
				),
				1000	--Offset = Polygonsize
			) AS y_koord,
			(
				SELECT ST_MakeEnvelope(0, 0, 1000, 1000, 900913) AS the_geom
			) AS bb,
			(
				SELECT 	the_geom
				FROM 	points
			) AS point
		WHERE 	ST_Intersects(
				ST_Translate(bb.the_geom, x_koord, y_koord), 
				point.the_geom
			)
	);

-- delete dublicates
DELETE FROM point_fishnet
WHERE gid NOT IN
(
	SELECT min(gid)
	FROM point_fishnet
	GROUP BY the_geom HAVING count(*) >= 1
);