aggregate hex_grid.sql

set search_path to "sandbox","public";
DROP VIEW IF EXISTS grid_test_001;
CREATE VIEW grid_test_001 as (
SELECT g.gid, g.the_geom, count(*) FROM hex_grid_001 g 
JOIN n1000_turisthytte p 
ON g.the_geom && st_transform(p.wkb_geometry,4326)
GROUP BY g.gid, g.the_geom
);

CREATE TABLE sandbox.t_grid_n1000_byggpkt_0125 AS 
 SELECT g.gid,
    g.the_geom,
    count(*) AS count
   FROM sandbox.hex_grid_0125 g
   JOIN sandbox.n1000_bygningspunkt p ON g.the_geom && st_transform(p.wkb_geometry, 4326)
  GROUP BY g.gid, g.the_geom;

hexagon-grid postgis.sql

--bug: overshooting y-max a lot. Need adjustments on the nrow calculations
--Thanks to: http://rexdouglass.com/spatial-hexagon-binning-in-postgis/ 
SET search_path TO "sandbox","public";
DROP TABLE IF EXISTS hex_grid;
CREATE TABLE hex_grid (gid serial not null primary key);

SELECT addgeometrycolumn('hex_grid','the_geom', 0, 'POLYGON', 2);

CREATE OR REPLACE FUNCTION genhexagons(width float, xmin float,ymin  float,xmax float,ymax float  )
RETURNS float AS $total$
declare
    b float :=width/2;
    a float :=b/2; --sin(30)=.5
    c float :=2*a;
    height float := 2*a+c;  --1.1547*width;
    ncol float :=ceil(abs(xmax-xmin)/width);
    nrow float :=ceil(abs(ymax-ymin)/height);
 
    polygon_string varchar := 'POLYGON((' ||

                                        0 || ' ' || 0     || ' , ' ||

                                        b || ' ' || a     || ' , ' ||

                                        b || ' ' || a+c   || ' , ' ||

                                        0 || ' ' || a+c+a || ' , ' ||

                                     -1*b || ' ' || a+c   || ' , ' ||

                                     -1*b || ' ' || a     || ' , ' ||

                                        0 || ' ' || 0     ||

                                '))';

BEGIN
    INSERT INTO hex_grid (the_geom) 
	SELECT st_translate(the_geom, x_series*(2*a+c)+xmin, y_series*(2*(c+a))+ymin)
		FROM 
		generate_series(0, ncol::int,1) AS x_series,
		generate_series(0, nrow::int,1) AS y_series,
		(SELECT polygon_string::geometry AS the_geom
			UNION
		SELECT ST_Translate(polygon_string::geometry, b , a+c)  AS the_geom
		) AS two_hex;

    ALTER TABLE hex_grid
    ALTER COLUMN the_geom TYPE geometry(Polygon, 4326)

    USING ST_SetSRID(the_geom,4326);

    RETURN NULL;

END;

$total$ LANGUAGE plpgsql;

--width in the units of the projection, xmin,ymin,xmax,ymax
SELECT genhexagons(0.005,-5.0,50.0,35.0,90.0);

CREATE INDEX
  ON hex_grid
  USING gist
  (the_geom);

DROP TABLE IF EXISTS hex_grid_005;
ALTER TABLE hex_grid RENAME TO hex_grid_005;

notes.md

• http://rexdouglass.com/spatial-hexagon-binning-in-postgis/

• http://mechanicalscribe.com/notes/binify-d3-topojson-tutorial/

• https://www.google.no/search?q=honeycomb%20qgis&rct=j

• function that generates several grids with different resolution

• function that sets up views with intersects on many different resolutions

• fix nRows bug (overshooting)

• implement support for arbitrary SRID