Interpolation experiment to generate an h3 dataset resolution 10

h3_spatial_features_interpolation.sql

-- Switzerland
create table carto-dev-data.public.derived_spatialfeatures_che_h3res10_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 10) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_che_h3res8_v1_yearly_v2` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;

-- Spain
create table carto-dev-data.public.derived_spatialfeatures_esp_h3res10_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 10) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_esp_h3res8_v1_yearly_v2` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;

-- Ukraine
create table carto-dev-data.public.derived_spatialfeatures_ukr_h3res10_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 10) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_ukr_h3res8_v1_yearly_v2` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;

-- USA
create table carto-dev-data.public.derived_spatialfeatures_usa_h3res10_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 10) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_usa_h3res8_v1_yearly_v2` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;


-- Switzerland
create table carto-dev-data.public.derived_spatialfeatures_che_h3res12_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 12) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_che_h3res8_v1_yearly_v2` 
)
select h3, population/pow(7,4) as population, female/pow(7,4) as female, male/pow(7,4) as male
from q, unnest(q.h3_children) as h3;

-- Spain
create table carto-dev-data.public.derived_spatialfeatures_esp_h3res12_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 12) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_esp_h3res10_v1_yearly_v2_interpolated` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;

-- Ukraine
create table carto-dev-data.public.derived_spatialfeatures_ukr_h3res12_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 12) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_ukr_h3res10_v1_yearly_v2_interpolated` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;

-- USA
create table carto-dev-data.public.derived_spatialfeatures_usa_h3res12_v1_yearly_v2_interpolated cluster by h3 
as 
with q as (
  select `carto-un`.carto.H3_TOCHILDREN(h3, 12) as h3_children,* except(h3)
   from `carto-dev-data.public.derived_spatialfeatures_usa_h3res10_v1_yearly_v2_interpolated` 
)
select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
from q, unnest(q.h3_children) as h3;