Pharmacies H3 Analysis

pharmacies.md

## 

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies` cluster by geom as 
select * from carto-demo-data.demo_tables.osm_pois_usa where subgroup_name='Pharmacy'

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi` cluster by geom as 
select * except (geom),st_buffer(geom, 2000) as geom 
from carto-demo-data.demo_tables.osm_pois_usa where subgroup_name='Pharmacy'

Polyfill: 1min 24s

CREATE OR REPLACE TABLE `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_res10_polyfill` cluster by h3
AS
SELECT osm_id, `carto-un`.carto.H3_STRING_TOINT(h3) as h3 FROM `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi`
cross join unnest(`carto-un`.carto.H3_POLYFILL(geom, 10)) as h3 

overlap areas. 3s!

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_overlap` cluster by h3
as 
select h3, count(*) as overlap_count from `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_res10_polyfill`
group by h3
having count(*) > 1

Coverage. 2s!

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_coverage` cluster by h3
as 
select distinct h3 from `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_res10_polyfill`

Enrich. 15s

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_overlap_pop`
as  
select o.*, sp.population, sp.female_population, sp.male_population
from `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_overlap`i 
inner join `carto-dev-data.public.derived_spatialfeatures_usa_h3int_res10_v1_yearly_v2_interpolated` sp 
ON o.h3=sp.h3

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_overlap_pop`
as  
select o.*, sp.population, sp.female as female_population, sp.male as male_population
from `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi_h3_overlap` o
inner join `carto-dev-data.public.derived_spatialfeatures_usa_h3int_res10_v1_yearly_v2_interpolated` sp 
ON o.h3=sp.h3