{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b313a218-4778-4d5b-9036-f0370d4212a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "import streamlit as st\n",
    "\n",
    "conn = ibis.duckdb.connect(extensions=[\"spatial\"])\n",
    "\n",
    "state_boundaries = \"https://data.source.coop/cboettig/us-boundaries/us-state-territory.parquet\"\n",
    "county_boundaries = \"https://data.source.coop/cboettig/us-boundaries/us-county.parquet\"\n",
    "states = conn.read_parquet(state_boundaries).rename(state_id = \"STUSPS\", state = \"NAME\")\n",
    "county = conn.read_parquet(county_boundaries).rename(county = \"NAMELSAD\", state = \"STATE_NAME\")\n",
    "\n",
    "localities_boundaries = \"us_localities.parquet\"\n",
    "locality = conn.read_parquet(localities_boundaries)\n",
    "\n",
    "\n",
    "votes = conn.read_csv(\"landvote.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba4d8915-cde3-4ef9-ad8c-7759ed2c8a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "vote_county = (votes\n",
    "               .filter(_[\"Jurisdiction Type\"] == \"County\")\n",
    "               .rename(county = \"Jurisdiction Name\", state_id = \"State\")\n",
    "               .mutate(key = _.county + ibis.literal('-') + _.state_id)\n",
    "               .rename(amount = 'Conservation Funds at Stake', yes = '% Yes')\n",
    "               .mutate(amount_n=_.amount.replace('$', '').replace(',', '').cast('float'))\n",
    "               .mutate(log_amount=_.amount_n.log())\n",
    "               .mutate(year=_['Date'].year().cast('int32'))\n",
    "               .select('key', 'Status', 'yes', 'year', 'amount', 'log_amount', )\n",
    "               )\n",
    "df_county = (county\n",
    "            .join(states.select(\"state\", \"state_id\"), \"state\")\n",
    "            .mutate(key = _.county + ibis.literal('-') + _.state_id)\n",
    "            .select('key', 'geometry')\n",
    "            .right_join(vote_county, \"key\")\n",
    "            .drop('key_right')\n",
    "            .mutate(jurisdiction = ibis.literal(\"County\"))\n",
    "            .cast({\"geometry\": \"geometry\"})\n",
    "             )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cce23c9-245c-4c28-9523-0231eb5acc17",
   "metadata": {},
   "outputs": [],
   "source": [
    "vote_local = (votes\n",
    "                .filter(_[\"Jurisdiction Type\"] == \"Municipal\")\n",
    "                .rename(city = \"Jurisdiction Name\", state_id = \"State\")\n",
    "                .mutate(key = _.city + ibis.literal('-') + _.state_id)\n",
    "                .rename(amount = 'Conservation Funds at Stake', yes = '% Yes')\n",
    "                .mutate(amount_n=_.amount.replace('$', '').replace(',', '').cast('float'))\n",
    "                .mutate(log_amount=_.amount_n.log())\n",
    "                .mutate(year=_['Date'].year().cast('int32'))\n",
    "                .select('key', 'Status', 'yes', 'year', 'amount', 'log_amount', )\n",
    "                )\n",
    "\n",
    "df_local = (locality\n",
    "            .mutate(key = _.name + ibis.literal('-') + _.state_id)\n",
    "            .select('key', 'geometry')\n",
    "            .right_join(vote_local, \"key\")\n",
    "            .drop('key_right')\n",
    "            .mutate(jurisdiction = ibis.literal(\"Municipal\"))\n",
    "            .cast({\"geometry\": \"geometry\"})\n",
    "            \n",
    "            )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1e81807-8ce3-44bf-9a1c-8563fa33817c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_county.union(df_local)\n",
    "df.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d3bee26-7ca8-490c-be5b-fc69a6c3db2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import subprocess\n",
    "import os\n",
    "from huggingface_hub import HfApi, login\n",
    "import streamlit as st\n",
    "\n",
    "login(st.secrets[\"HF_TOKEN\"])\n",
    "# api = HfApi(add_to_git_credential=False)\n",
    "api = HfApi()\n",
    "\n",
    "def hf_upload(file, repo_id):\n",
    "    info = api.upload_file(\n",
    "            path_or_fileobj=file,\n",
    "            path_in_repo=file,\n",
    "            repo_id=repo_id,\n",
    "            repo_type=\"dataset\",\n",
    "        )\n",
    "def generate_pmtiles(input_file, output_file, max_zoom=12):\n",
    "    # Ensure Tippecanoe is installed\n",
    "    if subprocess.call([\"which\", \"tippecanoe\"], stdout=subprocess.DEVNULL) != 0:\n",
    "        raise RuntimeError(\"Tippecanoe is not installed or not in PATH\")\n",
    "\n",
    "    # Construct the Tippecanoe command\n",
    "    command = [\n",
    "        \"tippecanoe\",\n",
    "        \"-o\", output_file,\n",
    "        \"-z\", str(max_zoom),\n",
    "        \"--drop-densest-as-needed\",\n",
    "        \"--extend-zooms-if-still-dropping\",\n",
    "        \"--force\",\n",
    "        input_file\n",
    "    ]\n",
    "\n",
    "    # Run Tippecanoe\n",
    "    try:\n",
    "        subprocess.run(command, check=True)\n",
    "        print(f\"Successfully generated PMTiles file: {output_file}\")\n",
    "    except subprocess.CalledProcessError as e:\n",
    "        print(f\"Error running Tippecanoe: {e}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac91a627-70a8-4e60-b3ef-66e9c1e02762",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.execute().to_file(\"vote.geojson\")\n",
    "generate_pmtiles(\"vote.geojson\", \"vote.pmtiles\")\n",
    "hf_upload(\"vote.pmtiles\", \"boettiger-lab/landvote\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa397626-6e94-4ab9-a3bb-2bcbd14e8d40",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import leafmap.maplibregl as leafmap\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "\n",
    "url = \"https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/vote.pmtiles\"\n",
    "\n",
    "#gdf = df.filter(_.year==1988).execute()\n",
    "#gdf.to_file(\"vote.geojson\")\n",
    "\n",
    "outcome = [\n",
    "      'match',\n",
    "      ['get', 'Status'], \n",
    "      \"Pass\", '#2E865F',\n",
    "      \"Fail\", '#FF3300', \n",
    "      '#ccc'\n",
    "    ]\n",
    "paint = {\"fill-extrusion-color\": outcome, \n",
    "         \"fill-extrusion-opacity\": 0.7,\n",
    "         \"fill-extrusion-height\": [\"*\", [\"get\", \"log_amount\"], 5000],\n",
    "        }\n",
    "style = {\n",
    "    \"layers\": [\n",
    "        {\n",
    "            \"id\": \"votes\",\n",
    "            \"source\": \"vote\",\n",
    "            \"source-layer\": \"vote\",\n",
    "            \"type\": \"fill-extrusion\",\n",
    "            \"filter\": [\n",
    "                \"==\",\n",
    "                [\"get\", \"year\"],\n",
    "                1988,\n",
    "            ],  # only show buildings with height info\n",
    "            \"paint\": paint\n",
    "        },\n",
    "    ],\n",
    "}\n",
    "\n",
    "m.add_pmtiles(\n",
    "    url,\n",
    "    style=style,\n",
    "    visible=True,\n",
    "    opacity=1.0,\n",
    "    tooltip=True,\n",
    "    fit_bounds=False,\n",
    ")\n",
    "#m.add_layer_control()\n",
    "m\n",
    "\n",
    "\n",
    "\n",
    "#m.add_geojson(\"vote.geojson\", \"fill-extrusion\", paint = paint)\n",
    "#m.add_gdf(gdf, \"fill-extrusion\", paint = paint)\n",
    "#m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e521f00-1b04-4016-9a6a-71a12e846dd3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}