static-maps.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "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": 27,
   "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": 28,
   "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": 29,
   "id": "a1e81807-8ce3-44bf-9a1c-8563fa33817c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_county.union(df_local)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e0402bc4-9b1b-4d31-8789-1970c34bcfa8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "47d5f61e2fdc46328e233418e9b48d95",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "gdf = df.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2d0d5b70-2739-48e8-9ac6-789cb2f9f648",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['State',\n",
       " 'Jurisdiction Name',\n",
       " 'Jurisdiction Type',\n",
       " 'Date',\n",
       " 'Description',\n",
       " 'Finance Mechanism',\n",
       " '\"Other\" Comment',\n",
       " 'Purpose',\n",
       " 'Total Funds at Stake',\n",
       " 'Conservation Funds at Stake',\n",
       " 'Total Funds Approved',\n",
       " 'Conservation Funds Approved',\n",
       " 'Pass?',\n",
       " 'Status',\n",
       " '% Yes',\n",
       " '% No',\n",
       " 'Notes',\n",
       " 'Voted Acq. Measure',\n",
       " 'column18']"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "votes.drop(\"Total Funds )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5d3bee26-7ca8-490c-be5b-fc69a6c3db2a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The token has not been saved to the git credentials helper. Pass `add_to_git_credential=True` in this function directly or `--add-to-git-credential` if using via `huggingface-cli` if you want to set the git credential as well.\n",
      "Token is valid (permission: write).\n",
      "Your token has been saved to /home/jovyan/.cache/huggingface/token\n",
      "Login successful\n"
     ]
    }
   ],
   "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": 25,
   "id": "a3bde171-e7a8-4a5d-97ea-bfffdf26918b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">DatabaseTable: ibis_read_geo_pamy33s53vaepploan6xzko2mu\n",
       "  key          string\n",
       "  Status       string\n",
       "  yes          string\n",
       "  year         int32\n",
       "  amount       string\n",
       "  log_amount   float64\n",
       "  jurisdiction string\n",
       "  geom         geospatial:geometry\n",
       "</pre>\n"
      ],
      "text/plain": [
       "DatabaseTable: ibis_read_geo_pamy33s53vaepploan6xzko2mu\n",
       "  key          string\n",
       "  Status       string\n",
       "  yes          string\n",
       "  year         int32\n",
       "  amount       string\n",
       "  log_amount   float64\n",
       "  jurisdiction string\n",
       "  geom         geospatial:geometry"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import leafmap.maplibregl as leafmap\n",
    "import geopandas\n",
    "url = \"https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/vote.geojson\"\n",
    "\n",
    "conn.read_geo(url).filter\n",
    "#gpf = geopandas.read_file(url, engine=\"pyogrio\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "58094159-4efc-4b56-a1d4-dac27be86924",
   "metadata": {},
   "outputs": [],
   "source": [
    "gpf.to_file(\"vote.geojson\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "221db74d-961c-4cac-a443-02b3d562b531",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "For layer 0, using name \"vote\"\n",
      "vote.geojson:496: null geometry (additional not reported): in JSON object {\"type\":\"Feature\",\"properties\":{\"key\":null,\"Status\":\"Fail\",\"yes\":\"43.668463401297%\",\"year\":1990,\"amount\":null,\"log_amount\":null,\"jurisdiction\":\"County\"},\"geometry\":null}\n",
      "2195 features, 10372266 bytes of geometry, 95306 bytes of string pool\n",
      "  99.9%  12/1136/1649  \n",
      "  100.0%  12/220/1795  \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully generated PMTiles file: vote.pmtiles\n"
     ]
    }
   ],
   "source": [
    "generate_pmtiles(\"vote.geojson\", \"vote.pmtiles\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "23deeb8c-3ef9-4279-bd5f-7b72494ee567",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Projected CRS: EPSG:3857>\n",
       "Name: WGS 84 / Pseudo-Mercator\n",
       "Axis Info [cartesian]:\n",
       "- X[east]: Easting (metre)\n",
       "- Y[north]: Northing (metre)\n",
       "Area of Use:\n",
       "- name: World between 85.06°S and 85.06°N.\n",
       "- bounds: (-180.0, -85.06, 180.0, 85.06)\n",
       "Coordinate Operation:\n",
       "- name: Popular Visualisation Pseudo-Mercator\n",
       "- method: Popular Visualisation Pseudo Mercator\n",
       "Datum: World Geodetic System 1984 ensemble\n",
       "- Ellipsoid: WGS 84\n",
       "- Prime Meridian: Greenwich"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf = geopandas.read_file(\"vote.pmtiles\", engine=\"pyogrio\")\n",
    "gdf.crs"
   ]
  },
  {
   "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.11.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}