Skip to content

Instantly share code, notes, and snippets.

@wboykinm

wboykinm/hexgrid.sql

Created February 12, 2014 17:12
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save wboykinm/8959996 to your computer and use it in GitHub Desktop.
Save wboykinm/8959996 to your computer and use it in GitHub Desktop.
Download ZIP
Hexgrid code
Raw
hexgrid.sql
WITH hgrid AS
(SELECT CDB_HexagonGrid(ST_Expand(CDB_XYZ_Extent(/* INSERT TILE XYZ SOMEHOW */), CDB_XYZ_Resolution(/* CURRENT ZOOM */) * 15), CDB_XYZ_Resolution(/* CURRENT ZOOM */) * 15) AS cell)
SELECT hgrid.cell AS the_geom_webmercator,
count(i.cartodb_id) AS points_count,
count(i.cartodb_id)/power(15 * CDB_XYZ_Resolution(/* CURRENT ZOOM */), 2) AS points_density,
1 AS cartodb_id
FROM hgrid,
(SELECT *
FROM /* TABLE NAME HERE */) i
WHERE ST_Intersects(i.the_geom_webmercator, hgrid.cell)
GROUP BY hgrid.cell
@wboykinm
Copy link
Author

WITH hgrid AS (SELECT CDB_HexagonGrid(ST_Expand(ST_SetSRID(ST_MakeBox2D(ST_MakePoint(-14626989.732651, 12181004.827526), ST_MakePoint(-13374645.461227, 13433349.098950)), 900913), CDB_XYZ_Resolution(12) * 15), CDB_XYZ_Resolution(12) * 15) AS cell) SELECT hgrid.cell AS wkb_geometry, count(i.id) AS points_count, count(i.id)/power(15 * CDB_XYZ_Resolution(12), 2) AS points_density, 1 AS id FROM hgrid, (SELECT * FROM households) i WHERE ST_Intersects(i.wkb_geometry, hgrid.cell) GROUP BY hgrid.cell

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment