GH6jG8uD

1CREATE OR REPLACE PROCEDURE create_hex_grid(source_table_name varchar(30), height numeric = 1000, srid integer = 31287,
2                                      geom_column_name varchar(30) = 'geom', target_table_name varchar(30) = 'hexgrid')
3    LANGUAGE plpgsql
4AS
5$$
6DECLARE
7    _curs  refcursor ;
8    _width NUMERIC  := height * 0.866;
9    _geom  GEOMETRY;
10    _hx    GEOMETRY := ST_GeomFromText(
11            FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
12                   (_width * 0.5), (height * 0.25),
13                   (_width * 0.5), (height * 0.75),
14                   0, height,
15                   (_width * -0.5), (height * 0.75),
16                   (_width * -0.5), (height * 0.25)
17                ), srid);
18
19BEGIN
20    CREATE TEMP TABLE hx_tmp
21    (
22        geom GEOMETRY(POLYGON)
23    );
24
25    OPEN _curs FOR EXECUTE 'SELECT st_transform(' || quote_ident(geom_column_name) || ', ' || srid || ') from ' ||
26                           source_table_name;
27    LOOP
28        FETCH
29            _curs INTO _geom;
30        EXIT WHEN NOT FOUND;
31
32        INSERT INTO hx_tmp
33        SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
34        FROM generate_series(
35                     (st_xmin(_geom) / _width)::INTEGER * _width - _width,
36                     (st_xmax(_geom) / _width)::INTEGER * _width + _width,
37                     _width) x_series,
38             generate_series(
39                     (st_ymin(_geom) / (height * 1.5))::INTEGER * (height * 1.5) - height,
40                     (st_ymax(_geom) / (height * 1.5))::INTEGER * (height * 1.5) + height,
41                     height * 1.5) y_series
42        WHERE ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
43
44        INSERT INTO hx_tmp
45        SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
46        FROM generate_series(
47                     (st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
48                     (st_xmax(_geom) / _width)::INTEGER * _width + _width,
49                     _width) x_series,
50             generate_series(
51                         (st_ymin(_geom) / (height * 1.5))::INTEGER * (height * 1.5) - (height * 1.75),
52                         (st_ymax(_geom) / (height * 1.5))::INTEGER * (height * 1.5) + height,
53                         height * 1.5) y_series
54        WHERE ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
55
56    END LOOP;
57    CLOSE _curs;
58
59    CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
60    EXECUTE 'DROP TABLE IF EXISTS ' || target_table_name;
61    EXECUTE 'CREATE TABLE ' || target_table_name || ' (geom GEOMETRY(POLYGON, ' || srid || '))';
62    EXECUTE 'INSERT INTO ' || target_table_name || ' SELECT * FROM hx_tmp GROUP BY geom';
63    EXECUTE 'CREATE INDEX sidx_' || target_table_name || '_geom ON ' || target_table_name || ' USING GIST (geom)';
64    DROP TABLE IF EXISTS hx_tmp;
65END
66$$;