{ "cells": [ { "cell_type": "code", "execution_count": 13, "id": "2a04e077-1297-406a-a257-fb0f98566036", "metadata": {}, "outputs": [], "source": [ "import os\n", "import ibis\n", "from ibis import _\n", "import ibis.selectors as s\n", "from cng.utils import *\n", "from cng.h3 import *\n", "from minio import Minio\n", "import streamlit \n", "from datetime import timedelta\n", "import re\n", "duckdb_install_h3()\n", "\n", "# con = ibis.duckdb.connect(\"duck.db\",extensions = [\"spatial\", \"h3\"])\n", "con = ibis.duckdb.connect(extensions = [\"spatial\", \"h3\"])\n", "set_secrets(con)\n", "\n", "# Get signed URLs to access license-controlled layers\n", "key = st.secrets[\"MINIO_KEY\"]\n", "secret = st.secrets[\"MINIO_SECRET\"]\n", "client = Minio(\"minio.carlboettiger.info\", key, secret)\n", "\n", "mobi_z8 = con.read_parquet(\"https://minio.carlboettiger.info/public-mobi/hex/all-richness-h8.parquet\").select(\"h8\", \"Z\").rename(richness = \"Z\")\n", "svi_z8 = con.read_parquet(\"https://minio.carlboettiger.info/public-social-vulnerability/2022/SVI2022_US_tract_h3_z8.parquet\").select(\"h8\", \"svi\").filter(_.svi > 0)\n", "carbon_z8 = con.read_parquet(\"https://minio.carlboettiger.info/public-carbon/hex/us-tracts-vuln-total-carbon-2018-h8.parquet\").select('carbon','h8')\n", "county_bounds = con.read_parquet(\"https://minio.carlboettiger.info/public-census/2024/county/2024_us_county.parquet\")\n", "tpl_z8 = con.read_parquet(\"s3://shared-tpl/conservation_almanac/z8/tpl_h3_z8.parquet\")\n", "landvote_z8 = con.read_parquet(\"s3://shared-tpl/landvote/z8/landvote_h3_z8.parquet\")" ] }, { "cell_type": "code", "execution_count": 15, "id": "919e66ee-5e41-4e86-a9b9-867dc6bc3d87", "metadata": {}, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "31265c6efc164d5082af3ed5f855ef2e", "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": [ "current_tables = con.list_tables()\n", "\n", "if \"conservation_almanac\" not in set(current_tables):\n", " con.create_table(\"conservation_almanac\", tpl_z8)\n", "\n", "if \"landvote\" not in set(current_tables):\n", " con.create_table(\"landvote\", landvote_z8)\n", "\n", "if \"carbon\" not in set(current_tables):\n", " con.create_table(\"carbon\", carbon_z8)\n", "\n", "if \"mobi\" not in set(current_tables):\n", " con.create_table(\"mobi\", mobi_z8)\n", "\n", "if \"svi\" not in set(current_tables):\n", " con.create_table(\"svi\", svi_z8)\n", "\n", "conservation_almanac = con.table(\"conservation_almanac\")\n", "landvote = con.table(\"landvote\")\n", "carbon = con.table(\"carbon\")\n", "mobi = con.table(\"mobi\")\n", "svi = con.table(\"svi\")\n" ] }, { "cell_type": "code", "execution_count": 12, "id": "78a7680e-a2ba-4e4a-ab90-c703ebda74b6", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
fidmean_carbon
012347484.333333
121514440.160714
214997440.160714
329331430.705128
429333388.551515
529245366.432727
625881339.250000
724728339.000000
824727339.000000
929326333.180000
\n", "
" ], "text/plain": [ " fid mean_carbon\n", "0 12347 484.333333\n", "1 21514 440.160714\n", "2 14997 440.160714\n", "3 29331 430.705128\n", "4 29333 388.551515\n", "5 29245 366.432727\n", "6 25881 339.250000\n", "7 24728 339.000000\n", "8 24727 339.000000\n", "9 29326 333.180000" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "con.sql('''\n", "SELECT fid, AVG(carbon) as mean_carbon\n", " FROM conservation_almanac\n", " LEFT JOIN carbon\n", " USING (h8)\n", " GROUP BY fid\n", " ORDER BY mean_carbon DESC LIMIT 10;\n", " ''').execute()" ] }, { "cell_type": "code", "execution_count": null, "id": "93796006-8203-496d-8288-2c0156ea22bc", "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.12.10" } }, "nbformat": 4, "nbformat_minor": 5 }