Skip to content

Instantly share code, notes, and snippets.

@HeikkiVesanto
Created November 4, 2021 09:58
Show Gist options
  • Save HeikkiVesanto/042fc22bdaf6e48a983953e237495a9e to your computer and use it in GitHub Desktop.
Save HeikkiVesanto/042fc22bdaf6e48a983953e237495a9e to your computer and use it in GitHub Desktop.
Hoods to Hex SQL
create index idx_liberties_geom_2157 on dublin_hoods.liberties using gist (st_transform(geom, 2157));
create table dublin_hoods.liberties_extent as
select g.id, g.geom, count(nh.*) as count, ((count(nh.*)/(select count(*)::float from dublin_hoods.liberties)) * 100)::int as pct
from dublin_hoods.hex_grid g
left join dublin_hoods.liberties nh on st_intersects(g.geom, st_transform(nh.geom, 2157)) --My grid is in EPSG:2157 but the geoms are 4326 so need to reproject.
group by g.id, g.geom
having count(nh.*) > 0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment