3 drop schema if exists hex cascade;
6 set search_path to hex,public,gis;
8 create or replace function hexgrid(
14 origin geometry(POINT),
16 ) returns table (x integer, y integer, cantor numeric, hex geometry) as $$
19 stride double precision;
22 xmin double precision;
23 ymin double precision;
24 xoff double precision;
26 -- the origin will be the center of the 1,1 hex.
27 -- if null, then the origin will be set to fit in the
30 -- xdim, and ydim can be null, in which case they are
31 -- calculated from the tilesize and the covering box
35 -- precedence: ydim, xdim, tilesize
37 -- counter rotate geometry, fill, rotate grid
38 rcover = ST_Rotate(st_transform(coverage,srid), -angle*3.1415926/180.0);
40 xmin := ST_XMin(rcover);
41 ymin := ST_YMin(rcover);
43 stride := tilesize * 2.0 / sqrt(3.0);
45 --raise notice 'stride = %', stride;
46 xdim := ((ST_XMax(rcover) - ST_XMin(rcover))/tilesize*2/sqrt(3.0))::integer;
47 ydim := ((ST_YMax(rcover) - ST_YMin(rcover))/tilesize)::integer;
49 --raise notice 'coverage = % % % %', xmin, ymin, xdim, ydim;
51 -- TODO reorient these so right hand rule is inside
52 hex := ST_Scale(ST_GeomFromText('POLYGON((
53 .577350269189625764509148780502 0.0,
54 .288675134594812882254574390251 0.5,
55 -.288675134594812882254574390251 0.5,
56 -.577350269189625764509148780502 0.0,
57 -.288675134594812882254574390251 -0.5,
58 .288675134594812882254574390251 -0.5,
59 .577350269189625764509148780502 0.0))',ST_SRID(rcover)),tilesize,tilesize,1.0);
61 xoff := .577350269189625764509148780502 * 1.5;
63 for x in 0 .. xdim loop
64 for y in 0 .. ydim loop
65 -- TODO actually calculate cantor
66 return query select x, ydim-y, hex.cantor(ARRAY[x,ydim-y]),
68 tilesize * (x * xoff) + xmin,
69 --tilesize * (y + 0.5 * (x%2) - (x+1)/2) + ymin
70 tilesize * (y + 0.5 * (x%2) ) + ymin
72 --st_rotate(ST_Translate(hex,
73 --tilesize * (x * xoff) + xmin,
74 --tilesize * (y + 0.5 * (x%2) - (x+1)/2) + ymin
84 -- create some hexagon ops
86 create function ijk(xy integer[]) returns integer[] language 'plpgsql' as $$
90 if array_length(xy,1) = 3 then
98 ijk[2] := ijk[2] + (-xy[1]+1) / 2;
100 ijk[2] := ijk[2] - xy[1]/2;
103 ijk[3] := -ijk[1] - ijk[2];
109 create function ijk(x integer, y integer) returns integer[] language 'sql' as $$
110 select hex.ijk(ARRAY[x,y]);
113 -- invert the cantor paring
114 create function xy(hex numeric) returns integer[] language 'plpgsql' as $$
123 w := floor((sqrt(8.0 * hex + 1.0) - 1.0)/2.0)::integer;
128 -- handle the negative cases
132 w := floor((sqrt(8.0 * hex + 1.0) - 1.0)/2.0)::integer;
138 if xy[1] % 2 = 1 then
139 xy[1] = -(xy[1]+1)/2;
143 if xy[2] % 2 = 1 then
144 xy[2] = -(xy[2]+1)/2;
154 create function ijk(hex numeric) returns integer[] language 'sql' as $$
155 select hex.ijk(hex.xy(hex));
158 create function xy(ijk integer[]) returns integer[] language 'plpgsql' as $$
162 -- make sure this is really an ijk
163 -- just no-op if it's already an
164 -- xy, so this is sort of a coercion function
165 if array_length(ijk,1) = 2 then
172 xy[2] := xy[2] + (-ijk[1] + 1) / 2;
174 xy[2] := xy[2] - ijk[1]/2;
180 CREATE OR REPLACE FUNCTION vec_add(arr1 integer[], arr2 integer[])
183 SELECT array_agg(result)
184 FROM (SELECT tuple.val1 + tuple.val2 AS result
185 FROM (SELECT UNNEST($1) AS val1
187 ,generate_subscripts($1, 1) AS ix) tuple
189 $$ LANGUAGE SQL IMMUTABLE STRICT;
191 create function adjhex(dir integer, hex integer[]) returns integer[] language 'plpgsql' as $$
195 if array_length(hex,1) = 2 then
201 hex := hex.vec_add(hex, ARRAY[-1, 1, 0]);
203 hex := hex.vec_add(hex, ARRAY[ 0, 1,-1]);
205 hex := hex.vec_add(hex, ARRAY[ 1, 0,-1]);
207 hex := hex.vec_add(hex, ARRAY[ 1,-1, 0]);
209 hex := hex.vec_add(hex, ARRAY[ 0,-1, 1]);
211 hex := hex.vec_add(hex, ARRAY[-1, 0, 1]);
219 create function adjhex(dir integer, hex numeric) returns integer[] language 'sql' as $$
220 select hex.adjhex(dir, hex.ijk(hex));
223 -- TODO make this work for any, or inline it
224 create function natcantor(hex integer[]) returns numeric language 'sql' as $$
225 select cast((hex[1] + hex[2]) * (hex[1] + hex[2] + 1) / 2 + hex[2] + 1 as numeric);
228 create function cantor(hex integer[]) returns numeric language 'plpgsql' as $$
231 if array_length(hex,1) = 3 then
235 if hex[1] < 0 or hex[2] < 0 then
239 hex[1] = -hex[1] - 1;
242 hex[2] = -hex[2] - 1;
244 return -cast((hex[1] + hex[2]) * (hex[1] + hex[2] + 1) / 2 + hex[2] + 1 as numeric);
247 return cast((hex[1] + hex[2]) * (hex[1] + hex[2] + 1) / 2 + hex[2] + 1 as numeric);
251 create function adjacent(hex numeric) returns setof numeric language 'sql' as $$
252 select hex.cantor(hex.adjhex(dir, hex)) from generate_series(0,5) as dir;
255 create function adjacent(dir integer, hex numeric) returns numeric language 'sql' as $$
256 select hex.cantor(hex.adjhex(dir, hex.ijk(hex)));
259 create function within(hex numeric, range integer = 1, include boolean = false) returns setof numeric language 'plpgsql' as $$
261 c integer[]; -- cantor coordinates
271 -- we could in theory eliminate the inner loop and
274 for r in 1 .. range loop
277 c[3] := -c[1] - c[2]; -- keep the invariant
278 hex := hex.cantor(c);
280 for h in 0 .. r * 6 - 1 loop
282 hex = hex.adjacent(h/r+2,hex);
289 create function within_array(hex numeric, range integer = 1, include boolean = false) returns numeric[] language 'sql' as $$
290 select array_agg(c) from hex.within(hex, range, include) c;
293 create function atrange(hex numeric, range integer = 1) returns setof numeric language 'plpgsql' as $$
295 c integer[]; -- cantor coordinates
305 c[1] := c[1] + range;
306 c[3] := -c[1] - c[2]; -- keep the invariant
307 hex := hex.cantor(c);
309 for q in 0 .. range * 6 - 1 loop
311 hex = hex.adjacent(q/range+2,hex);
320 xy(ijk(xy(ARRAY[1,2])))
323 select xy(ARRAY[2,2]), dir, xy(adjhex(dir, ijk(ARRAY[2,2]))) from generate_series(0,5) as dir;
325 xy(adjhex(dir, ijk(ARRAY[1,2]))),
326 cantor(xy(adjhex(dir, ijk(ARRAY[1,2])))),
327 xy(cantor(xy(adjhex(dir, ijk(ARRAY[1,2])))))
328 from generate_series(0,5) as dir;
330 select cantor(ARRAY[4,3]);
331 --select * from within(cantor(ARRAY[4,4]), 2);
332 select h, xy(h) from within(cantor(ARRAY[4,3]), 2) as h;