[hex grids from postgres table]

hexgrids.sql

CREATE OR REPLACE PROCEDURE create_hex_grid(source_table_name varchar(30), height numeric = 1000, srid integer = 31287,
                                      geom_column_name varchar(30) = 'geom', target_table_name varchar(30) = 'hexgrid')
    LANGUAGE plpgsql
AS
$$
DECLARE
    _curs  refcursor ;
    _width NUMERIC  := height * 0.866;
    _geom  GEOMETRY;
    _hx    GEOMETRY := ST_GeomFromText(
            FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
                   (_width * 0.5), (height * 0.25),
                   (_width * 0.5), (height * 0.75),
                   0, height,
                   (_width * -0.5), (height * 0.75),
                   (_width * -0.5), (height * 0.25)
                ), srid);

BEGIN
    CREATE TEMP TABLE hx_tmp
    (
        geom GEOMETRY(POLYGON)
    );

    OPEN _curs FOR EXECUTE 'SELECT st_transform(' || quote_ident(geom_column_name) || ', ' || srid || ') from ' ||
                           source_table_name;
    LOOP
        FETCH
            _curs INTO _geom;
        EXIT WHEN NOT FOUND;

        INSERT INTO hx_tmp
        SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
        FROM generate_series(
                     (st_xmin(_geom) / _width)::INTEGER * _width - _width,
                     (st_xmax(_geom) / _width)::INTEGER * _width + _width,
                     _width) x_series,
             generate_series(
                     (st_ymin(_geom) / (height * 1.5))::INTEGER * (height * 1.5) - height,
                     (st_ymax(_geom) / (height * 1.5))::INTEGER * (height * 1.5) + height,
                     height * 1.5) y_series
        WHERE ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);

        INSERT INTO hx_tmp
        SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
        FROM generate_series(
                     (st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
                     (st_xmax(_geom) / _width)::INTEGER * _width + _width,
                     _width) x_series,
             generate_series(
                         (st_ymin(_geom) / (height * 1.5))::INTEGER * (height * 1.5) - (height * 1.75),
                         (st_ymax(_geom) / (height * 1.5))::INTEGER * (height * 1.5) + height,
                         height * 1.5) y_series
        WHERE ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);

    END LOOP;
    CLOSE _curs;

    CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
    EXECUTE 'DROP TABLE IF EXISTS ' || target_table_name;
    EXECUTE 'CREATE TABLE ' || target_table_name || ' (geom GEOMETRY(POLYGON, ' || srid || '))';
    EXECUTE 'INSERT INTO ' || target_table_name || ' SELECT * FROM hx_tmp GROUP BY geom';
    EXECUTE 'CREATE INDEX sidx_' || target_table_name || '_geom ON ' || target_table_name || ' USING GIST (geom)';
    DROP TABLE IF EXISTS hx_tmp;
END
$$;