{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f7e6298c-d886-432a-a1b7-c3fee914c24f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# boilerplate setup\n",
    "import leafmap.maplibregl as leafmap\n",
    "import ibis\n",
    "from ibis import _\n",
    "\n",
    "conn = ibis.duckdb.connect()\n",
    "ca_parquet = \"https://data.source.coop/cboettig/ca30x30/ca_areas.parquet\"\n",
    "# or use local copy:\n",
    "ca_parquet = \"/home/rstudio/source.coop/cboettig/ca30x30/ca_areas.parquet\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd9c2561-334a-40d6-adf0-ce20a991a9a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualize the full data to show overlap:\n",
    "import leafmap.maplibregl as leafmap\n",
    "ca_pmtiles = \"https://data.source.coop/cboettig/ca30x30/ca_areas.pmtiles\"\n",
    "style = {\n",
    "    \"version\": 8,\n",
    "    \"sources\": {\n",
    "        \"ca\": {\n",
    "            \"type\": \"vector\",\n",
    "            \"url\": \"pmtiles://\" + ca_pmtiles,\n",
    "        }},\n",
    "    \"layers\": [{\n",
    "            \"id\": \"layer1\",\n",
    "            \"source\": \"ca\",\n",
    "            \"source-layer\": \"CA_Cons_Areas_parentlyr_Merge_ecofix\",\n",
    "            \"type\": \"fill\",\n",
    "            \"filter\": [\"<\",[\"get\", \"reGAP\"], 3,],\n",
    "            \"paint\": {\n",
    "                \"fill-color\": {\n",
    "            'property': 'Release_Year',\n",
    "            'type': 'categorical',\n",
    "            'stops': [[2023, \"#FF000080\"],  # note RGBA code includes alpha\n",
    "                      [2024, \"#0000FF80\"]]\n",
    "            },\n",
    "                \"fill-opacity\": 0.5 # ignored? \n",
    "            }\n",
    "        }]}\n",
    "\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "m.add_pmtiles(ca_pmtiles, style = style)\n",
    "m.to_html(\"ca30x30_gap12.html\") # save a copy\n",
    "m\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3ceed4c7-b7a2-436e-bca1-c57e7133cdf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make things faster.  Let's zoom in on a subset of the data. \n",
    "\n",
    "from ibis.interactive import *\n",
    "ca_parquet = \"/home/rstudio/source.coop/cboettig/ca30x30/ca_areas.parquet\"\n",
    "conn = ibis.duckdb.connect()\n",
    "\n",
    "# Ugly code for a cookie cutter zoom\n",
    "# Turns out we could have just used filter(_.UNIT_NAME == \"Angeles National Forest\")\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import box\n",
    "min_lon, min_lat, max_lon, max_lat = -118.4, 34., -117.545715, 34.495239\n",
    "bbox = box(min_lon, min_lat, max_lon, max_lat)\n",
    "gdf = gpd.GeoDataFrame({'geometry': [bbox]}, crs=\"EPSG:4326\")\n",
    "cookie_cutter = ibis.literal(gdf.geometry.iloc[0])\n",
    "\n",
    "angeles_forest = (\n",
    "    conn.read_parquet(ca_parquet)\n",
    "    .cast({\"SHAPE\": \"geometry\"})\n",
    "    .rename(geom = \"SHAPE\")\n",
    " #   .mutate(geom = _.geom.convert(\"epsg:3310\",\"epsg:4326\"))\n",
    " #   .filter( _.geom.intersects(cookie_cutter))\n",
    " #   .filter(_.UNIT_NAME == \"Angeles National Forest\") \n",
    "    .select(\"reGAP\", \"UNIT_NAME\", \"MNG_AGNCY\", \"Release_Year\", \"OBJECTID\", \"geom\")\n",
    ")\n",
    "# split 2023 & 2024\n",
    "allgap_2023 = angeles_forest.filter(_.Release_Year == 2023)\n",
    "allgap_2024 = angeles_forest.filter(_.Release_Year == 2024)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "a0cb34b1-8d70-49bf-80c6-244ecc8ddf84",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "buffer = -0.0003 \n",
    "\n",
    "tbl = (\n",
    "    conn.read_parquet(\"https://data.source.coop/cboettig/ca30x30/ca_areas.parquet\")\n",
    "    .cast({\"SHAPE\": \"geometry\"})\n",
    "    .rename(geom = \"SHAPE\")\n",
    "  #  .filter(_.UNIT_NAME == \"Angeles National Forest\")\n",
    "    .filter(_.reGAP < 3) \n",
    "    .mutate(geom = _.geom.convert(\"epsg:3310\",\"epsg:4326\"))\n",
    ")\n",
    "tbl_2023 = tbl.filter(_.Release_Year == 2023).mutate(geom=_.geom.buffer(buffer))\n",
    "tbl_2024 = tbl.filter(_.Release_Year == 2024)\n",
    "intersects = tbl_2024.anti_join(tbl_2023, _.geom.intersects(tbl_2023.geom))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "adab4c4f-d2cb-48b2-b32e-5c7beb5fdbc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a5694cb8358f4591a512a2304462bd87",
       "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": [
    "new = intersects.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "03b46dbe-bda1-45db-8b7d-7caa1e2e3a76",
   "metadata": {},
   "outputs": [],
   "source": [
    "new.to_parquet(\"new2024.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3ed21429-a75e-4b52-b6eb-d41c8f051883",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7c5bd159278847f481827b444ffc32b8",
       "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": "c9df74d0099c4ba7929e45ae3fce1eb5",
       "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": [
    "import leafmap.maplibregl as leafmap\n",
    "\n",
    "release_year = {'property': 'Release_Year', 'type': 'categorical','stops': [[2023, \"#FF000080\"], [2024, \"#0000FF80\"]]},\n",
    "gap = {\n",
    "        'property': 'reGAP',\n",
    "        'type': 'categorical',\n",
    "        'stops': [\n",
    "            [1, \"#26633d\"],\n",
    "            [2, \"#879647\"],\n",
    "            [3, \"#BBBBBB\"],\n",
    "            [4, \"#F8F8F8\"]]\n",
    "        }\n",
    "paint = {\"fill-color\": gap, \"fill-opacity\": 0.5}\n",
    "\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "m.add_gdf(intersects.execute(),layer_type=\"fill\", name = \"intes\", paint=paint)\n",
    "m.add_layer_control()\n",
    "m.to_html(\"new_in_2024.html\")\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebbb2650-4442-4e54-8467-5e681d6fab9e",
   "metadata": {},
   "source": [
    "Using difference (overlap) instead:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "1e538620-19f5-42eb-8233-e280b5c5b920",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "21dbc7c4-20e2-4422-ac21-cd50badcae58",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "34dc23600db64d4899b43e42348907b4",
       "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": "1c7b2f7a0bf548ce933d40d7bb0e7f43",
       "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"
    },
    {
     "ename": "InvalidInputException",
     "evalue": "Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result\nError: Catalog Error: Scalar Function with name st_make_valid does not exist!\nDid you mean \"ST_MakeValid\"?\nLINE 2: ...ACE TABLE t1 AS SELECT OBJECTID AS gid, st_make_valid(geom) AS geom, FROM st_r...\n                                                  ^",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInvalidInputException\u001b[0m                     Traceback (most recent call last)",
      "File \u001b[0;32m<timed exec>:13\u001b[0m\n",
      "\u001b[0;31mInvalidInputException\u001b[0m: Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result\nError: Catalog Error: Scalar Function with name st_make_valid does not exist!\nDid you mean \"ST_MakeValid\"?\nLINE 2: ...ACE TABLE t1 AS SELECT OBJECTID AS gid, st_make_valid(geom) AS geom, FROM st_r...\n                                                  ^"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "import duckdb\n",
    "db = duckdb.connect()\n",
    "db.install_extension(\"spatial\")\n",
    "db.load_extension(\"spatial\")\n",
    "\n",
    "\n",
    "tbl_2023 = tbl.filter(_.Release_Year == 2023) # no buffer\n",
    "tbl_2024 = tbl.filter(_.Release_Year == 2024)\n",
    "tbl_2023.execute().to_file(\"la_2023.geojson\")\n",
    "tbl_2024.execute().to_file(\"la_2024.geojson\")\n",
    "\n",
    "\n",
    "db.sql('''\n",
    "CREATE OR REPLACE TABLE t1 AS SELECT OBJECTID AS gid, st_make_valid(geom) AS geom, FROM st_read(\"la_2024.geojson\");\n",
    "CREATE OR REPLACE TABLE t2 AS SELECT OBJECTID AS gid, st_make_valid(geom) AS geom, FROM st_read(\"la_2023.geojson\");\n",
    "''')\n",
    "db.sql('''\n",
    "COPY (\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",
    ") TO 'new2024.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES')\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d869c220-a4c2-46b1-9e02-d5cce5a2bffa",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "con = ibis.duckdb.connect()\n",
    "diff = con.read_geo(\"new2024.geojson\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "598d096a-5cae-4ddc-938c-908eba334b91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9eae57f133a44d0799e4622fdb318e30",
       "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": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import leafmap.maplibregl as leafmap\n",
    "\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "m.add_gdf(diff.execute(),layer_type=\"fill\", name = \"diff\", paint = {\"fill-opacity\": 0.5})\n",
    "m.add_layer_control()\n",
    "m\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "72e833e6-0d7b-4508-a42d-cc7fc7df7c46",
   "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\">r0 := DatabaseTable: ibis_read_parquet_3fotibldaretnd5wjeicmg4oli\n",
       "  OBJECTID           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",
       "  SHAPE              binary\n",
       "  SHAPE_bbox         xmin: float32\n",
       "  ymin: float32\n",
       "  xmax: float32\n",
       "  ymax: float32\n",
       "\n",
       "r1 := DatabaseTable: ibis_read_parquet_t3lw66szfrfcrj34bcr6dbmi64\n",
       "  OBJECTID           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               binary\n",
       "  SHAPE_bbox         xmax: float64\n",
       "  xmin: float64\n",
       "  ymax: float64\n",
       "  ymin: float64\n",
       "\n",
       "r2 := Project[r1]\n",
       "  OBJECTID: r1.OBJECTID\n",
       "\n",
       "r3 := Project[r0]\n",
       "  OBJECTID:           r0.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r0.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r0.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r0.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r0.cpad_MNG_AG_LEV\n",
       "  reGAP:              r0.reGAP\n",
       "  Easement:           r0.Easement\n",
       "  TYPE:               r0.TYPE\n",
       "  CA_County_Name:     r0.CA_County_Name\n",
       "  CA_Region_Name:     r0.CA_Region_Name\n",
       "  TerrMar:            r0.TerrMar\n",
       "  CA_Ecoregion_Name:  r0.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r0.ACCESS_TYP\n",
       "  MNG_AGNCY:          r0.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r0.MNG_AG_LEV\n",
       "  UNIT_NAME:          r0.UNIT_NAME\n",
       "  DefaultSelection:   r0.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r0.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r0.CA_Region_Acres\n",
       "  CA_County_Acres:    r0.CA_County_Acres\n",
       "  Acres:              r0.Acres\n",
       "  CA_Marine_Acres:    r0.CA_Marine_Acres\n",
       "  Release_Year:       r0.Release_Year\n",
       "  mgmt_stack:         r0.mgmt_stack\n",
       "  SHAPE:              Cast(r0.SHAPE, to=geospatial:geometry)\n",
       "  SHAPE_bbox:         r0.SHAPE_bbox\n",
       "\n",
       "r4 := Project[r2]\n",
       "  OBJECTID:    r2.OBJECTID\n",
       "  established: 2024\n",
       "\n",
       "r5 := Project[r3]\n",
       "  OBJECTID:           r3.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r3.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r3.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r3.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r3.cpad_MNG_AG_LEV\n",
       "  reGAP:              r3.reGAP\n",
       "  Easement:           r3.Easement\n",
       "  TYPE:               r3.TYPE\n",
       "  CA_County_Name:     r3.CA_County_Name\n",
       "  CA_Region_Name:     r3.CA_Region_Name\n",
       "  TerrMar:            r3.TerrMar\n",
       "  CA_Ecoregion_Name:  r3.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r3.ACCESS_TYP\n",
       "  MNG_AGNCY:          r3.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r3.MNG_AG_LEV\n",
       "  UNIT_NAME:          r3.UNIT_NAME\n",
       "  DefaultSelection:   r3.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r3.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r3.CA_Region_Acres\n",
       "  CA_County_Acres:    r3.CA_County_Acres\n",
       "  Acres:              r3.Acres\n",
       "  CA_Marine_Acres:    r3.CA_Marine_Acres\n",
       "  Release_Year:       r3.Release_Year\n",
       "  mgmt_stack:         r3.mgmt_stack\n",
       "  SHAPE:              r3.SHAPE\n",
       "  SHAPE_bbox:         r3.SHAPE_bbox\n",
       "  area:               GeoArea(r3.SHAPE)\n",
       "\n",
       "r6 := JoinChain[r5]\n",
       "  JoinLink[left, r4]\n",
       "    r5.OBJECTID == r4.OBJECTID\n",
       "  values:\n",
       "    OBJECTID:           r5.OBJECTID\n",
       "    cpad_ACCESS_TYP:    r5.cpad_ACCESS_TYP\n",
       "    cpad_PARK_NAME:     r5.cpad_PARK_NAME\n",
       "    cpad_MNG_AGENCY:    r5.cpad_MNG_AGENCY\n",
       "    cpad_MNG_AG_LEV:    r5.cpad_MNG_AG_LEV\n",
       "    reGAP:              r5.reGAP\n",
       "    Easement:           r5.Easement\n",
       "    TYPE:               r5.TYPE\n",
       "    CA_County_Name:     r5.CA_County_Name\n",
       "    CA_Region_Name:     r5.CA_Region_Name\n",
       "    TerrMar:            r5.TerrMar\n",
       "    CA_Ecoregion_Name:  r5.CA_Ecoregion_Name\n",
       "    ACCESS_TYP:         r5.ACCESS_TYP\n",
       "    MNG_AGNCY:          r5.MNG_AGNCY\n",
       "    MNG_AG_LEV:         r5.MNG_AG_LEV\n",
       "    UNIT_NAME:          r5.UNIT_NAME\n",
       "    DefaultSelection:   r5.DefaultSelection\n",
       "    CA_Ecoregion_Acres: r5.CA_Ecoregion_Acres\n",
       "    CA_Region_Acres:    r5.CA_Region_Acres\n",
       "    CA_County_Acres:    r5.CA_County_Acres\n",
       "    Acres:              r5.Acres\n",
       "    CA_Marine_Acres:    r5.CA_Marine_Acres\n",
       "    Release_Year:       r5.Release_Year\n",
       "    mgmt_stack:         r5.mgmt_stack\n",
       "    SHAPE:              r5.SHAPE\n",
       "    SHAPE_bbox:         r5.SHAPE_bbox\n",
       "    area:               r5.area\n",
       "    OBJECTID_right:     r4.OBJECTID\n",
       "    established:        r4.established\n",
       "\n",
       "r7 := Project[r6]\n",
       "  OBJECTID:           r6.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r6.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r6.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r6.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r6.cpad_MNG_AG_LEV\n",
       "  reGAP:              r6.reGAP\n",
       "  Easement:           r6.Easement\n",
       "  TYPE:               r6.TYPE\n",
       "  CA_County_Name:     r6.CA_County_Name\n",
       "  CA_Region_Name:     r6.CA_Region_Name\n",
       "  TerrMar:            r6.TerrMar\n",
       "  CA_Ecoregion_Name:  r6.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r6.ACCESS_TYP\n",
       "  MNG_AGNCY:          r6.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r6.MNG_AG_LEV\n",
       "  UNIT_NAME:          r6.UNIT_NAME\n",
       "  DefaultSelection:   r6.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r6.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r6.CA_Region_Acres\n",
       "  CA_County_Acres:    r6.CA_County_Acres\n",
       "  Acres:              r6.Acres\n",
       "  CA_Marine_Acres:    r6.CA_Marine_Acres\n",
       "  Release_Year:       r6.Release_Year\n",
       "  mgmt_stack:         r6.mgmt_stack\n",
       "  SHAPE:              r6.SHAPE\n",
       "  SHAPE_bbox:         r6.SHAPE_bbox\n",
       "  area:               r6.area\n",
       "  OBJECTID_right:     r6.OBJECTID_right\n",
       "  established:        Coalesce([r6.established, 2023])\n",
       "\n",
       "Project[r7]\n",
       "  OBJECTID:           r7.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r7.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r7.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r7.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r7.cpad_MNG_AG_LEV\n",
       "  reGAP:              r7.reGAP\n",
       "  Easement:           r7.Easement\n",
       "  TYPE:               r7.TYPE\n",
       "  CA_County_Name:     r7.CA_County_Name\n",
       "  CA_Region_Name:     r7.CA_Region_Name\n",
       "  TerrMar:            r7.TerrMar\n",
       "  CA_Ecoregion_Name:  r7.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r7.ACCESS_TYP\n",
       "  MNG_AGNCY:          r7.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r7.MNG_AG_LEV\n",
       "  UNIT_NAME:          r7.UNIT_NAME\n",
       "  DefaultSelection:   r7.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r7.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r7.CA_Region_Acres\n",
       "  CA_County_Acres:    r7.CA_County_Acres\n",
       "  Acres:              r7.Acres\n",
       "  CA_Marine_Acres:    r7.CA_Marine_Acres\n",
       "  Release_Year:       r7.Release_Year\n",
       "  mgmt_stack:         r7.mgmt_stack\n",
       "  SHAPE:              GeoConvert(r7.SHAPE, source='epsg:3310', target='epsg:4326')\n",
       "  SHAPE_bbox:         r7.SHAPE_bbox\n",
       "  area:               r7.area\n",
       "  OBJECTID_right:     r7.OBJECTID_right\n",
       "  established:        r7.established\n",
       "</pre>\n"
      ],
      "text/plain": [
       "r0 := DatabaseTable: ibis_read_parquet_3fotibldaretnd5wjeicmg4oli\n",
       "  OBJECTID           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",
       "  SHAPE              binary\n",
       "  SHAPE_bbox         xmin: float32\n",
       "  ymin: float32\n",
       "  xmax: float32\n",
       "  ymax: float32\n",
       "\n",
       "r1 := DatabaseTable: ibis_read_parquet_t3lw66szfrfcrj34bcr6dbmi64\n",
       "  OBJECTID           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               binary\n",
       "  SHAPE_bbox         xmax: float64\n",
       "  xmin: float64\n",
       "  ymax: float64\n",
       "  ymin: float64\n",
       "\n",
       "r2 := Project[r1]\n",
       "  OBJECTID: r1.OBJECTID\n",
       "\n",
       "r3 := Project[r0]\n",
       "  OBJECTID:           r0.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r0.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r0.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r0.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r0.cpad_MNG_AG_LEV\n",
       "  reGAP:              r0.reGAP\n",
       "  Easement:           r0.Easement\n",
       "  TYPE:               r0.TYPE\n",
       "  CA_County_Name:     r0.CA_County_Name\n",
       "  CA_Region_Name:     r0.CA_Region_Name\n",
       "  TerrMar:            r0.TerrMar\n",
       "  CA_Ecoregion_Name:  r0.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r0.ACCESS_TYP\n",
       "  MNG_AGNCY:          r0.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r0.MNG_AG_LEV\n",
       "  UNIT_NAME:          r0.UNIT_NAME\n",
       "  DefaultSelection:   r0.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r0.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r0.CA_Region_Acres\n",
       "  CA_County_Acres:    r0.CA_County_Acres\n",
       "  Acres:              r0.Acres\n",
       "  CA_Marine_Acres:    r0.CA_Marine_Acres\n",
       "  Release_Year:       r0.Release_Year\n",
       "  mgmt_stack:         r0.mgmt_stack\n",
       "  SHAPE:              Cast(r0.SHAPE, to=geospatial:geometry)\n",
       "  SHAPE_bbox:         r0.SHAPE_bbox\n",
       "\n",
       "r4 := Project[r2]\n",
       "  OBJECTID:    r2.OBJECTID\n",
       "  established: 2024\n",
       "\n",
       "r5 := Project[r3]\n",
       "  OBJECTID:           r3.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r3.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r3.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r3.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r3.cpad_MNG_AG_LEV\n",
       "  reGAP:              r3.reGAP\n",
       "  Easement:           r3.Easement\n",
       "  TYPE:               r3.TYPE\n",
       "  CA_County_Name:     r3.CA_County_Name\n",
       "  CA_Region_Name:     r3.CA_Region_Name\n",
       "  TerrMar:            r3.TerrMar\n",
       "  CA_Ecoregion_Name:  r3.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r3.ACCESS_TYP\n",
       "  MNG_AGNCY:          r3.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r3.MNG_AG_LEV\n",
       "  UNIT_NAME:          r3.UNIT_NAME\n",
       "  DefaultSelection:   r3.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r3.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r3.CA_Region_Acres\n",
       "  CA_County_Acres:    r3.CA_County_Acres\n",
       "  Acres:              r3.Acres\n",
       "  CA_Marine_Acres:    r3.CA_Marine_Acres\n",
       "  Release_Year:       r3.Release_Year\n",
       "  mgmt_stack:         r3.mgmt_stack\n",
       "  SHAPE:              r3.SHAPE\n",
       "  SHAPE_bbox:         r3.SHAPE_bbox\n",
       "  area:               GeoArea(r3.SHAPE)\n",
       "\n",
       "r6 := JoinChain[r5]\n",
       "  JoinLink[left, r4]\n",
       "    r5.OBJECTID == r4.OBJECTID\n",
       "  values:\n",
       "    OBJECTID:           r5.OBJECTID\n",
       "    cpad_ACCESS_TYP:    r5.cpad_ACCESS_TYP\n",
       "    cpad_PARK_NAME:     r5.cpad_PARK_NAME\n",
       "    cpad_MNG_AGENCY:    r5.cpad_MNG_AGENCY\n",
       "    cpad_MNG_AG_LEV:    r5.cpad_MNG_AG_LEV\n",
       "    reGAP:              r5.reGAP\n",
       "    Easement:           r5.Easement\n",
       "    TYPE:               r5.TYPE\n",
       "    CA_County_Name:     r5.CA_County_Name\n",
       "    CA_Region_Name:     r5.CA_Region_Name\n",
       "    TerrMar:            r5.TerrMar\n",
       "    CA_Ecoregion_Name:  r5.CA_Ecoregion_Name\n",
       "    ACCESS_TYP:         r5.ACCESS_TYP\n",
       "    MNG_AGNCY:          r5.MNG_AGNCY\n",
       "    MNG_AG_LEV:         r5.MNG_AG_LEV\n",
       "    UNIT_NAME:          r5.UNIT_NAME\n",
       "    DefaultSelection:   r5.DefaultSelection\n",
       "    CA_Ecoregion_Acres: r5.CA_Ecoregion_Acres\n",
       "    CA_Region_Acres:    r5.CA_Region_Acres\n",
       "    CA_County_Acres:    r5.CA_County_Acres\n",
       "    Acres:              r5.Acres\n",
       "    CA_Marine_Acres:    r5.CA_Marine_Acres\n",
       "    Release_Year:       r5.Release_Year\n",
       "    mgmt_stack:         r5.mgmt_stack\n",
       "    SHAPE:              r5.SHAPE\n",
       "    SHAPE_bbox:         r5.SHAPE_bbox\n",
       "    area:               r5.area\n",
       "    OBJECTID_right:     r4.OBJECTID\n",
       "    established:        r4.established\n",
       "\n",
       "r7 := Project[r6]\n",
       "  OBJECTID:           r6.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r6.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r6.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r6.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r6.cpad_MNG_AG_LEV\n",
       "  reGAP:              r6.reGAP\n",
       "  Easement:           r6.Easement\n",
       "  TYPE:               r6.TYPE\n",
       "  CA_County_Name:     r6.CA_County_Name\n",
       "  CA_Region_Name:     r6.CA_Region_Name\n",
       "  TerrMar:            r6.TerrMar\n",
       "  CA_Ecoregion_Name:  r6.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r6.ACCESS_TYP\n",
       "  MNG_AGNCY:          r6.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r6.MNG_AG_LEV\n",
       "  UNIT_NAME:          r6.UNIT_NAME\n",
       "  DefaultSelection:   r6.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r6.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r6.CA_Region_Acres\n",
       "  CA_County_Acres:    r6.CA_County_Acres\n",
       "  Acres:              r6.Acres\n",
       "  CA_Marine_Acres:    r6.CA_Marine_Acres\n",
       "  Release_Year:       r6.Release_Year\n",
       "  mgmt_stack:         r6.mgmt_stack\n",
       "  SHAPE:              r6.SHAPE\n",
       "  SHAPE_bbox:         r6.SHAPE_bbox\n",
       "  area:               r6.area\n",
       "  OBJECTID_right:     r6.OBJECTID_right\n",
       "  established:        Coalesce([r6.established, 2023])\n",
       "\n",
       "Project[r7]\n",
       "  OBJECTID:           r7.OBJECTID\n",
       "  cpad_ACCESS_TYP:    r7.cpad_ACCESS_TYP\n",
       "  cpad_PARK_NAME:     r7.cpad_PARK_NAME\n",
       "  cpad_MNG_AGENCY:    r7.cpad_MNG_AGENCY\n",
       "  cpad_MNG_AG_LEV:    r7.cpad_MNG_AG_LEV\n",
       "  reGAP:              r7.reGAP\n",
       "  Easement:           r7.Easement\n",
       "  TYPE:               r7.TYPE\n",
       "  CA_County_Name:     r7.CA_County_Name\n",
       "  CA_Region_Name:     r7.CA_Region_Name\n",
       "  TerrMar:            r7.TerrMar\n",
       "  CA_Ecoregion_Name:  r7.CA_Ecoregion_Name\n",
       "  ACCESS_TYP:         r7.ACCESS_TYP\n",
       "  MNG_AGNCY:          r7.MNG_AGNCY\n",
       "  MNG_AG_LEV:         r7.MNG_AG_LEV\n",
       "  UNIT_NAME:          r7.UNIT_NAME\n",
       "  DefaultSelection:   r7.DefaultSelection\n",
       "  CA_Ecoregion_Acres: r7.CA_Ecoregion_Acres\n",
       "  CA_Region_Acres:    r7.CA_Region_Acres\n",
       "  CA_County_Acres:    r7.CA_County_Acres\n",
       "  Acres:              r7.Acres\n",
       "  CA_Marine_Acres:    r7.CA_Marine_Acres\n",
       "  Release_Year:       r7.Release_Year\n",
       "  mgmt_stack:         r7.mgmt_stack\n",
       "  SHAPE:              GeoConvert(r7.SHAPE, source='epsg:3310', target='epsg:4326')\n",
       "  SHAPE_bbox:         r7.SHAPE_bbox\n",
       "  area:               r7.area\n",
       "  OBJECTID_right:     r7.OBJECTID_right\n",
       "  established:        r7.established"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ca"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7c9344b1-bbde-4c5a-b2cc-6e4a32bfb8cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import leafmap.maplibregl as leafmap\n",
    "import ibis\n",
    "from ibis import _\n",
    "ca_parquet = \"ca_areas.parquet\"\n",
    "\n",
    "con = ibis.duckdb.connect()\n",
    "new2024 = (con\n",
    "      .read_parquet(\"new2024.parquet\")\n",
    "      .select(\"OBJECTID\")\n",
    "      .mutate(established = 2024)\n",
    "     )\n",
    "\n",
    "ca = (con\n",
    "      .read_parquet(ca_parquet)\n",
    "      .cast({\"SHAPE\": \"geometry\"})\n",
    "      .mutate(area = _.SHAPE.area())\n",
    "      .filter(_.reGAP < 3)\n",
    "      .left_join(new2024, \"OBJECTID\")\n",
    "      .mutate(established=_.established.fill_null(2023))\n",
    "      .mutate(geom = _.SHAPE.convert(\"epsg:3310\",\"epsg:4326\"))\n",
    "      .rename(name = \"UNIT_NAME\", access_type = \"ACCESS_TYP\", manager = \"MNG_AGNCY\", manager_type = \"MNG_AG_LEV\", id = \"OBJECTID\")\n",
    "      .select(_.established, _.reGAP, _.name, _.access_type, _.manager, _.manager_type, _.Easement, _.Acres, _.id,  _.geom)\n",
    "     )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "6f3df8c1-a603-4dd5-be84-8deaae928d0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>manager_type</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Non Profit</td>\n",
       "      <td>458009.641083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Federal</td>\n",
       "      <td>158633.082092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>State</td>\n",
       "      <td>106750.540298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Special District</td>\n",
       "      <td>94705.677739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>County</td>\n",
       "      <td>15834.462494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>8292.326221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>City</td>\n",
       "      <td>2773.695034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Private</td>\n",
       "      <td>42.579013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Home Owners Association</td>\n",
       "      <td>39.521428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Joint Powers Authority (JPA)</td>\n",
       "      <td>11.565516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Government</td>\n",
       "      <td>4.377818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Local Government</td>\n",
       "      <td>0.029984</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    manager_type           area\n",
       "0                     Non Profit  458009.641083\n",
       "1                        Federal  158633.082092\n",
       "2                          State  106750.540298\n",
       "3               Special District   94705.677739\n",
       "4                         County   15834.462494\n",
       "5                        Unknown    8292.326221\n",
       "6                           City    2773.695034\n",
       "7                        Private      42.579013\n",
       "8        Home Owners Association      39.521428\n",
       "9   Joint Powers Authority (JPA)      11.565516\n",
       "10                    Government       4.377818\n",
       "11              Local Government       0.029984"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compute some summary tables:\n",
    "(ca\n",
    " .filter(_.established == 2024)\n",
    " .group_by(_.manager_type)\n",
    " .agg(area = _.Acres.sum())\n",
    " .order_by(_.area.desc())\n",
    " .execute()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "fa06abe4-b3fc-4b75-bce0-d1df1b3ad9fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = ca.filter(_.manager_type == \"State\").execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c62854f6-1456-4207-8c69-53af17970102",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3da2341d220f4240990fe9d2e72a05f4",
       "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": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "established = {'property': 'established',\n",
    "               'type': 'categorical',\n",
    "               'stops': [\n",
    "                   [2023, \"#26542C80\"], \n",
    "                   [2024, \"#F3AB3D80\"]]\n",
    "              }\n",
    "gap = {\n",
    "        'property': 'reGAP',\n",
    "        'type': 'categorical',\n",
    "        'stops': [\n",
    "            [1, \"#26633d\"],\n",
    "            [2, \"#879647\"],\n",
    "            [3, \"#BBBBBB\"],\n",
    "            [4, \"#F8F8F8\"]]\n",
    "        }\n",
    "paint = {\"fill-color\": established}\n",
    "\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "m.add_gdf(gdf,layer_type=\"fill\", name = \"CA 30x30\", paint = paint)\n",
    "m.add_layer_control()\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "41e73ac8-4612-49d9-9f59-b09ac0717c6d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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
}