{
"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": [
"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",
"\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": [
"\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
}