ex-hexbin.geojson

{"type": "FeatureCollection", "features": [{"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43508, 37.754], [-122.434647, 37.75425]
, [-122.434647, 37.75475], [-122.43508, 37.755], [-122.435513, 37.75475], [-122.435513, 37.75425], [-122.43508, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434214, 37.754], [-122.433781, 37.75425], [-122.433781, 37.75475], [-122.434214, 37.755], [-122.434647, 37.75475], [-122.434647, 37.75425], [-122.434214, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433781, 37.75475], [-122.433348, 37.755], [-122.433348, 37.7555], [-122.433781, 37.75575], [-122.434214, 37.7555], [-122.434214, 37.755], [-122.433781, 37.75475]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434647, 37.75475], [-122.434214, 37.755], [-122.434214, 37.7555], [-122.434647, 37.75575], [-122.43508, 37.7555], [-122.43508, 37.755], [-122.434647, 37.75475]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432049, 37.75475], [-122.431616, 37.755], [-122.431616, 37.7555], [-122.432049, 37.75575], [-122.432482, 37.7555], [-122.432482, 37.755], [-122.432049, 37.75475]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432915, 37.75475], [-122.432482, 37.755], [-122.432482, 37.7555], [-122.432915, 37.75575], [-122.433348, 37.7555], [-122.433348, 37.755], [-122.432915, 37.75475]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433348, 37.754], [-122.432915, 37.75425], [-122.432915, 37.75475], [-122.433348, 37.755], [-122.433781, 37.75475], [-122.433781, 37.75425], [-122.433348, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432482, 37.754], [-122.432049, 37.75425], [-122.432049, 37.75475], [-122.432482, 37.755], [-122.432915, 37.75475], [-122.432915, 37.75425], [-122.432482, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43075, 37.754], [-122.430317, 37.75425], [-122.430317, 37.75475], [-122.43075, 37.755], [-122.431183, 37.75475], [-122.431183, 37.75425], [-122.43075, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431616, 37.754], [-122.431183, 37.75425], [-122.431183, 37.75475], [-122.431616, 37.755], [-122.432049, 37.75475], [-122.432049, 37.75425], [-122.431616, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.429884, 37.754], [-122.429451, 37.75425], [-122.429451, 37.75475], [-122.429884, 37.755], [-122.430317, 37.75475], [-122.430317, 37.75425], [-122.429884, 37.754]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.430317, 37.75475], [-122.429884, 37.755], [-122.429884, 37.7555], [-122.430317, 37.75575], [-122.43075, 37.7555], [-122.43075, 37.755], [-122.430317, 37.75475]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431183, 37.75475], [-122.43075, 37.755], [-122.43075, 37.7555], [-122.431183, 37.75575], [-122.431616, 37.7555], [-122.431616, 37.755], [-122.431183, 37.75475]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.429884, 37.7495], [-122.429451, 37.74975], [-122.429451, 37.75025], [-122.429884, 37.7505], [-122.430317, 37.75025], [-122.430317, 37.74975], [-122.429884, 37.7495]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.430317, 37.75025], [-122.429884, 37.7505], [-122.429884, 37.751], [-122.430317, 37.75125], [-122.43075, 37.751], [-122.43075, 37.7505], [-122.430317, 37.75025]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431183, 37.75025], [-122.43075, 37.7505], [-122.43075, 37.751], [-122.431183, 37.75125], [-122.431616, 37.751], [-122.431616, 37.7505], [-122.431183, 37.75025]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43075, 37.7495], [-122.430317, 37.74975], [-122.430317, 37.75025], [-122.43075, 37.7505], [-122.431183, 37.75025], [-122.431183, 37.74975], [-122.43075, 37.7495]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431616, 37.7495], [-122.431183, 37.74975], [-122.431183, 37.75025], [-122.431616, 37.7505], [-122.432049, 37.75025], [-122.432049, 37.74975], [-122.431616, 37.7495]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432482, 37.7495], [-122.432049, 37.74975], [-122.432049, 37.75025], [-122.432482, 37.7505], [-122.432915, 37.75025], [-122.432915, 37.74975], [-122.432482, 37.7495]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433348, 37.7495], [-122.432915, 37.74975], [-122.432915, 37.75025], [-122.433348, 37.7505], [-122.433781, 37.75025], [-122.433781, 37.74975], [-122.433348, 37.7495]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432915, 37.75025], [-122.432482, 37.7505], [-122.432482, 37.751], [-122.432915, 37.75125], [-122.433348, 37.751], [-122.433348, 37.7505], [-122.432915, 37.75025]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432049, 37.75025], [-122.431616, 37.7505], [-122.431616, 37.751], [-122.432049, 37.75125], [-122.432482, 37.751], [-122.432482, 37.7505], [-122.432049, 37.75025]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433348, 37.751], [-122.432915, 37.75125], [-122.432915, 37.75175], [-122.433348, 37.752], [-122.433781, 37.75175], [-122.433781, 37.75125], [-122.433348, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432482, 37.751], [-122.432049, 37.75125], [-122.432049, 37.75175], [-122.432482, 37.752], [-122.432915, 37.75175], [-122.432915, 37.75125], [-122.432482, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43075, 37.751], [-122.430317, 37.75125], [-122.430317, 37.75175], [-122.43075, 37.752], [-122.431183, 37.75175], [-122.431183, 37.75125], [-122.43075, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431616, 37.751], [-122.431183, 37.75125], [-122.431183, 37.75175], [-122.431616, 37.752], [-122.432049, 37.75175], [-122.432049, 37.75125], [-122.431616, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.429884, 37.751], [-122.429451, 37.75125], [-122.429451, 37.75175], [-122.429884, 37.752], [-122.430317, 37.75175], [-122.430317, 37.75125], [-122.429884, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.430317, 37.75175], [-122.429884, 37.752], [-122.429884, 37.7525], [-122.430317, 37.75275], [-122.43075, 37.7525], [-122.43075, 37.752], [-122.430317, 37.75175]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431183, 37.75175], [-122.43075, 37.752], [-122.43075, 37.7525], [-122.431183, 37.75275], [-122.431616, 37.7525], [-122.431616, 37.752], [-122.431183, 37.75175]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.429884, 37.7525], [-122.429451, 37.75275], [-122.429451, 37.75325], [-122.429884, 37.7535], [-122.430317, 37.75325], [-122.430317, 37.75275], [-122.429884, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.430317, 37.75325], [-122.429884, 37.7535], [-122.429884, 37.754], [-122.430317, 37.75425], [-122.43075, 37.754], [-122.43075, 37.7535], [-122.430317, 37.75325]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431183, 37.75325], [-122.43075, 37.7535], [-122.43075, 37.754], [-122.431183, 37.75425], [-122.431616, 37.754], [-122.431616, 37.7535], [-122.431183, 37.75325]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43075, 37.7525], [-122.430317, 37.75275], [-122.430317, 37.75325], [-122.43075, 37.7535], [-122.431183, 37.75325], [-122.431183, 37.75275], [-122.43075, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.431616, 37.7525], [-122.431183, 37.75275], [-122.431183, 37.75325], [-122.431616, 37.7535], [-122.432049, 37.75325], [-122.432049, 37.75275], [-122.431616, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432482, 37.7525], [-122.432049, 37.75275], [-122.432049, 37.75325], [-122.432482, 37.7535], [-122.432915, 37.75325], [-122.432915, 37.75275], [-122.432482, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432049, 37.75325], [-122.431616, 37.7535], [-122.431616, 37.754], [-122.432049, 37.75425], [-122.432482, 37.754], [-122.432482, 37.7535], [-122.432049, 37.75325]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432915, 37.75325], [-122.432482, 37.7535], [-122.432482, 37.754], [-122.432915, 37.75425], [-122.433348, 37.754], [-122.433348, 37.7535], [-122.432915, 37.75325]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433348, 37.7525], [-122.432915, 37.75275], [-122.432915, 37.75325], [-122.433348, 37.7535], [-122.433781, 37.75325], [-122.433781, 37.75275], [-122.433348, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432049, 37.75175], [-122.431616, 37.752], [-122.431616, 37.7525], [-122.432049, 37.75275], [-122.432482, 37.7525], [-122.432482, 37.752], [-122.432049, 37.75175]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.432915, 37.75175], [-122.432482, 37.752], [-122.432482, 37.7525], [-122.432915, 37.75275], [-122.433348, 37.7525], [-122.433348, 37.752], [-122.432915, 37.75175]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433781, 37.75175], [-122.433348, 37.752], [-122.433348, 37.7525], [-122.433781, 37.75275], [-122.434214, 37.7525], [-122.434214, 37.752], [-122.433781, 37.75175]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434647, 37.75175], [-122.434214, 37.752], [-122.434214, 37.7525], [-122.434647, 37.75275], [-122.43508, 37.7525], [-122.43508, 37.752], [-122.434647, 37.75175]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434214, 37.7525], [-122.433781, 37.75275], [-122.433781, 37.75325], [-122.434214, 37.7535], [-122.434647, 37.75325], [-122.434647, 37.75275], [-122.434214, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433781, 37.75325], [-122.433348, 37.7535], [-122.433348, 37.754], [-122.433781, 37.75425], [-122.434214, 37.754], [-122.434214, 37.7535], [-122.433781, 37.75325]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434647, 37.75325], [-122.434214, 37.7535], [-122.434214, 37.754], [-122.434647, 37.75425], [-122.43508, 37.754], [-122.43508, 37.7535], [-122.434647, 37.75325]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43508, 37.7525], [-122.434647, 37.75275], [-122.434647, 37.75325], [-122.43508, 37.7535], [-122.435513, 37.75325], [-122.435513, 37.75275], [-122.43508, 37.7525]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43508, 37.751], [-122.434647, 37.75125], [-122.434647, 37.75175], [-122.43508, 37.752], [-122.435513, 37.75175], [-122.435513, 37.75125], [-122.43508, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434214, 37.751], [-122.433781, 37.75125], [-122.433781, 37.75175], [-122.434214, 37.752], [-122.434647, 37.75175], [-122.434647, 37.75125], [-122.434214, 37.751]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434647, 37.75025], [-122.434214, 37.7505], [-122.434214, 37.751], [-122.434647, 37.75125], [-122.43508, 37.751], [-122.43508, 37.7505], [-122.434647, 37.75025]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.433781, 37.75025], [-122.433348, 37.7505], [-122.433348, 37.751], [-122.433781, 37.75125], [-122.434214, 37.751], [-122.434214, 37.7505], [-122.433781, 37.75025]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.434214, 37.7495], [-122.433781, 37.74975], [-122.433781, 37.75025], [-122.434214, 37.7505], [-122.434647, 37.75025], [-122.434647, 37.74975], [-122.434214, 37.7495]]]}, "properties": null}, {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.43508, 37.7495], [-122.434647, 37.74975], [-122.434647, 37.75025], [-122.43508, 37.7505], [-122.435513, 37.75025], [-122.435513, 37.74975], [-122.43508, 37.7495]]]}, "properties": null}]}

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)'))));