{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ed54b757-50b9-4e30-89b4-8b16acea52ab",
   "metadata": {},
   "source": [
    "# Preprocessing CBN Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e6731a7-b7f1-4560-af95-4f357fc783d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "from cng.utils import *\n",
    "from utils import *\n",
    "from h3_utils import *\n",
    "s3 = s3_client()\n",
    "\n",
    "import os\n",
    "os.chdir('../data/')\n",
    "\n",
    "duckdb_install_h3()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2219c881-1017-4def-9d0c-c83b5541b5d2",
   "metadata": {},
   "source": [
    "## Counties **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6435342f-ec14-4407-bc76-c276260b08aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('counties',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Counties'\n",
    "name = 'CA_counties'\n",
    "\n",
    "unzip(s3, folder = folder, file = '30x30_Counties.zip')\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8519d194-15f5-4bbe-8304-d6b9dbf1fbf3",
   "metadata": {},
   "source": [
    "## Climate Zones **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea5ed097-6651-4ce0-ba2f-be8d1894737b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('climate_zones',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Climate_zones'\n",
    "name = 'climate_zones_10'\n",
    "download(s3, folder = folder, file = 'clusters_10.tif')\n",
    "process_raster(s3, folder = folder, file = 'clusters_10.tif', file_name = f\"{name}.tif\")\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}_processed.parquet\", cols = cols,\n",
    "          # zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70bcd1f7-ecee-4ad4-8128-797a57be39bb",
   "metadata": {},
   "source": [
    "## Ecoregions **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "456253b5-ea00-4674-8d3b-4b9c1f049888",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('ecoregion',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Ecoregion'\n",
    "name = 'ACE_ecoregions'\n",
    "\n",
    "unzip(s3, folder = folder, file = '30x30_Ecoregions.zip')\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\")\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09bb6c74-4003-4cbb-aa7a-8a8654be15ea",
   "metadata": {},
   "source": [
    "## Habitat"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59a91e0a-1076-4d4a-9012-300987f4fb10",
   "metadata": {},
   "source": [
    "#### 13 class major habitat types **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de501ac3-f6fe-44f5-86c1-afba763147ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "con = ibis.duckdb.connect('habitat',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Habitat'\n",
    "name = 'fveg22_1'\n",
    "unzip(s3, folder = folder, file = 'fveg221gdb.zip')\n",
    "\n",
    "command = [\n",
    "    \"gdalwarp\",\n",
    "    \"-of\", \"GTiff\",\n",
    "    'fveg22_1.gdb',\n",
    "    'fveg22_1.tif' \n",
    "    ]\n",
    "\n",
    "subprocess.run(command, check=True)\n",
    "process_raster(s3, folder = folder, file = f\"{name}.tif\")\n",
    "upload(folder = folder, file = f'{name}_processed.tif.aux.xml')\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}_processed.parquet\", cols = cols,\n",
    "#           zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "070eddca-e26a-44b8-9878-eb6368940f1e",
   "metadata": {},
   "source": [
    "#### 60+ class habitat types"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20839c2d-ff08-4264-8d58-3bfa1e3347aa",
   "metadata": {},
   "source": [
    "## ACE Biodiversity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "477860c7-5f7e-42ab-8a87-4e3d39f77396",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('ace',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'ACE_biodiversity'\n",
    "name = 'ACE_terrestrial_biodiversity_summary_ds2739'\n",
    "\n",
    "download(s3, folder = folder, file = 'Terrestrial_Biodiversity_Summary_-_ACE_[ds2739].geojson',\n",
    "         file_name = f\"{name}.geojson\")\n",
    "\n",
    "process_vector(s3, folder = folder, file = f\"{name}.geojson\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.geojson\")\n",
    "gdf = gpd.read_parquet(f\"{name}.parquet\")\n",
    "\n",
    "# cols = [item for item in cols if item not in [\"Hex_ID\",\"Shape__Area\",\"Shape__Length\"]]\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3763a569-42ce-479a-9deb-7b3a7f018f79",
   "metadata": {},
   "source": [
    "#### ACE BioRank and Rare Rank "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87216b37-2867-45b2-b77d-2dbcdd87fafa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data to rank 5.\n",
    "ACE_rank_files = ['ACE_biorank_statewide','ACE_biorank_ecoregion',\n",
    "                  'ACE_rarerank_statewide','ACE_rarerank_ecoregion']\n",
    "             \n",
    "ACE_rank_cols =  ['BioRankSW','BioRankEco','RarRankSW','RarRankEco']   \n",
    "\n",
    "for col,name in zip(ACE_rank_cols,ACE_rank_files):\n",
    "    cols = ['OBJECTID', 'Hex_ID', 'Eco_Sect', 'Eco_Name',\n",
    "            'County', 'Shape__Area', 'Shape__Length', 'geometry']\n",
    "    cols.append(col) #select only the cols we want + the new col. \n",
    "    rank_df = gdf[gdf[col]==5][cols]# filter ranks = 5\n",
    "    process_vector(s3, folder = 'ACE_biodiversity/'+name, file = name+'.parquet',gdf = rank_df)\n",
    "    convert_pmtiles(con, s3, folder ='ACE_biodiversity/'+name, file = name+'.parquet')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77a45a2f-c379-49cd-81da-625b16da37b7",
   "metadata": {},
   "source": [
    "#### Other ACE Biodiversity **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45dec0c5-4af1-4046-9909-13fe7c37fdfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "ACE_files = ['ACE_amphibian_richness','ACE_reptile_richness',\n",
    "            'ACE_bird_richness','ACE_mammal_richness',\n",
    "            'ACE_rare_amphibian_richness','ACE_rare_reptile_richness',\n",
    "            'ACE_rare_bird_richness','ACE_rare_mammal_richness',\n",
    "            'ACE_endemic_amphibian_richness','ACE_endemic_reptile_richness',\n",
    "            'ACE_endemic_bird_richness','ACE_endemic_mammal_richness']\n",
    "\n",
    "ACE_cols = ['NtvAmph','NtvRept','NtvBird','NtvMamm','RarAmph','RarRept','RarBird','RarMamm',\n",
    "            'AmphEndem','ReptEndem','BirdEndem','MammEndem']\n",
    "\n",
    "for col,name in zip(ACE_cols,ACE_files):\n",
    "    cols = ['OBJECTID', 'Hex_ID', 'Eco_Sect', 'Eco_Name',\n",
    "            'County', 'Shape__Area', 'Shape__Length', 'geometry']\n",
    "    cols.append(col) #select only the cols we want + the new col. \n",
    "    if name in ['NtvAmph','NtvRept','NtvBird','NtvMamm']:\n",
    "        percentile = 0.8\n",
    "    else: \n",
    "        percentile = 0.95\n",
    "    threshold = gdf[col].quantile(percentile)\n",
    "    ace = gdf[gdf[col]>=threshold][cols]\n",
    "    process_vector(s3, folder = 'ACE_biodiversity/'+name, file = name+'.parquet',gdf = ace)\n",
    "    convert_pmtiles(con, s3, folder ='ACE_biodiversity/'+name, file = name+'.parquet')\n",
    "\n",
    "\n",
    "# calculate 80% percentile, filter to those >= threshold. \n",
    "# subset to calculate acres within each network, % of feature conserved and % of network "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6991222f-7d24-4f10-9ee0-db20513405d6",
   "metadata": {},
   "source": [
    "## Biodiversity"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8c107d9-de0c-4a02-9c8c-9ab1b86bedbc",
   "metadata": {},
   "source": [
    "#### Plant richness **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b65e0f6-120e-45ef-b78a-13f623e70ede",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('plant',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Biodiversity_unique/Plant_richness'\n",
    "name = 'species_D'\n",
    "\n",
    "download(s3, folder = folder, file = f\"{name}.tif\")\n",
    "filter_raster(s3, folder = folder, file = f\"{name}.tif\", percentile = 80)\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}_processed.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38a2e81e-0862-474c-8e87-3d862191ff6a",
   "metadata": {},
   "source": [
    "#### Rarity-weighted endemic plant richness **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8899748-ebbd-4807-a9b1-a095d94e7208",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('end_plant',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Biodiversity_unique/Rarityweighted_endemic_plant_richness'\n",
    "name = 'endemicspecies_E'\n",
    "\n",
    "download(s3, folder = folder, file = f\"{name}.tif\")\n",
    "filter_raster(s3, folder = folder, file = f\"{name}.tif\", percentile = 80)\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}_processed.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61cd71e9-47b8-42d4-84a6-ff0aace484f7",
   "metadata": {},
   "source": [
    "#### Abundance for 26 bird species"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c82bde3-6e2e-4919-a82d-b36f192d97cd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d9ef590a-8500-4ec1-9b70-7afc52483832",
   "metadata": {},
   "source": [
    "## Connectivity and Resilience\n",
    "#### Resilient Connected Network - all categories **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b3ad95a-8448-405d-b73e-ad425430126e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('CRN',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Connectivity_resilience/Resilient_connected_network_allcategories'\n",
    "name = 'rcn_wIntactBioCat_caOnly_2020-10-27'\n",
    "\n",
    "process_raster(s3, folder = folder, file = f\"{name}.tif\")\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}_processed.parquet\", cols = cols, \n",
    "           # zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4bb165c-c9fb-4fe5-a04c-f9d96c6a6a81",
   "metadata": {},
   "source": [
    "#### Present day connectivity - all categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13d25eb2-1e74-48c8-a6e8-2bbf8dd245be",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2ef86e94-2629-464e-9b8c-20b3616ec294",
   "metadata": {},
   "source": [
    "#### Climate migration routes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe1adebe-9976-44eb-a665-00b36cfef197",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e08ac0ec-d4e7-4c53-95a0-1211f4db5f97",
   "metadata": {},
   "source": [
    "## Freshwater Resources"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bb9c682-58e6-4a22-81bc-10e0b74e4e3f",
   "metadata": {},
   "source": [
    "#### Freshwater species richness"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b19f0f30-084b-4db2-b8c5-85dd86669752",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c6cbf153-5fb4-4890-b019-cb5a64f534ec",
   "metadata": {},
   "source": [
    "#### Wetlands **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "822d17e4-af3c-450b-b02f-979cfacfdad3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('wetlands',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Freshwater_resources/Wetlands'\n",
    "name = 'CA_wetlands'\n",
    "\n",
    "# only pick a subset \n",
    "unzip(s3, folder = folder, file = 'CA_geodatabase_wetlands.zip')\n",
    "gdf = gpd.read_file('CA_geodatabase_wetlands.gdb')\n",
    "wetlands = ['Freshwater Emergent Wetland', 'Freshwater Forested/Shrub Wetland', 'Estuarine and Marine Wetland']\n",
    "gdf = gdf[gdf['WETLAND_TYPE'].isin(wetlands)]\n",
    "\n",
    "process_vector(s3, folder = folder, file = f\"{name}.parquet\", gdf = gdf)\n",
    "convert_pmtiles(con, s3, folder =folder, file = f\"{name}.parquet\")\n",
    "\n",
    "# cols = [item for item in cols if item not in ['ACRES','Shape_Length','Shape_Area','__index_level_0__']]\n",
    "# geom_to_h3(con, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a78b9696-6dfb-4332-9288-56f9aa004f4d",
   "metadata": {},
   "source": [
    "#### Groundwater dependent ecosystems"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "838e7241-eb4a-49ff-b746-c9db6c16983a",
   "metadata": {},
   "source": [
    "#### Streams by order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a4a94dd-3374-4ab7-be7d-bc650e1d9d16",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "bc762a29-4094-4b79-a49b-393f43cd1ccf",
   "metadata": {},
   "source": [
    "#### Perennial streams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9614c0d-890b-4608-b1c2-132b943dddd9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6ffaf55b-7bfc-469a-a867-8fc58802ff11",
   "metadata": {},
   "source": [
    "#### Fish passage barriers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df1391f0-1980-4741-8f0c-4fce7f3a27f5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2284a1e0-0f1d-497e-9104-e2e736b66a4c",
   "metadata": {},
   "source": [
    "## NBS and Agriculture"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b53f5a5-c273-4191-ac35-56090f5c6d7d",
   "metadata": {},
   "source": [
    "#### Drinking water source watersheds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d874e9c-b206-42b4-a8ea-397b75b5dcf6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c208b7ee-fbdb-45b1-8258-57eae516bf2d",
   "metadata": {},
   "source": [
    "#### Farmland + Land suitable for grazing **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdf7061f-7598-4303-bb77-38f836feac8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('farm',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'NBS_agriculture/Farmland_all'\n",
    "name = 'Important_Farmland_2018'\n",
    "unzip(s3, folder = folder, file = f\"{name}.zip\")\n",
    "process_vector(s3, folder = folder, file = f\"{name}.gdb\",crs = \"epsg:4326\")\n",
    "\n",
    "convert_pmtiles(con, s3, folder = folder, file =f\"{name}.parquet\")\n",
    "\n",
    "# cols = [item for item in cols if item not in ['Shape_Length','Shape_Area']]\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)\n",
    "\n",
    "# only pick a subset \n",
    "folder = 'NBS_agriculture/Farmland_all/Farmland'\n",
    "name = 'Farmland_2018'\n",
    "gdf = gpd.read_file('Important_Farmland_2018.gdb')\n",
    "farmland_type = ['P','S','L','U'] # prime, statewide importance, local importance, unique\n",
    "gdf_farmland = gdf[gdf['polygon_ty'].isin(farmland_type)]\n",
    "process_vector(s3, folder = folder, file = f\"{name}.parquet\", gdf = gdf_farmland)\n",
    "convert_pmtiles(con, s3, folder = folder, file =f\"{name}.parquet\")\n",
    "\n",
    "# grazing lands \n",
    "folder = 'NBS_agriculture/Farmland_all/Lands_suitable_grazing'\n",
    "name = 'Grazing_land_2018'\n",
    "gdf_grazing = gdf[gdf['polygon_ty'] == 'G']\n",
    "process_vector(s3, folder = folder, file = f\"{name}.parquet\", gdf = gdf_grazing)\n",
    "convert_pmtiles(con, s3, folder = folder, file =f\"{name}.parquet\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2d52d1e-1fab-4233-ac30-3dea19acdf87",
   "metadata": {},
   "source": [
    "#### Carbon storage **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a840510-e058-4e01-992f-0a0f7677e26c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ce0dae5a-4040-43e3-93fd-2d4ac27a77b2",
   "metadata": {},
   "source": [
    "## Climate Risks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b52a326-54eb-4c49-9f54-001dba66eeb4",
   "metadata": {},
   "source": [
    "#### Fire perimeters **\n",
    "\n",
    "Only YEAR >= 2014. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fabccc3a-4973-440d-8c21-8c6616dbd0b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('fire',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Climate_risks/Historical_fire_perimeters'\n",
    "name = 'calfire_2023'\n",
    "\n",
    "unzip(s3, folder = folder, file = 'fire23-1gdb.zip')\n",
    "gdf = gpd.read_file('fire23_1.gdb')\n",
    "gdf = gdf[~gdf['YEAR_'].isna()]\n",
    "gdf['YEAR_'] = gdf['YEAR_'].astype('int64')\n",
    "gdf = gdf[gdf['YEAR_']>=2014]\n",
    "process_vector(s3, folder = folder, file = f\"{name}.parquet\", gdf = gdf)\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "\n",
    "# cols = [item for item in cols if item not in ['Shape_Length','Shape_Area']]\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c3cdd98-dba4-40d6-949a-5489d8594712",
   "metadata": {},
   "source": [
    "#### Flood hazard zones **"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cf26682-fc39-4253-8e96-74484480cdcf",
   "metadata": {},
   "source": [
    "#### Sea level rise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0023aab7-36c8-4d85-9d34-60fb5a3eaa61",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b5e08a43-fcfb-48f6-9163-10f039d18474",
   "metadata": {},
   "source": [
    "#### Mid-century habitat climate exposure **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61796bb3-824f-44fa-bb6c-2b36843cdb11",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "First mask out non-natural lands.\n",
    "A binary natural vs. non-natural  land mask is included in the data package. \n",
    "Use the combined group of all values < 0 and >=0.95 as exposed.  \n",
    "Do seperately for both climate models - CNRM and MIROC.\n",
    "'''\n",
    "\n",
    "unzip(s3, folder = 'Climate_risks/Mid-century_habitat_climate_exposure', file = 'Midcentury_habitat_climate_exposure.zip')\n",
    "\n",
    "# still need to do "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9362336e-2b3a-4d93-8048-6a4abe401ede",
   "metadata": {},
   "source": [
    "## Progress data - newly protected"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc0af89e-d9a5-49dd-8628-c6d80389dcd4",
   "metadata": {},
   "source": [
    "#### Newly counted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "849c512d-17b1-4d06-a9e9-b6428254c351",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('new_land',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Progress_data_new_protection/Newly_counted_lands'\n",
    "name = 'newly_counted_lands_2024'\n",
    "\n",
    "unzip(s3, folder = folder, file = f\"{name}.shp.zip\")\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\",crs = \"epsg:4326\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "\n",
    "# cols = [item for item in cols if item not in ['Shape_Leng', 'Shape_Area']]\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7c4c58e-8639-4af4-b8a7-48257d2e7479",
   "metadata": {},
   "source": [
    "#### DAC **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6628d7c-c3bc-4974-8c1a-0d1c362bb419",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('dac',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Progress_data_new_protection/DAC'\n",
    "name = 'DAC_2022'\n",
    "\n",
    "unzip(s3, folder = folder, file = 'sb535dacgdbf2022gdb.zip')\n",
    "process_vector(s3, folder = folder, file = 'SB535DACgdb_F_2022.gdb', file_name = f\"{name}.parquet\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a27e5479-0456-4506-851d-f5991abcecbd",
   "metadata": {},
   "source": [
    "#### Priority populations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc236b85-9fc4-4589-8dd9-5efb7f2e9614",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "con = ibis.duckdb.connect('priority_pop',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Progress_data_new_protection/Priority_populations'\n",
    "name = 'CalEnviroScreen4'\n",
    "# unzip(s3, folder = folder, file = 'Priority Populations 4.0 Geodatabase.zip')\n",
    "\n",
    "gdf = (con.read_geo('Priority Populations 4.0 Combined Layer.gdb')\n",
    "       .mutate(id=ibis.row_number().over()) #making a unique id \n",
    "      ).execute().set_crs('EPSG:3857')\n",
    "\n",
    "process_vector(s3, folder = folder, file = 'Priority Populations 4.0 Combined Layer.gdb',\n",
    "               file_name = f\"{name}.parquet\", gdf = gdf)\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "\n",
    "# cols = [item for item in cols if item not in ['Shape_Length','Shape_Area']]\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df1a939c-cb89-4a2f-8309-2819fe52ac45",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a919ff5f-dff3-4db7-81c2-694f07f37d1d",
   "metadata": {},
   "source": [
    "#### Low income communities **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "851f5cbf-8ab7-492e-9c49-6c45f8c35b76",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = ibis.duckdb.connect('low',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Progress_data_new_protection/Low_income_communities'\n",
    "name = 'low_income_CalEnviroScreen4'\n",
    "\n",
    "unzip(s3, folder = folder, file = 'Priority Populations 4.0 Geodatabase.zip')\n",
    "\n",
    "gdf = gpd.read_file('Priority Populations 4.0 Combined Layer.gdb')\n",
    "gdf = gdf[gdf['Designatio'] =='Low-income community']\n",
    "process_vector(s3, folder = folder, file = f\"{name}.parquet\", gdf = gdf)\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a60e0012-b5ca-4b03-8671-64806c54f115",
   "metadata": {},
   "source": [
    "## Base layer for denominator "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46ee1ce8-dae5-4e61-b12c-4a14ac60f866",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = ibis.duckdb.connect('base_layer',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'Progress_data_new_protection/Land_Status_Zone_Ecoregion_Counties'\n",
    "name = 'all_regions_reGAP_county_eco'\n",
    "\n",
    "unzip(s3, folder = folder, file = 'Land_Status_Zone_Ecoregion_Counties.shp.zip')\n",
    "process_vector(s3, folder = folder, file = 'Land_Status_Zone_Ecoregion_Counties.shp',\n",
    "               file_name = f\"{name}.parquet\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols = cols, zoom = 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af486c71-3b84-4685-9794-fbacbf5f81c7",
   "metadata": {},
   "source": [
    "# CPAD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf6c896f-65f3-403a-abd9-f7dec2f4f112",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = ibis.duckdb.connect('cpad',extensions = [\"spatial\", \"h3\"])\n",
    "set_secrets(con)\n",
    "\n",
    "folder = 'CPAD'\n",
    "name = 'cced_2024b_release'\n",
    "\n",
    "unzip(s3, folder = folder, file = f\"{name}.shp.zip\")\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\", crs=\"EPSG:3310\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\", crs=\"EPSG:4326\")\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols= cols, zoom = 8)\n",
    "\n",
    "name = 'cpad_2024b_release'\n",
    "unzip(s3, folder = folder, file = f\"{name}.shp.zip\")\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\", crs=\"EPSG:3310\")\n",
    "convert_pmtiles(con, s3, folder = folder, file = f\"{name}.parquet\")\n",
    "process_vector(s3, folder = folder, file = f\"{name}.shp\", crs=\"EPSG:4326\")\n",
    "# convert_h3(con, s3, folder = folder, file = f\"{name}.parquet\", cols= cols, zoom = 8)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}