Skip to content

Instantly share code, notes, and snippets.

@bmount

bmount/ex-hexbin.geojson

Last active March 26, 2020 02:54
Show Gist options
  • Save bmount/2195b975cae37ea88b83662df8356d9a to your computer and use it in GitHub Desktop.
Save bmount/2195b975cae37ea88b83662df8356d9a to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
ex-hexbin.geojson
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Raw
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;
Raw
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;
Raw
minimal-example.sql
select st_asgeojson(hexbin(0.2, st_envelope(st_geomfromtext('LINESTRING(-122.5 37.5, -122 37)'))));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment