Hex makegrid() function in PL/SQL

hex.sql

CREATE OR REPLACE FUNCTION makegrid(schemaname text, boundingbox text, gridtable text, halfwidth numeric)
RETURNS text AS $$
DECLARE
tbl_cnt int;
XMIN numeric;
XMAX numeric;
YMIN numeric;
YMAX numeric;
x_value numeric;
y_value numeric;
x_count integer;
y_count integer;
y_offset numeric;
y_value_adj numeric;
sequencevar text := gridtable ||’_ogc_fid_seq’;

BEGIN
– Check to see if grid table already exists
SELECT COUNT(*) INTO tbl_cnt FROM information_schema.tables WHERE table_schema = schemaname AND table_name = gridtable;
– If grid table already exists, drop table
IF (tbl_cnt > 0) THEN
EXECUTE ‘DROP TABLE ‘ || gridtable;
ELSE
END IF;

– Create grid table
EXECUTE ‘CREATE TABLE ‘ || gridtable ||’
(
ogc_fid serial,
x_count integer,
x numeric(8,1),
y_count integer,
y numeric(8,1),
wkb_geometry geometry
)
WITH (
  OIDS=FALSE
)’;

– load extents of the bounding box and values for the initial y parameters
EXECUTE ‘SELECT floor(st_xmin(ST_transform(wkb_geometry,3112))) FROM ‘|| boundingbox INTO XMIN;
EXECUTE ‘SELECT ceiling(st_xmax(ST_transform(wkb_geometry,3112))) FROM ‘|| boundingbox INTO XMAX;
EXECUTE ‘SELECT floor(st_ymin(ST_transform(wkb_geometry,3112))) FROM ‘|| boundingbox INTO YMIN;
EXECUTE ‘SELECT ceiling(st_ymax(ST_transform(wkb_geometry,3112))) FROM ‘|| boundingbox INTO YMAX;
y_count = 1;
y_value = YMIN;

LOOP 
– for each y value, reset x to XMIN and subloop through the x values
  x_count = 1;
	x_value = XMIN;
	LOOP
	– for every even numbered x_count, offset y by half the height of the hexagon
	y_offset = (ceiling((x_count+1)/2::numeric) – floor((x_count+1)/2::numeric))*round(halfwidth*SQRT(3)::numeric,1)/2;
	– add the offset to the y_value
	y_value_adj = y_value + y_offset;
	EXECUTE ‘INSERT INTO ‘ || gridtable || ‘ VALUES(NEXTVAL(‘|| quote_literal(sequencevar) ||’), ‘|| x_count ||’, ‘|| x_value ||’, ‘ || y_count ||’, ‘ || y_value_adj ||’, NULL)’;
	x_count = x_count + 1;
	x_value = x_value + round(halfwidth*3/2::numeric,1);
	EXIT WHEN x_value > XMAX;    
	END LOOP;
– after exiting the subloop, increment the y count and y value
y_count = y_count + 1;
y_value = y_value + round(halfwidth*SQRT(3)::numeric,1);
EXIT WHEN y_value > YMAX;   
END LOOP;  

– With the table now populated with x y points, the last step is to create a hexagon geometry for each.
EXECUTE ‘UPDATE ‘|| gridtable ||’ SET wkb_geometry = ST_transform(ST_Polygon(ST_makeline(ARRAY[st_makepoint(x-(0.5*'|| halfwidth ||'), y+(SQRT(3)*0.5*'|| halfwidth ||')), st_makepoint(x-(1*'|| halfwidth ||'), y), st_makepoint(x-(0.5*'|| halfwidth ||'), y-(SQRT(3)*0.5*'|| halfwidth ||')), st_makepoint(x+(0.5*'|| halfwidth ||'), y-(SQRT(3)*0.5*'|| halfwidth ||')), st_makepoint(x+(1*'|| halfwidth ||'), y), st_makepoint(x+(0.5*'|| halfwidth ||'), y+(SQRT(3)*0.5*'|| halfwidth ||')), st_makepoint(x-(0.5*'|| halfwidth ||'), y+(SQRT(3)*0.5*'|| halfwidth ||'))]),3112),4326)’;

RETURN ‘HEXAGON GRID CREATED’;
END;
$$ LANGUAGE plpgsql STRICT;

makegrid.sql

SELECT makegrid(‘public’, ‘myboundingbox’, ‘myhexgrid’, 250);