tlc_overture_lonboard.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yellow Cab Pick-Up Points Joined with Overture Maps Buildings\n",
    "## We take hexagons from an H3 heatmap of TLC Data, processing millions of records in seconds\n",
    "## We then obtain the building geometries from Overture using DuckDB and finally join the two datasets to plot with Lonboard"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Install dependencies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: keplergl in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (0.3.2)\n",
      "Requirement already satisfied: lonboard in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (0.9.3)\n",
      "Requirement already satisfied: geopandas in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (1.0.1)\n",
      "Requirement already satisfied: matplotlib in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (3.9.2)\n",
      "Requirement already satisfied: palettable in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (3.3.3)\n",
      "Requirement already satisfied: numpy in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (2.1.1)\n",
      "Requirement already satisfied: duckdb in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (1.1.1)\n",
      "Requirement already satisfied: ipywidgets<8,>=7.0.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from keplergl) (7.8.4)\n",
      "Requirement already satisfied: traittypes>=0.2.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from keplergl) (0.2.1)\n",
      "Requirement already satisfied: pandas>=0.23.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from keplergl) (2.2.3)\n",
      "Requirement already satisfied: Shapely>=1.6.4.post2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from keplergl) (2.0.6)\n",
      "Requirement already satisfied: anywidget<0.10.0,>=0.9.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from lonboard) (0.9.13)\n",
      "Requirement already satisfied: pyarrow>=14.0.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from lonboard) (17.0.0)\n",
      "Requirement already satisfied: typing-extensions<5.0.0,>=4.6.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from lonboard) (4.12.2)\n",
      "Requirement already satisfied: pyogrio>=0.7.2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from geopandas) (0.9.0)\n",
      "Requirement already satisfied: packaging in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from geopandas) (24.1)\n",
      "Requirement already satisfied: pyproj>=3.3.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from geopandas) (3.6.1)\n",
      "Requirement already satisfied: contourpy>=1.0.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (1.3.0)\n",
      "Requirement already satisfied: cycler>=0.10 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (0.12.1)\n",
      "Requirement already satisfied: fonttools>=4.22.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (4.54.1)\n",
      "Requirement already satisfied: kiwisolver>=1.3.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (1.4.7)\n",
      "Requirement already satisfied: pillow>=8 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (10.4.0)\n",
      "Requirement already satisfied: pyparsing>=2.3.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (3.1.4)\n",
      "Requirement already satisfied: python-dateutil>=2.7 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from matplotlib) (2.9.0.post0)\n",
      "Requirement already satisfied: psygnal>=0.8.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from anywidget<0.10.0,>=0.9.0->lonboard) (0.11.1)\n",
      "Requirement already satisfied: comm>=0.1.3 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipywidgets<8,>=7.0.0->keplergl) (0.2.2)\n",
      "Requirement already satisfied: ipython-genutils~=0.2.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipywidgets<8,>=7.0.0->keplergl) (0.2.0)\n",
      "Requirement already satisfied: traitlets>=4.3.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipywidgets<8,>=7.0.0->keplergl) (5.14.3)\n",
      "Requirement already satisfied: widgetsnbextension~=3.6.9 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipywidgets<8,>=7.0.0->keplergl) (3.6.9)\n",
      "Requirement already satisfied: ipython>=4.0.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipywidgets<8,>=7.0.0->keplergl) (8.27.0)\n",
      "Requirement already satisfied: jupyterlab-widgets<3,>=1.0.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipywidgets<8,>=7.0.0->keplergl) (1.1.10)\n",
      "Requirement already satisfied: pytz>=2020.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from pandas>=0.23.0->keplergl) (2024.2)\n",
      "Requirement already satisfied: tzdata>=2022.7 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from pandas>=0.23.0->keplergl) (2024.2)\n",
      "Requirement already satisfied: certifi in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from pyogrio>=0.7.2->geopandas) (2024.8.30)\n",
      "Requirement already satisfied: six>=1.5 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from python-dateutil>=2.7->matplotlib) (1.16.0)\n",
      "Requirement already satisfied: decorator in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (5.1.1)\n",
      "Requirement already satisfied: jedi>=0.16 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.19.1)\n",
      "Requirement already satisfied: matplotlib-inline in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.1.7)\n",
      "Requirement already satisfied: prompt-toolkit<3.1.0,>=3.0.41 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (3.0.48)\n",
      "Requirement already satisfied: pygments>=2.4.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (2.18.0)\n",
      "Requirement already satisfied: stack-data in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.6.3)\n",
      "Requirement already satisfied: colorama in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.4.6)\n",
      "Requirement already satisfied: notebook>=4.4.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (7.2.2)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.3 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jedi>=0.16->ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.8.4)\n",
      "Requirement already satisfied: jupyter-server<3,>=2.4.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.14.2)\n",
      "Requirement already satisfied: jupyterlab-server<3,>=2.27.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.27.3)\n",
      "Requirement already satisfied: jupyterlab<4.3,>=4.2.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (4.2.5)\n",
      "Requirement already satisfied: notebook-shim<0.3,>=0.2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.2.4)\n",
      "Requirement already satisfied: tornado>=6.2.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (6.4.1)\n",
      "Requirement already satisfied: wcwidth in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from prompt-toolkit<3.1.0,>=3.0.41->ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.2.13)\n",
      "Requirement already satisfied: executing>=1.2.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from stack-data->ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (2.1.0)\n",
      "Requirement already satisfied: asttokens>=2.1.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from stack-data->ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (2.4.1)\n",
      "Requirement already satisfied: pure-eval in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from stack-data->ipython>=4.0.0->ipywidgets<8,>=7.0.0->keplergl) (0.2.3)\n",
      "Requirement already satisfied: anyio>=3.1.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (4.6.0)\n",
      "Requirement already satisfied: argon2-cffi>=21.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (23.1.0)\n",
      "Requirement already satisfied: jinja2>=3.0.3 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (3.1.4)\n",
      "Requirement already satisfied: jupyter-client>=7.4.4 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (8.6.3)\n",
      "Requirement already satisfied: jupyter-core!=5.0.*,>=4.12 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (5.7.2)\n",
      "Requirement already satisfied: jupyter-events>=0.9.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.10.0)\n",
      "Requirement already satisfied: jupyter-server-terminals>=0.4.4 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.5.3)\n",
      "Requirement already satisfied: nbconvert>=6.4.4 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (7.16.4)\n",
      "Requirement already satisfied: nbformat>=5.3.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (5.10.4)\n",
      "Requirement already satisfied: overrides>=5.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (7.7.0)\n",
      "Requirement already satisfied: prometheus-client>=0.9 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.21.0)\n",
      "Requirement already satisfied: pywinpty>=2.0.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.0.13)\n",
      "Requirement already satisfied: pyzmq>=24 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (26.2.0)\n",
      "Requirement already satisfied: send2trash>=1.8.2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.8.3)\n",
      "Requirement already satisfied: terminado>=0.8.3 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.18.1)\n",
      "Requirement already satisfied: websocket-client>=1.7 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.8.0)\n",
      "Requirement already satisfied: async-lru>=1.0.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.0.4)\n",
      "Requirement already satisfied: httpx>=0.25.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.27.2)\n",
      "Requirement already satisfied: ipykernel>=6.5.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (6.29.5)\n",
      "Requirement already satisfied: jupyter-lsp>=2.0.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.2.5)\n",
      "Requirement already satisfied: setuptools>=40.1.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (74.0.0)\n",
      "Requirement already satisfied: babel>=2.10 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.16.0)\n",
      "Requirement already satisfied: json5>=0.9.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.9.25)\n",
      "Requirement already satisfied: jsonschema>=4.18.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (4.23.0)\n",
      "Requirement already satisfied: requests>=2.31 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.32.3)\n",
      "Requirement already satisfied: idna>=2.8 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from anyio>=3.1.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (3.10)\n",
      "Requirement already satisfied: sniffio>=1.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from anyio>=3.1.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.3.1)\n",
      "Requirement already satisfied: argon2-cffi-bindings in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from argon2-cffi>=21.1->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (21.2.0)\n",
      "Requirement already satisfied: httpcore==1.* in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from httpx>=0.25.0->jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.0.5)\n",
      "Requirement already satisfied: h11<0.15,>=0.13 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from httpcore==1.*->httpx>=0.25.0->jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.14.0)\n",
      "Requirement already satisfied: debugpy>=1.6.5 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipykernel>=6.5.0->jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.8.6)\n",
      "Requirement already satisfied: nest-asyncio in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipykernel>=6.5.0->jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.6.0)\n",
      "Requirement already satisfied: psutil in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from ipykernel>=6.5.0->jupyterlab<4.3,>=4.2.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (6.0.0)\n",
      "Requirement already satisfied: MarkupSafe>=2.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jinja2>=3.0.3->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.1.5)\n",
      "Requirement already satisfied: attrs>=22.2.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema>=4.18.0->jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (24.2.0)\n",
      "Requirement already satisfied: jsonschema-specifications>=2023.03.6 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema>=4.18.0->jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2023.12.1)\n",
      "Requirement already satisfied: referencing>=0.28.4 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema>=4.18.0->jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.35.1)\n",
      "Requirement already satisfied: rpds-py>=0.7.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema>=4.18.0->jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.20.0)\n",
      "Requirement already satisfied: platformdirs>=2.5 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-core!=5.0.*,>=4.12->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (4.3.6)\n",
      "Requirement already satisfied: pywin32>=300 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-core!=5.0.*,>=4.12->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (306)\n",
      "Requirement already satisfied: python-json-logger>=2.0.4 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.0.7)\n",
      "Requirement already satisfied: pyyaml>=5.3 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (6.0.2)\n",
      "Requirement already satisfied: rfc3339-validator in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.1.4)\n",
      "Requirement already satisfied: rfc3986-validator>=0.1.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.1.1)\n",
      "Requirement already satisfied: beautifulsoup4 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (4.12.3)\n",
      "Requirement already satisfied: bleach!=5.0.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (6.1.0)\n",
      "Requirement already satisfied: defusedxml in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.7.1)\n",
      "Requirement already satisfied: jupyterlab-pygments in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.3.0)\n",
      "Requirement already satisfied: mistune<4,>=2.0.3 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (3.0.2)\n",
      "Requirement already satisfied: nbclient>=0.5.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.10.0)\n",
      "Requirement already satisfied: pandocfilters>=1.4.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.5.1)\n",
      "Requirement already satisfied: tinycss2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.3.0)\n",
      "Requirement already satisfied: fastjsonschema>=2.15 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from nbformat>=5.3.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.20.0)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from requests>=2.31->jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (3.3.2)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from requests>=2.31->jupyterlab-server<3,>=2.27.1->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.2.3)\n",
      "Requirement already satisfied: webencodings in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from bleach!=5.0.0->nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (0.5.1)\n",
      "Requirement already satisfied: fqdn in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.5.1)\n",
      "Requirement already satisfied: isoduration in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (20.11.0)\n",
      "Requirement already satisfied: jsonpointer>1.13 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (3.0.0)\n",
      "Requirement already satisfied: uri-template in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.3.0)\n",
      "Requirement already satisfied: webcolors>=24.6.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (24.8.0)\n",
      "Requirement already satisfied: cffi>=1.0.1 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from argon2-cffi-bindings->argon2-cffi>=21.1->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.17.1)\n",
      "Requirement already satisfied: soupsieve>1.2 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from beautifulsoup4->nbconvert>=6.4.4->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.6)\n",
      "Requirement already satisfied: pycparser in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from cffi>=1.0.1->argon2-cffi-bindings->argon2-cffi>=21.1->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.22)\n",
      "Requirement already satisfied: arrow>=0.15.0 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from isoduration->jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (1.3.0)\n",
      "Requirement already satisfied: types-python-dateutil>=2.8.10 in c:\\project_files\\tlc_buildings\\tlc_build_venv\\lib\\site-packages (from arrow>=0.15.0->isoduration->jsonschema[format-nongpl]>=4.18.0->jupyter-events>=0.9.0->jupyter-server<3,>=2.4.0->notebook>=4.4.1->widgetsnbextension~=3.6.9->ipywidgets<8,>=7.0.0->keplergl) (2.9.0.20240906)\n"
     ]
    }
   ],
   "source": [
    "! pip install lonboard geopandas matplotlib palettable numpy duckdb "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shapely \n",
    "import geopandas as gpd\n",
    "import duckdb\n",
    "import numpy as np\n",
    "from matplotlib.colors import LogNorm\n",
    "from palettable.matplotlib import Inferno_9\n",
    "from lonboard import Map, PolygonLayer\n",
    "from lonboard.colormap import apply_continuous_cmap"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a helpful snippet I reuse almost everyay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = duckdb.connect()\n",
    "con.sql(\"\"\" INSTALL h3 FROM community;\n",
    "            LOAD h3;\n",
    "            INSTALL spatial;\n",
    "            LOAD spatial;\n",
    "            INSTALL httpfs;\n",
    "            LOAD httpfs;\n",
    "            SET s3_region='us-west-2';\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I go more into detail in [this notebook](https://github.com/kentstephen/duckdb_h3/blob/main/tlc.ipynb). I borrow inspiration and some code from several NYC Fused UDFs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7116a31e3d4a42c595cb2c950ef2f964",
       "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": [
    "url = 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2010-01.parquet'\n",
    "min_cnt = 20\n",
    "resolution = 10\n",
    "\n",
    "query = f\"\"\"\n",
    "WITH to_cells as (\n",
    "SELECT\n",
    "    h3_h3_to_string(h3_latlng_to_cell(pickup_latitude, pickup_longitude, {resolution})) AS cell_id, \n",
    "    h3_cell_to_boundary_wkt(cell_id) boundary,\n",
    "    count(1) as cnt\n",
    "FROM read_parquet('{url}')\n",
    "GROUP BY 1 \n",
    "HAVING cnt >= {min_cnt})\n",
    "SELECT\n",
    "    boundary, -- Kepler in Jupyter doesn't like the H3 string so we have to use the Polygon boundary\n",
    "    cnt\n",
    "FROM to_cells\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "df = con.sql(query).df()\n",
    "gdf_cab = gpd.GeoDataFrame(df.drop(columns=['boundary']), geometry=df.boundary.apply(shapely.wkt.loads))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        cnt                                           geometry\n",
      "0     13004  POLYGON ((-73.95648 40.76834, -73.95739 40.768...\n",
      "1      6547  POLYGON ((-74.00543 40.74549, -74.00634 40.745...\n",
      "2       492  POLYGON ((-74.01573 40.7048, -74.01664 40.7046...\n",
      "3     12196  POLYGON ((-73.99001 40.74213, -73.99092 40.741...\n",
      "4      9120  POLYGON ((-73.9625 40.77384, -73.96341 40.7736...\n",
      "...     ...                                                ...\n",
      "6329     29  POLYGON ((-73.96098 40.75046, -73.9619 40.7503...\n",
      "6330     21  POLYGON ((-73.96346 40.79082, -73.96438 40.790...\n",
      "6331     20  POLYGON ((-73.93779 40.70351, -73.9387 40.7033...\n",
      "6332     39  POLYGON ((-73.92155 40.73452, -73.92246 40.734...\n",
      "6333     21  POLYGON ((-73.94682 40.75575, -73.94773 40.755...\n",
      "\n",
      "[6334 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "gdf_cab = gdf_cab.set_crs(epsg=4326, inplace=True)\n",
    "print(gdf_cab)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also find a bbox by querying Overture's land division type, but here we want some outliers out of the city so we are going to use this bbox generated with [this bounding box tool](https://boundingbox.klokantech.com/). Select the CSV option."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'xmin': -74.27507, 'xmax': -73.72905, 'ymin': 40.488386, 'ymax': 40.957151}\n"
     ]
    }
   ],
   "source": [
    "bbox_string = \"-74.27507,40.488386,-73.72905,40.957151\" #NYC\n",
    "\n",
    "# Split the string into individual float values\n",
    "bbox_values = list(map(float, bbox_string.split(',')))\n",
    "\n",
    "# Create a dictionary with the required keys and values\n",
    "bbox = {\n",
    "    'xmin': bbox_values[0],\n",
    "    'xmax': bbox_values[2],\n",
    "    'ymin': bbox_values[1],\n",
    "    'ymax': bbox_values[3]\n",
    "}\n",
    "\n",
    "print(bbox)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a pretty basic Overture DuckDB query. If you want to examine more columns you can check out the [buildings schema here](https://docs.overturemaps.org/schema/reference/buildings/building/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1a9ed2a789c14f709de3c8390326dd4c",
       "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": [
    "query = f\"\"\"\n",
    "SELECT\n",
    "    ST_AsText(geometry) as geometry\n",
    "FROM read_parquet('s3://overturemaps-us-west-2/release/2024-09-18.0/theme=buildings/type=building/*', filename=true, hive_partitioning=1)\n",
    "WHERE\n",
    "    bbox.xmin <= {bbox[\"xmax\"]}\n",
    "    AND bbox.xmax >= {bbox[\"xmin\"]}\n",
    "    AND bbox.ymin <= {bbox[\"ymax\"]}\n",
    "    AND bbox.ymax >= {bbox[\"ymin\"]}\n",
    "  \n",
    "\"\"\"\n",
    "df_buildings = con.sql(query).df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Geometries moving between Pandas and DuckDB need to be as text, so here we apply `shapely.wkt.loads` so we can make a GeoPandas GeoDataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_buildings['geometry'] = df_buildings['geometry'].apply(shapely.wkt.loads)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a GeoDataFrame and set the CRS."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POLYGON ((-74.27378 40.4893, -74.27395 40.4892...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POLYGON ((-74.27436 40.501, -74.27434 40.50108...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POLYGON ((-74.27357 40.50101, -74.27349 40.500...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POLYGON ((-74.27318 40.5009, -74.27309 40.5008...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POLYGON ((-74.27318 40.50097, -74.27307 40.500...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1746236</th>\n",
       "      <td>POLYGON ((-73.73109 40.95716, -73.73099 40.957...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1746237</th>\n",
       "      <td>POLYGON ((-73.73077 40.95716, -73.73084 40.957...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1746238</th>\n",
       "      <td>POLYGON ((-73.73049 40.95711, -73.73053 40.957...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1746239</th>\n",
       "      <td>POLYGON ((-73.73011 40.95716, -73.73015 40.957...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1746240</th>\n",
       "      <td>POLYGON ((-73.72935 40.95709, -73.72925 40.957...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1746241 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  geometry\n",
       "0        POLYGON ((-74.27378 40.4893, -74.27395 40.4892...\n",
       "1        POLYGON ((-74.27436 40.501, -74.27434 40.50108...\n",
       "2        POLYGON ((-74.27357 40.50101, -74.27349 40.500...\n",
       "3        POLYGON ((-74.27318 40.5009, -74.27309 40.5008...\n",
       "4        POLYGON ((-74.27318 40.50097, -74.27307 40.500...\n",
       "...                                                    ...\n",
       "1746236  POLYGON ((-73.73109 40.95716, -73.73099 40.957...\n",
       "1746237  POLYGON ((-73.73077 40.95716, -73.73084 40.957...\n",
       "1746238  POLYGON ((-73.73049 40.95711, -73.73053 40.957...\n",
       "1746239  POLYGON ((-73.73011 40.95716, -73.73015 40.957...\n",
       "1746240  POLYGON ((-73.72935 40.95709, -73.72925 40.957...\n",
       "\n",
       "[1746241 rows x 1 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_buildings = gpd.GeoDataFrame(df_buildings, geometry='geometry')\n",
    "gdf_buildings.set_crs(epsg=4326, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we use spatial join to merge the cabs with the buildings and drop an unneccesary index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                  geometry  cnt\n",
      "36822    POLYGON ((-74.22391 40.57405, -74.22399 40.574...   29\n",
      "36825    POLYGON ((-74.22399 40.57412, -74.22392 40.574...   29\n",
      "36826    POLYGON ((-74.22388 40.57415, -74.22382 40.574...   29\n",
      "36827    POLYGON ((-74.22437 40.57442, -74.22421 40.574...   29\n",
      "36828    POLYGON ((-74.22409 40.57438, -74.22403 40.574...   29\n",
      "...                                                    ...  ...\n",
      "1687812  POLYGON ((-73.81267 40.78368, -73.81266 40.783...  503\n",
      "1687813  POLYGON ((-73.81295 40.78383, -73.81294 40.783...  503\n",
      "1687814  POLYGON ((-73.81287 40.78382, -73.81285 40.783...  503\n",
      "1687815  POLYGON ((-73.81275 40.78379, -73.81273 40.783...  503\n",
      "1688063  POLYGON ((-73.81404 40.78397, -73.81403 40.784...  503\n",
      "\n",
      "[134270 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "gdf_joined = gdf_buildings.sjoin(gdf_cab)\n",
    "gdf_joined = gdf_joined.set_crs(epsg=4326)\n",
    "gdf_joined = gdf_joined.drop(columns=['index_right'])\n",
    "print(gdf_joined)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I got the following code [from this Lonboard Overture Maps Example](https://developmentseed.org/lonboard/latest/examples/overture-maps/). Kyle Barron has great docs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = gdf_joined[\"cnt\"].to_numpy()\n",
    "counts = np.nan_to_num(counts, nan=1)\n",
    "normalizer = LogNorm(1, counts.max(), clip=True)\n",
    "normalized_counts = normalizer(counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "Inferno_9.mpl_colormap\n",
    "colors = apply_continuous_cmap(normalized_counts, Inferno_9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "layer = PolygonLayer.from_geopandas(\n",
    "    gdf_joined,\n",
    "    get_fill_color=colors,  \n",
    ")\n",
    "m = Map(layer)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Render the map. You can drag to zoom in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1aaa0b7970e74d1ab32c0900a0e4789b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Map(layers=[PolygonLayer(get_fill_color=<pyarrow.lib.FixedSizeListArray object at 0x00000216C0EFBDC0>\n",
       "[\n",
       "  [\n",
       "  …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a dark basemap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "m.basemap_style=\"https://basemaps.cartocdn.com/gl/dark-matter-nolabels-gl-style/style.json\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### I think this is a cool template to join H3 with GeoJSON. What big dataset do you have in mind for this?"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "fused_ex_venv",
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}