{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e84709ab-1b47-49ee-8cbd-8aa69744b6c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c4382edb57b643e6907b0314c79387bd",
       "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"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b4ae3a5cd5f84c5b86fdcd767d330adf",
       "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"
    },
    {
     "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: tmp2.main.t2\n",
       "  gid                int64\n",
       "  cpad_ACCESS_TYP    string\n",
       "  cpad_PARK_NAME     string\n",
       "  cpad_MNG_AGENCY    string\n",
       "  cpad_MNG_AG_LEV    string\n",
       "  reGAP              int16\n",
       "  Easement           int16\n",
       "  TYPE               string\n",
       "  CA_County_Name     string\n",
       "  CA_Region_Name     string\n",
       "  TerrMar            string\n",
       "  CA_Ecoregion_Name  string\n",
       "  ACCESS_TYP         string\n",
       "  MNG_AGNCY          string\n",
       "  MNG_AG_LEV         string\n",
       "  UNIT_NAME          string\n",
       "  DefaultSelection   string\n",
       "  CA_Ecoregion_Acres float32\n",
       "  CA_Region_Acres    float32\n",
       "  CA_County_Acres    float32\n",
       "  Acres              float32\n",
       "  CA_Marine_Acres    float32\n",
       "  Release_Year       int16\n",
       "  mgmt_stack         string\n",
       "  geom               geospatial:geometry\n",
       "  SHAPE_bbox         xmin: float32\n",
       "  ymin: float32\n",
       "  xmax: float32\n",
       "  ymax: float32\n",
       "</pre>\n"
      ],
      "text/plain": [
       "DatabaseTable: tmp2.main.t2\n",
       "  gid                int64\n",
       "  cpad_ACCESS_TYP    string\n",
       "  cpad_PARK_NAME     string\n",
       "  cpad_MNG_AGENCY    string\n",
       "  cpad_MNG_AG_LEV    string\n",
       "  reGAP              int16\n",
       "  Easement           int16\n",
       "  TYPE               string\n",
       "  CA_County_Name     string\n",
       "  CA_Region_Name     string\n",
       "  TerrMar            string\n",
       "  CA_Ecoregion_Name  string\n",
       "  ACCESS_TYP         string\n",
       "  MNG_AGNCY          string\n",
       "  MNG_AG_LEV         string\n",
       "  UNIT_NAME          string\n",
       "  DefaultSelection   string\n",
       "  CA_Ecoregion_Acres float32\n",
       "  CA_Region_Acres    float32\n",
       "  CA_County_Acres    float32\n",
       "  Acres              float32\n",
       "  CA_Marine_Acres    float32\n",
       "  Release_Year       int16\n",
       "  mgmt_stack         string\n",
       "  geom               geospatial:geometry\n",
       "  SHAPE_bbox         xmin: float32\n",
       "  ymin: float32\n",
       "  xmax: float32\n",
       "  ymax: float32"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "conn = ibis.duckdb.connect(\"tmp2\", extensions=[\"spatial\"])\n",
    "\n",
    "tbl = (\n",
    "    conn.read_parquet(\"https://data.source.coop/cboettig/ca30x30/ca_areas.parquet\")\n",
    "    .cast({\"SHAPE\": \"geometry\"})\n",
    "    .rename(geom = \"SHAPE\", gid = \"OBJECTID\")\n",
    "   # .filter(_.UNIT_NAME == \"Angeles National Forest\")\n",
    "    .filter(_.reGAP < 3) \n",
    ")\n",
    "conn.create_table(\"t1\", tbl.filter(_.Release_Year == 2024), overwrite = True)\n",
    "conn.create_table(\"t2\", tbl.filter(_.Release_Year == 2023), overwrite = True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "303792ac-9b1d-41b2-a17b-5cf855d70633",
   "metadata": {},
   "outputs": [],
   "source": [
    "ca2024 = conn.table(\"t1\").execute()\n",
    "ca2023 = conn.table(\"t2\").execute()\n",
    "\n",
    "import leafmap.maplibregl as leafmap\n",
    "m = leafmap.Map()\n",
    "m.add_gdf(ca2024, name = \"2024\")\n",
    "m.add_gdf(ca2023, name =\"2023\")\n",
    "\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fdd2ed2-623f-479c-b0b7-7c723f3f6728",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "conn.disconnect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "121c3cad-680c-4f3a-9075-638711ea1634",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c69a096d24974e9ea8ad3d5b937b723a",
       "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"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 22min 10s, sys: 43 s, total: 22min 53s\n",
      "Wall time: 11min 47s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "## RUN this on a machine with a whole lot of RAM.  consider filtering federal/non-federal first.\n",
    "import duckdb\n",
    "db = duckdb.connect(\"tmp2\")\n",
    "db.install_extension(\"spatial\")\n",
    "db.load_extension(\"spatial\")\n",
    "\n",
    "db.sql('''\n",
    "CREATE OR REPLACE TABLE diff AS (\n",
    "with temp as \n",
    "(\n",
    "  select   b.gid, st_union_agg(a.geom) as geom\n",
    "  from     t1 b join t2 a on st_intersects(a.geom, b.geom)\n",
    "  group by b.gid\n",
    ") \n",
    "select st_difference(b.geom,coalesce(t.geom, 'GEOMETRYCOLLECTION EMPTY'::geometry)) as geom\n",
    "from t1 b left join temp t on b.gid = t.gid\n",
    ")\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cf3d3e5b-2ff1-4ef9-a147-01f15e970e49",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Cannot go straight to geoparquet due to M geometries!\n",
    "#db.table(\"diff\").to_parquet(\"diff.parquet\")\n",
    "\n",
    "## This doesn't work either: \n",
    "#db.sql('''CREATE OR REPLACE TABLE diff2024 AS SELECT *, st_force2d(geom) AS geom FROM diff''')\n",
    "\n",
    "## We could cast geom as blob...."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "308dc665-1323-4e9b-bc2d-69201c325c4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# F*ck it.  Let's do it all in RAM via geopandas, which drops M geoms due to a fortunate bug! \n",
    "conn = ibis.duckdb.connect(\"tmp2\", extensions=[\"spatial\"])\n",
    "gdf = conn.table(\"diff\").mutate(geom = _.geom.convert(\"epsg:3310\",\"epsg:4326\")).execute()\n",
    "gdf.to_parquet(\"ca2024_diffs.parquet\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "063a11d8-15d7-4b91-b67c-3ccae3edcc8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stash in our team S3 storage \n",
    "\n",
    "import streamlit as st\n",
    "from minio import Minio\n",
    "import os\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, secure=True)\n",
    "\n",
    "size = os.path.getsize(\"ca2024_diffs.parquet\")\n",
    "with open(\"ca2024_diffs.parquet\", \"rb\") as file_data:\n",
    "    client.put_object(\"public-biodiversity\", \"ca30x30/ca2024_diffs.parquet\", file_data, length = size)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "34425101-0592-42fd-9d62-22c9e7a6d6ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c5cc696c15374d519fb940e054a902f0",
       "version_major": 2,
       "version_minor": 1
      },
      "text/plain": [
       "Map(height='600px', map_options={'bearing': 0, 'center': (0, 20), 'pitch': 0, 'style': 'https://basemaps.carto…"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# can read from S3 and plot the whole thing.  Note gdf has no metadata.\n",
    "\n",
    "import leafmap.maplibregl as leafmap\n",
    "import ibis\n",
    "conn = ibis.duckdb.connect(extensions=[\"spatial\"])\n",
    "gdf = conn.read_parquet(\"https://minio.carlboettiger.info/public-biodiversity/ca30x30/ca2024_diffs.parquet\").execute()\n",
    "m = leafmap.Map()\n",
    "m.add_gdf(gdf)\n",
    "#m.to_html(\"ca2024.html\")\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "17af39a0-9a69-4bfa-9084-d7b26adf74fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = \"ca2024.html\"\n",
    "size = os.path.getsize(path)\n",
    "with open(path, \"rb\") as file_data:\n",
    "    client.put_object(\"public-biodiversity\", \"ca30x30/\"+path, file_data, length = size)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "775abaca-f21a-4cd8-ad7d-0310cc6b33b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"TO 'new2024.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES')\""
   ]
  }
 ],
 "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
}