Proxying layers

Among the many layers of data available, some have actually high granularity and will be used as a renormalising function to distribute values that would otherwise be uniform in space. The idea is to link certain economic activity related variables to some infrastructure and geographic layers, and assuming some form of homogeneity, distribute the economic activity proposrtionally to the the distribution, say, of infrastrucutre in a location. This notebook will lay some basis for this.

#Base
import os
import re
import numpy as np
import glob

# data eng
import shapely as shp
import geopandas as gpd
import pandas as pd
import ibis as ib
from ibis import _
ib.options.interactive = True

# module
import scalenav.scale_nav as sd
import scalenav.data as dt
import scalenav.oop as snoo
from scalenav.plotting import cmap

### Visualisation related
import pydeck as pdk
from matplotlib import pyplot as plt
import pypalettes as pypal

Relevant docs

ddb spatial extension web: https://duckdb.org/docs/extensions/spatial.html

ddb spatial extension github functions ref: https://github.com/duckdb/duckdb_spatial/blob/main/docs/functions.md

H3 + duckdb extension : https://github.com/isaacbrodsky/h3-duckdb

ibis + geospatial :

general : https://ibis-project.org/reference/expression-tables#attributes

blogpost : https://ibis-project.org/posts/ibis-duckdb-geospatial/#functions-supported-and-next-steps

functions ref : https://ibis-project.org/reference/expression-geospatial

grid_param = 100

ghsl_file = f"/Users/cenv1069/Documents/data/datasets/JRC/S_{grid_param}m/**"
# "/Users/cenv1069/Documents/agriculture/ghsl/non_res/**"
# "/Users/cenv1069/Documents/data/datasets/JRC/S_10m/**"

tiffs = [x for x in glob.glob(ghsl_file,recursive=True) if re.search(pattern=".tif$",string=x)]
print(len(tiffs))
tiffs

0

[]

# view_state = pdk.data_utils.compute_view(points=ghsl_gridded.sample(10000)["h3_gridded"].apply(lambda cell: h3.cell_to_latlng(cell)[::-1]))

# lagos: latitude = 6.450151,longitude = 3.531193 !!!! DOES NOT WORK BECAUSE IT IS EXCLUDED FROM DOSE!!!
# Abidjan :  latitude = 5.367, longitude = -4.009
# Kano : latitude = 11.985, longitude = 8.533

zoom_lat=11.985
zoom_lon=8.533
zoom_width=70_000

view_state_zoomed = pdk.ViewState(latitude = zoom_lat,longitude = zoom_lon,zoom=12,pitch=30,bearing=0)

# extracting data for the plotting region : 

plotting_region = dt.square_poly(lat=zoom_lat,lon=zoom_lon,distance=zoom_width)
# gpd.GeoSeries(plotting_region,crs="EPSG:4326").explore()

limits = [np.round(x,3) for x in plotting_region.bounds]
limits

[8.212, 11.664, 8.854, 12.306]

conn = snoo.sn_connect()

Connecting to a temporary in-memory DB instance.

out_folder = "outputs"

out_filename = f'S_NRES_{grid_param}_R8_C19/*.parquet'

out_file = f"../../{out_folder}/{out_filename}"

raw_grid = snoo.sn_table(conn,path=out_file,name="raw_grid")

H3 and grid parameters

dose_wdi_geo_file = "/Users/cenv1069/Documents/data/datasets/local_data/dose-wdi/0_3/dose_wdi_geo.parquet"

dwg = conn.read_parquet(dose_wdi_geo_file,table_name="dwg")

dwg.head()

┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ gid_0  ┃ country ┃ gid_1   ┃ grp_usd_2015 ┃ services_usd_2015 ┃ manufacturing_usd_2015 ┃ agriculture_usd_2015 ┃ centr                                                           ┃ geometry                                                                                                                                                                                                                                    ┃ color             ┃ radius    ┃ x         ┃ y         ┃
┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━┩
│ string │ string  │ string  │ float64      │ float64           │ float64                │ float64              │ binary                                                          │ binary                                                                                                                                                                                                                                      │ array<int64>      │ float64   │ float64   │ float64   │
├────────┼─────────┼─────────┼──────────────┼───────────────────┼────────────────────────┼──────────────────────┼─────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼───────────────────┼───────────┼───────────┼───────────┤
│ ALB    │ Albania │ ALB.1_1 │ 4.341915e+08 │      2.009631e+08 │           9.449606e+07 │         8.588401e+07 │ b'\x01\x01\x00\x00\x00@\xa0\x88\x02=\x174@\x9d\xa3\xc5\xb3rPD@' │ b'\x01\x03\x00\x00\x00\x01\x00\x00\x00\xbe\x02\x00\x00\x07h\x11@ a4@\xd3\xa9\x02 \xb47D@\xcd\x15\x10 d`4@\xc4G\xfe?\xd27D@\xa0*\xf0_j_4@\xd8\x98\xf8\x1f\xf07D@$\xb3\xfe\xbf\x7f^4@.:\xf6?\x028D@\x88\xce\xf8'+11165                        │ [238, 97, ... +2] │ 19.888996 │ 20.090775 │ 40.628500 │
│ ALB    │ Albania │ ALB.2_1 │ 3.719427e+08 │      1.721516e+08 │           8.094841e+07 │         7.357106e+07 │ b'\x01\x01\x00\x00\x00\xe6M\xcan\xe1?4@\x87\xf1\xed\xcdg\xcbD@' │ b'\x01\x03\x00\x00\x00\x01\x00\x00\x00\xbb\x02\x00\x00\xa0\x16\x07\xc0\rl4@\xf5\x0e\xf9_\x15\xa4D@\xdfC\x15@)l4@u\xe3\xfe\x9f\x1a\xa4D@\x1f\x99\xf5\xff\xfdk4@:\xa9\xf5\x9f1\xa4D@\xb5O\t\x80\xd8k4@\xab\xc4\x02\x80_\xa4D@\x9fY\x12'+11117 │ [238, 97, ... +2] │ 19.734250 │ 20.249534 │ 41.589105 │
│ ALB    │ Albania │ ALB.3_1 │ 1.113524e+09 │      5.153885e+08 │           2.423439e+08 │         2.202575e+08 │ b'\x01\x01\x00\x00\x00\xb5?\xc3\x1e`\xa13@\x14\x15\xcd2B\xb9D@' │ b'\x01\x03\x00\x00\x00\x01\x00\x00\x00\x8f\x0c\x00\x00\xb3@\xfd_\\\xa33@9\xad\x04 \xa8\xabD@\x85\x1a\xf0\xbf\xb6\xa23@\x13U\t`\x14\xabD@~r\x14 \n\xa33@+\xf0\xfc?\xda\xaaD@\xa8O\xf6\xbf\xf1\xa33@\xd1\x9a\xf6\xff7\xaaD@\xc0@\x10'+51373   │ [238, 97, ... +2] │ 20.830796 │ 19.630373 │ 41.447333 │
│ ALB    │ Albania │ ALB.4_1 │ 7.954835e+08 │      3.681851e+08 │           1.731265e+08 │         1.573483e+08 │ b'\x01\x01\x00\x00\x00\x17\xb66{\xbb/4@m\xda[\xa0\x1f\x85D@'    │ b'\x01\x03\x00\x00\x00\x01\x00\x00\x00\xc5\x02\x00\x00\xe3\xe0\xf3?\xd0o4@\xe3\xa1\xf7\xdf\xadlD@ti\x15`\xcco4@\xb0\xb3\xf8\x7f\x9blD@A\x93\x0e \x8dp4@\x1f\x03\x05 flD@\xd8E\x13 \xf1p4@\xd5\x0f\x03\xc0\xa5kD@\xa6,\x01'+11277            │ [238, 97, ... +2] │ 20.494461 │ 20.186454 │ 41.040028 │
│ ALB    │ Albania │ ALB.5_1 │ 1.345159e+09 │      6.225993e+08 │           2.927561e+08 │         2.660753e+08 │ b"\x01\x01\x00\x00\x00\xee\x16g\xe7\xda\x9e3@Y\x90\xfc/'cD@"    │ b'\x01\x03\x00\x00\x00\x01\x00\x00\x00,\n\x00\x00x\x06\xf4_s\xbe3@\xfc\x0c\xfb\x7f\xcd9D@+\xb1\x00\xe0\xb7\xbe3@\x8c\xc1\xfd_y:D@\xf2.\x0f\x80U\xbe3@\x159\x02\xc0\xca:D@\xdf\xed\xee\xbf\x9d\xbd3@\xf6\xb7\x04\xe0\x1f;D@,B\x14'+41597     │ [238, 97, ... +2] │ 21.019778 │ 19.620528 │ 40.774633 │
└────────┴─────────┴─────────┴──────────────┴───────────────────┴────────────────────────┴──────────────────────┴─────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────────────────┴───────────┴───────────┴───────────┘

print("Assuming raster grid resolution : ",grid_param)

h3_res = dt.rast_to_h3_map(x=raw_grid.lon.median().as_scalar().execute(),
                           y=raw_grid.lat.median().as_scalar().execute())[str(grid_param)]["h3_res"]

print("Using base H3 resolution : ", h3_res)

ghsl_h3 = snoo.sn_project(raw_grid,res=h3_res)

Assuming raster grid resolution :  100
Using angles for meter grid.
Using base H3 resolution :  12
Assuming coordinates columns ('lon','lat')

print(ghsl_h3.count())
ghsl_h3.head()



┌───────┐
│ 14461 │
└───────┘





┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓
┃ lon       ┃ lat       ┃ band_var ┃ h3_id           ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩
│ float32   │ float32   │ uint16   │ string          │
├───────────┼───────────┼──────────┼─────────────────┤
│ -0.003524 │ 16.255964 │     1404 │ 8c59ac45c8d07ff │
│ -0.000778 │ 16.255051 │      275 │ 8c59ac45c16e5ff │
│  0.000138 │ 16.255051 │       29 │ 8c59ac45c1695ff │
│ -0.008100 │ 16.251389 │      836 │ 8c59ac45ccc09ff │
│ -0.007185 │ 16.251389 │      836 │ 8c59ac45cccc9ff │
└───────────┴───────────┴──────────┴─────────────────┘











ghsl_h3.h3_id.nunique()











┌───────┐
│ 14461 │
└───────┘













conn.list_tables()









['dwg', 'ghsl_h3', 'raw_grid']













# ghsl_h3 = conn.create_view("ghsl_h3",obj=ghsl_h3.group_by("h3_id").agg(band=_.band.sum(),x=_.x.first(),y=_.y.first()))
ghsl_h3 = ghsl_h3.group_by("h3_id").agg(band_var=_.band_var.sum(),lon=_.lon.first(),lat=_.lat.first())













ghsl_h3 = conn.create_table(obj=ghsl_h3,overwrite=True,name="ghsl_h3")













ghsl_df = ghsl_h3.execute()













Coordinates to plot only points






points_layer = pdk.Layer(
    "ScatterplotLayer",
    ghsl_df,
    pickable=False,
    opacity=0.8,
    stroked=False,
    filled=True,
    radius_scale=1,
    radius_min_pixels=1,
    radius_max_pixels=20,
    line_width_min_pixels=1,
    get_position = ["lon","lat"],
    get_radius=50,
    get_fill_color=[255, 140, 0, 255],
    get_line_color=[0, 0, 0, 0],
)













view_state_global = pdk.data_utils.compute_view(ghsl_df[["lon","lat"]])

# Create mapdeck object
deck_map_ghsl_h3 = pdk.Deck(layers=[
    # h3_layer_ghsl_h3,
    points_layer
    ]
             ,initial_view_state=view_state_global
             ,tooltip= {"text": "Value :  {band_var}"}
             ,map_style="road"
             )

deck_map_ghsl_h3.to_html("../../deck_maps/deck_ghsl_h3_grid.html",iframe_height=800,
                        #  open_browser=False
                         )










        
    








Selecting an area






dwg.filter(_.country.re_search("Nigeria")).select("gid_0","country")#.sql("Select ST_GeomFromWKB(geometry) as geom from dwg;")









┏━━━━━━━━┳━━━━━━━━━┓
┃ gid_0  ┃ country ┃
┡━━━━━━━━╇━━━━━━━━━┩
│ string │ string  │
├────────┼─────────┤
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ NGA    │ Nigeria │
│ …      │ …       │
└────────┴─────────┘











selected_country = "NGA"
selected_region = "NGA.20_1"















Rescaling approach to econ data






conn.drop_view("country",force=True)













country = conn.sql(f"""
SELECT sel_country.* EXCLUDE (color,centr,geom,radius,x,y),
         ghsl.*
         FROM ghsl_h3 AS ghsl
         JOIN (SELECT * EXCLUDE geometry, ST_GeomFromWKB(geometry) as geom FROM dwg where gid_1='{selected_region}') AS sel_country
         ON ST_Intersects(ST_Point(ghsl.lon,ghsl.lat),sel_country.geom);
         
""")













print(country.count())
country.head(3)











┌──────┐
│ 3015 │
└──────┘





┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┓
┃ gid_0  ┃ country ┃ gid_1    ┃ grp_usd_2015 ┃ services_usd_2015 ┃ manufacturing_usd_2015 ┃ agriculture_usd_2015 ┃ h3_id           ┃ band_var ┃ lon      ┃ lat       ┃
┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━┩
│ string │ string  │ string   │ float64      │ float64           │ float64                │ float64              │ string          │ int64    │ float32  │ float32   │
├────────┼─────────┼──────────┼──────────────┼───────────────────┼────────────────────────┼──────────────────────┼─────────────────┼──────────┼──────────┼───────────┤
│ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │ 8c580a8998501ff │       57 │ 8.591227 │ 12.553541 │
│ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │ 8c580ad1d672dff │       59 │ 8.268123 │ 12.550795 │
│ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │ 8c580ac6502cbff │      112 │ 8.479559 │ 12.545303 │
└────────┴─────────┴──────────┴──────────────┴───────────────────┴────────────────────────┴──────────────────────┴─────────────────┴──────────┴──────────┴───────────┘











gid_1_band = country.group_by("gid_1").agg(total_band=_.band_var.sum())
gid_1_band









┏━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ gid_1    ┃ total_band ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━┩
│ string   │ int64      │
├──────────┼────────────┤
│ NGA.20_1 │    3560655 │
└──────────┴────────────┘











country_gdf = (country
           .join(gid_1_band,country.gid_1==gid_1_band.gid_1,how="left")
           .mutate(band_frac=_.band_var/_.total_band,build_gdp=_.services_usd_2015+_.manufacturing_usd_2015)
           .mutate(h3_id_gdp=_.band_frac*_.build_gdp)).to_pandas()













print(country_gdf.shape)
country_gdf.head()









(3015, 16)









  
    

      
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      h3_id
      band_var
      lon
      lat
      gid_1_right
      total_band
      band_frac
      build_gdp
      h3_id_gdp
    

  
  
    

      0
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
    

    

      1
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580ad1d672dff
      59
      8.268123
      12.550795
      NGA.20_1
      3560655
      0.000017
      9.189241e+09
      152265.586867
    

    

      2
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580ac6502cbff
      112
      8.479559
      12.545303
      NGA.20_1
      3560655
      0.000031
      9.189241e+09
      289046.537781
    

    

      3
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580ac61b233ff
      110
      8.496035
      12.534320
      NGA.20_1
      3560655
      0.000031
      9.189241e+09
      283884.992464
    

    

      4
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580ac61b039ff
      9
      8.496035
      12.533404
      NGA.20_1
      3560655
      0.000003
      9.189241e+09
      23226.953929
    

  









Check that things add up






country_gdf[["gid_1","band_frac"]].groupby("gid_1").agg("sum")













  
    

      
      band_frac
    

    

      gid_1
      
    

  
  
    

      NGA.20_1
      1.0
    

  











country_gdf[["h3_id_gdp"]].sum()









h3_id_gdp    9.189241e+09
dtype: float64













country_gdf[["h3_id_gdp"]].sum()/country_gdf.shape[0]









h3_id_gdp    3.047841e+06
dtype: float64













Mapping






import scalenav.plotting as snplt













col_pal = "viridis"

col_pal = pypal.load_cmap(col_pal)













country_gdf["color"] = snplt.cmap((country_gdf.h3_id_gdp),palette=col_pal)

country_gdf.head(3)









Max input : 22994684.39, palette colors : 10









  
    

      
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      h3_id
      band_var
      lon
      lat
      gid_1_right
      total_band
      band_frac
      build_gdp
      h3_id_gdp
      color
    

  
  
    

      0
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
    

    

      1
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580ad1d672dff
      59
      8.268123
      12.550795
      NGA.20_1
      3560655
      0.000017
      9.189241e+09
      152265.586867
      [72, 33, 115, 255]
    

    

      2
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580ac6502cbff
      112
      8.479559
      12.545303
      NGA.20_1
      3560655
      0.000031
      9.189241e+09
      289046.537781
      [72, 33, 115, 255]
    

  











view_state = pdk.data_utils.compute_view(points=country_gdf[["lon","lat"]])













# # this map is not really usefull at high resolutions

layer_down = pdk.Layer(
    "ScatterplotLayer",
    country_gdf[["h3_id_gdp","lon","lat"]],
    pickable=False,
    opacity=0.4,
    stroked=False,
    filled=True,
    radius_scale=6,
    radius_min_pixels=1,
    radius_max_pixels=10,
    line_width_min_pixels=1,
    get_position = ["lon","lat"],
    get_radius=50,
    get_fill_color=[255, 140, 0, 255],
    get_line_color=[0, 0, 0, 0],
)













# Create mapdeck object
deck_map_down = pdk.Deck(layers=[
    layer_down
    ]
             ,initial_view_state=view_state
             ,tooltip= {"text": "Value :  {h3_id_gdp}"}
             ,height=800
             ,map_style="road"
             )

deck_map_down.to_html("../../deck_maps/deck_ghsl_h3_down_grid.html")










        
    








Gridding






rast_to_h3 = dt.rast_to_h3_map(x=country_gdf["lon"].mean(),y = country_gdf["lat"].mean())









Using angles for meter grid.













# ideally, this is precomputed, but for some reason it has not worked...
neighbs = rast_to_h3[str(grid_param)]["nn"]

country_gdf["h3_gridded"] = pd.Series(country_gdf.apply(lambda row: dt.centre_cell_to_square(h3_cell = row["h3_id"],neighbs=neighbs),axis=1).tolist())













country_gdf.dropna(subset=["h3_gridded"],inplace=True)













Gridded data set






ghsl_gridded = country_gdf.explode("h3_gridded")













print(ghsl_gridded.shape)
ghsl_gridded.head(3)









(129645, 18)









  
    

      
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      h3_id
      band_var
      lon
      lat
      gid_1_right
      total_band
      band_frac
      build_gdp
      h3_id_gdp
      color
      h3_gridded
    

  
  
    

      0
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998565ff
    

    

      0
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998505ff
    

    

      0
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998539ff
    

  











ghsl_gridded = ghsl_gridded[~ghsl_gridded.duplicated(subset=["h3_gridded"])]













print(ghsl_gridded.shape)
ghsl_gridded.reset_index(inplace=True,drop=True)









(114522, 18)













ghsl_gridded.head(3)













  
    

      
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      h3_id
      band_var
      lon
      lat
      gid_1_right
      total_band
      band_frac
      build_gdp
      h3_id_gdp
      color
      h3_gridded
    

  
  
    

      0
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998565ff
    

    

      1
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998505ff
    

    

      2
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      8c580a8998501ff
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998539ff
    

  











ghsl_gridded.set_index("h3_id",inplace=True,drop=True)













ghsl_gridded = ghsl_gridded.join(ghsl_gridded.value_counts("h3_id"),how="left",sort=False,validate="m:1")













ghsl_gridded["h3_id_gdp_gridded"] = np.round(ghsl_gridded["h3_id_gdp"]/ghsl_gridded["count"])













ghsl_gridded.reset_index(inplace=True,drop=False)













ghsl_gridded.dropna(axis=0,subset=["h3_gridded"],inplace=True)













print(ghsl_gridded.shape)
ghsl_gridded.head(3)









(114522, 20)









  
    

      
      h3_id
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      band_var
      lon
      lat
      gid_1_right
      total_band
      band_frac
      build_gdp
      h3_id_gdp
      color
      h3_gridded
      count
      h3_id_gdp_gridded
    

  
  
    

      0
      8c580a8998501ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998565ff
      43
      3421.0
    

    

      1
      8c580a8998501ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998505ff
      43
      3421.0
    

    

      2
      8c580a8998501ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998539ff
      43
      3421.0
    

  











# ghsl_gridded

conn.sql("""select *, 
    array_extract(h3_cell_to_latlng(h3_gridded),2) as x_h3, 
    array_extract(h3_cell_to_latlng(h3_gridded),1) as y_h3
    from ghsl_gridded""")









┏━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┓
┃ h3_id           ┃ gid_0  ┃ country ┃ gid_1    ┃ grp_usd_2015 ┃ services_usd_2015 ┃ manufacturing_usd_2015 ┃ agriculture_usd_2015 ┃ band_var ┃ lon      ┃ lat       ┃ gid_1_right ┃ total_band ┃ band_frac ┃ build_gdp    ┃ h3_id_gdp     ┃ color            ┃ h3_gridded      ┃ count ┃ h3_id_gdp_gridded ┃ x_h3     ┃ y_h3      ┃
┡━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━┩
│ string          │ string │ string  │ string   │ float64      │ float64           │ float64                │ float64              │ int64    │ float32  │ float32   │ string      │ int64      │ float64   │ float64      │ float64       │ array<int32>     │ string          │ int64 │ float64           │ float64  │ float64   │
├─────────────────┼────────┼─────────┼──────────┼──────────────┼───────────────────┼────────────────────────┼──────────────────────┼──────────┼──────────┼───────────┼─────────────┼────────────┼───────────┼──────────────┼───────────────┼──────────────────┼─────────────────┼───────┼───────────────────┼──────────┼───────────┤
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998565ff │    43 │            3421.0 │ 8.590692 │ 12.553376 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998505ff │    43 │            3421.0 │ 8.591150 │ 12.553385 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998539ff │    43 │            3421.0 │ 8.591488 │ 12.553281 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998521ff │    43 │            3421.0 │ 8.591076 │ 12.553109 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998515ff │    43 │            3421.0 │ 8.591609 │ 12.553393 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998541ff │    43 │            3421.0 │ 8.591009 │ 12.553876 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a899852bff │    43 │            3421.0 │ 8.591197 │ 12.553221 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998e5bff │    43 │            3421.0 │ 8.590739 │ 12.553212 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a8998531ff │    43 │            3421.0 │ 8.591535 │ 12.553117 │
│ 8c580a8998501ff │ NGA    │ Nigeria │ NGA.20_1 │ 1.151516e+10 │      8.028410e+09 │           1.160831e+09 │         2.325922e+09 │       57 │ 8.591227 │ 12.553541 │ NGA.20_1    │    3560655 │  0.000016 │ 9.189241e+09 │ 147104.041549 │ [72, 33, ... +2] │ 8c580a899854bff │    43 │            3421.0 │ 8.591131 │ 12.553988 │
│ …               │ …      │ …       │ …        │            … │                 … │                      … │                    … │        … │        … │         … │ …           │          … │         … │            … │             … │ …                │ …               │     … │                 … │        … │         … │
└─────────────────┴────────┴─────────┴──────────┴──────────────┴───────────────────┴────────────────────────┴──────────────────────┴──────────┴──────────┴───────────┴─────────────┴────────────┴───────────┴──────────────┴───────────────┴──────────────────┴─────────────────┴───────┴───────────────────┴──────────┴───────────┘











ghsl_gridded_zoom = conn.sql(
f"""
Select * from 
    (select *
    EXCLUDE (lon,lat,gid_1_right),
    array_extract(h3_cell_to_latlng(h3_gridded),2) as x_h3, 
    array_extract(h3_cell_to_latlng(h3_gridded),1) as y_h3
    from ghsl_gridded)
where (x_h3 <= {limits[2]}) and (x_h3 >= {limits[0]}) and (y_h3 <= {limits[3]}) and (y_h3 >= {limits[1]});
""").execute()


print(ghsl_gridded_zoom.shape)
ghsl_gridded_zoom.head(3)









(90604, 19)









  
    

      
      h3_id
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      band_var
      total_band
      band_frac
      build_gdp
      h3_id_gdp
      color
      h3_gridded
      count
      h3_id_gdp_gridded
      x_h3
      y_h3
    

  
  
    

      0
      8c580a0a6b209ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      131
      3560655
      0.000037
      9.189241e+09
      338081.218298
      [72, 33, 115, 255]
      8c580a0a6b267ff
      43
      7862.0
      8.354838
      12.410060
    

    

      1
      8c580a0a6b209ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      131
      3560655
      0.000037
      9.189241e+09
      338081.218298
      [72, 33, 115, 255]
      8c580a0a6b23bff
      43
      7862.0
      8.354643
      12.409674
    

    

      2
      8c580a0a6b209ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      131
      3560655
      0.000037
      9.189241e+09
      338081.218298
      [72, 33, 115, 255]
      8c580a0a448b7ff
      43
      7862.0
      8.355027
      12.409407
    

  











band_pal = pypal.load_cmap("Bmsurface")
ghsl_gridded_zoom["band_color"] = cmap(ghsl_gridded_zoom.band_frac,palette=band_pal)









Max input : 0.00, palette colors : 6













ghsl_gridded_zoom.columns









Index(['h3_id', 'gid_0', 'country', 'gid_1', 'grp_usd_2015',
       'services_usd_2015', 'manufacturing_usd_2015', 'agriculture_usd_2015',
       'band_var', 'total_band', 'band_frac', 'build_gdp', 'h3_id_gdp',
       'color', 'h3_gridded', 'count', 'h3_id_gdp_gridded', 'x_h3', 'y_h3',
       'band_color'],
      dtype='object')













h3_layer_ghsl_h3_gridded = pdk.Layer(
    "H3HexagonLayer",
    ghsl_gridded_zoom[["band_frac","h3_gridded","color","band_color"]],
    pickable=True,
    stroked=True,
    filled=True,
    opacity=.5,
    extruded=False,
    get_hexagon= "h3_gridded",
    get_fill_color = "band_color",
    get_line_color= [0, 0, 0, 100],
    line_width_min_pixels=0,
    line_width_max_pixels=1,
)


# layer_down = pdk.Layer(
#     "ScatterplotLayer",
#     country_gdf[["h3_id_gdp","x","y"]],
#     pickable=False,
#     opacity=0.8,
#     stroked=False,
#     filled=True,
#     radius_scale=10,
#     radius_min_pixels=1,
#     radius_max_pixels=20,
#     line_width_min_pixels=0,
#     line_width_max_pixels=0,
#     get_position = ["x","y"],
#     get_radius=25,
#     get_fill_color=[255, 140, 0, 255],
#     get_line_color=[0, 0, 0, 0],
# )













# Create mapdeck object

deck_map_ghsl_h3_gridded = pdk.Deck(layers=[h3_layer_ghsl_h3_gridded, 
                                            # layer_down
                                            ]
             ,initial_view_state=view_state_zoomed
             ,tooltip= {"text": "Value :  {band_frac}"}
             ,map_style="road"
             )

deck_map_ghsl_h3_gridded.to_html("../../deck_maps/deck_ghsl_nres_h3_gridded.html",
                                 iframe_height=800,)










        
    










Changing Scales






ghsl_gridded.rename(columns={"h3_id_gdp_gridded" : "h3_id_gdp_gridded_var",
                             "h3_id" : "h3_gridded_parent"
                             },inplace=True)













ghsl_gridded.rename(columns={"h3_gridded" : "h3_id"
                             },inplace=True)













ghsl_gridded.head(3)













  
    

      
      h3_gridded_parent
      gid_0
      country
      gid_1
      grp_usd_2015
      services_usd_2015
      manufacturing_usd_2015
      agriculture_usd_2015
      band_var
      lon
      lat
      gid_1_right
      total_band
      band_frac
      build_gdp
      h3_id_gdp
      color
      h3_id
      count
      h3_id_gdp_gridded_var
    

  
  
    

      0
      8c580a8998501ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998565ff
      43
      3421.0
    

    

      1
      8c580a8998501ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998505ff
      43
      3421.0
    

    

      2
      8c580a8998501ff
      NGA
      Nigeria
      NGA.20_1
      1.151516e+10
      8.028410e+09
      1.160831e+09
      2.325922e+09
      57
      8.591227
      12.553541
      NGA.20_1
      3560655
      0.000016
      9.189241e+09
      147104.041549
      [72, 33, 115, 255]
      8c580a8998539ff
      43
      3421.0
    

  











h3_gridded_new_scale = sd.change_res(ghsl_gridded,level=-3)













print(h3_gridded_new_scale.shape)
h3_gridded_new_scale.head()









(2093, 4)









  
    

      
      h3_id
      child_cells
      band_var
      h3_id_gdp_gridded_var
    

  
  
    

      0
      89580a00523ffff
      [8a580a005217fff, 8a580a005237fff]
      3345
      200760.0
    

    

      1
      89580a0052bffff
      [8a580a0052a7fff]
      5575
      334600.0
    

    

      2
      89580a0053bffff
      [8a580a00538ffff, 8a580a00539ffff]
      39025
      2342200.0
    

    

      3
      89580a01423ffff
      [8a580a014217fff]
      114
      6842.0
    

    

      4
      89580a0142bffff
      [8a580a014287fff, 8a580a014297fff, 8a580a01429...
      51338
      3454196.0
    

  











h3_gridded_new_scale["color"] = cmap(h3_gridded_new_scale.h3_id_gdp_gridded_var,palette=col_pal,log=True)









Max input : 18.68, palette colors : 10













#####

h3_layer_ghsl_h3_gridded_new_scale = pdk.Layer(
    "H3HexagonLayer",
    h3_gridded_new_scale,
    pickable=True,
    stroked=True,
    filled=True,
    opacity=.5,
    extruded=False,
    get_hexagon= "h3_id",
    get_fill_color = "color",
    get_line_color= [0, 0, 0, 100],
    line_width_min_pixels=0,
    line_width_max_pixels=1,
)













# Create mapdeck object
deck_map_ghsl_h3_gridded_rescaled = pdk.Deck(layers=[h3_layer_ghsl_h3_gridded_new_scale]
             ,initial_view_state=view_state_zoomed
             ,tooltip= {"text": "Value :  {h3_id_gdp_gridded_var}"}
             ,map_style="road"
             )

deck_map_ghsl_h3_gridded_rescaled.to_html("../../deck_maps/deck_ghsl_nres_h3_gridded_new_scale.html",iframe_height=800)