ex-hexbin.geojson

geojson-out-example.sql

with bins as (select hexbin(0.001, st_envelope(st_geomfromtext('LINESTRING(-122.43 37.75, -122.435 37.755)'))) as geom) SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
  SELECT jsonb_build_object(
    'type',       'Feature',
    'geometry',   ST_AsGeoJSON(geom)::jsonb, 'properties', null
  ) AS feature
  FROM (SELECT * FROM bins) inputs) features;

hexbin-fn.sql

-- See: https://gist.github.com/fitnr/558cd3edf0b2db10c4a446c01ae68578
-- adapted from https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093

-- usage: select * FROM hexbin(numeric, geometry)
-- cover a table with something like:
-- select row_number() over () id, geom
-- from hexbin(5280, st_transform(st_setsrid(st_expand(ST_EstimatedExtent('table', 'geom'), 0.1), 4269), 2255));

CREATE OR REPLACE FUNCTION hexbin (height numeric, minx numeric, miny numeric, maxx numeric, maxy numeric, srid integer)
RETURNS TABLE (geom geometry(polygon))
AS $$
    WITH d (width) AS (VALUES (height * 0.866)),
        hex (geom) AS (SELECT 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) FROM d)
        SELECT
            ST_Translate(hex.geom, x_series, y_series)::geometry(polygon) geom
        FROM d, hex,
            generate_series(
                (minx / width)::int * width - width,
                (maxx / width)::int * width + width,
                width) x_series,
            generate_series(
                (miny / (height * 1.5))::int * (height * 1.5) - height,
                (maxy / (height * 1.5))::int * (height * 1.5) + height,
                height * 1.5) y_series
        UNION
        SELECT ST_Translate(hex.geom, x_series, y_series)::geometry(polygon) geom
        FROM d, hex,
            generate_series(
                (minx / width)::int * width - (width * 1.5),
                (maxx / width)::int * width + width,
                width) x_series,
            generate_series(
                (miny / (height * 1.5))::int * (height * 1.5) - (height * 1.75),
                (maxy / (height * 1.5))::int * (height * 1.5) + height,
                height * 1.5) y_series;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION hexbin (height numeric, box box2d, srid integer)
RETURNS TABLE (geom geometry(polygon))
AS $$
    SELECT * FROM hexbin(height, st_xmin(box)::numeric, st_ymin(box)::numeric, st_xmax(box)::numeric, st_ymax(box)::numeric, srid);
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION hexbin (height numeric, box geometry)
RETURNS TABLE (geom geometry(polygon))
AS $$
    SELECT hex.geom
    FROM hexbin(height, st_xmin(box)::numeric, st_ymin(box)::numeric, st_xmax(box)::numeric, st_ymax(box)::numeric, st_srid(box)) hex(geom)
    WHERE _st_intersects(geom, box); -- skip the bounding box test in st_intersects because we know it's TRUE.
$$ LANGUAGE SQL IMMUTABLE;

minimal-example.sql

select st_asgeojson(hexbin(0.2, st_envelope(st_geomfromtext('LINESTRING(-122.5 37.5, -122 37)'))));