{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# 02. Floridan aquifer system - batch retrieval\n",
        "Lester J. Williams | LCG Mapper In Chief\n",
        "\n",
        "Paste a tab-delimited point table, retrieve the server-generated detailed CSV, and optionally draw\n",
        "rough major-unit sticks in supplied order. No ArcGIS license is needed. Requires `pandas matplotlib ipykernel`.\n",
        "Run steps in order. Duplicate UWIs and extra input columns are retained. Files use fixed names and are\n",
        "replaced when rerun. These sticks are individual point estimates, not interpolated cross sections."
      ],
      "id": "cell-01"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1. Connect to the same service as the website"
      ],
      "id": "cell-02"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "execution_count": null,
      "outputs": [],
      "source": [
        "import io\n",
        "import json\n",
        "import os\n",
        "import urllib.error\n",
        "import urllib.parse\n",
        "import urllib.request\n",
        "from pathlib import Path\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "from IPython.display import display\n",
        "\n",
        "API_BASE = os.environ.get(\"LCG_SUBSURFACE_API_BASE\", \"https://subsurface.logiccloudgeo.com\").rstrip(\"/\")\n",
        "# For a local development API, use: API_BASE = \"http://127.0.0.1:8000\"\n",
        "OUTPUT_DIR = Path(\"LCG_Retrieval_Results\")\n",
        "OUTPUT_DIR.mkdir(exist_ok=True)\n",
        "\n",
        "def request(path, payload=None):\n",
        "    body = None if payload is None else json.dumps(payload).encode(\"utf-8\")\n",
        "    req = urllib.request.Request(API_BASE + path, data=body,\n",
        "        headers={\"Accept\": \"application/json, text/csv\", \"Content-Type\": \"application/json\"})\n",
        "    try:\n",
        "        with urllib.request.urlopen(req, timeout=180) as response:\n",
        "            return response.read()\n",
        "    except urllib.error.HTTPError as error:\n",
        "        raise RuntimeError(f\"HTTP {error.code}: {error.read().decode('utf-8', errors='replace')}\") from error\n",
        "    except urllib.error.URLError as error:\n",
        "        raise RuntimeError(f\"Cannot reach {API_BASE}. Check endpoint readiness or select your local API.\") from error\n",
        "\n",
        "ready = json.loads(request(\"/v2/ready\"))\n",
        "model_response = json.loads(request(\"/v2/model\"))\n",
        "model = model_response[\"configuration\"]\n",
        "print(\"API:\", API_BASE, \"| Ready:\", ready[\"status\"], \"| Model:\", model[\"model_version\"])\n",
        "print(\"Configuration SHA-256:\", model_response[\"sha256\"])"
      ],
      "id": "cell-03"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2. Paste points from Excel or a text editor\n",
        "Replace the text between triple quotes, including the header. Required columns are UWI, latitude,\n",
        "longitude (WGS84 decimal degrees); optional additional columns are preserved. Keep transect rows in\n",
        "spatial order. Latitude comes before longitude here. Do not paste projected metre coordinates."
      ],
      "id": "cell-04"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "execution_count": null,
      "outputs": [],
      "source": [
        "POINTS_TSV = \"\"\"UWI\tlatitude\tlongitude\n",
        "OCALA_REVIEW\t29.183302\t-82.211380\n",
        "R001\t31.123456\t-84.123456\n",
        "\"\"\"\n",
        "source = pd.read_csv(io.StringIO(POINTS_TSV.strip()), sep=\"\\t\", dtype=str, keep_default_na=False)\n",
        "if not {\"UWI\", \"latitude\", \"longitude\"}.issubset(source.columns):\n",
        "    raise ValueError(\"Include UWI, latitude and longitude headers separated by tabs.\")\n",
        "print(\"Rows:\", len(source))\n",
        "with pd.option_context(\"display.max_rows\", 20, \"display.max_columns\", None):\n",
        "    display(source)"
      ],
      "id": "cell-05"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3. Retrieve detailed values in bounded chunks\n",
        "The server supplies all scientific columns and row/unit statuses. Each request uses at most 20 points\n",
        "and never exceeds the configuration limit. Failures raise an error before writing a partial CSV;\n",
        "completed chunks are not silently substituted for a complete result. Slow connections may need smaller chunks."
      ],
      "id": "cell-06"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "execution_count": null,
      "outputs": [],
      "source": [
        "chunk_size = min(20, model[\"batch\"][\"max_points\"])\n",
        "if source.empty:\n",
        "    raise ValueError(\"Provide at least one point.\")\n",
        "chunks = []\n",
        "for offset in range(0, len(source), chunk_size):\n",
        "    rows = source.iloc[offset:offset+chunk_size]\n",
        "    payload = rows.to_csv(index=False, sep=\"\\t\", lineterminator=\"\\n\")\n",
        "    raw = request(\"/v2/batch\", {\"points\": payload}).decode(\"utf-8-sig\")\n",
        "    frame = pd.read_csv(io.StringIO(raw), dtype=str, keep_default_na=False)\n",
        "    if len(frame) != len(rows) or frame[\"UWI\"].tolist() != rows[\"UWI\"].tolist():\n",
        "        raise RuntimeError(\"Batch row order/count changed; no result file written.\")\n",
        "    chunks.append(frame)\n",
        "    print(f\"Retrieved {offset+len(rows)} / {len(source)} rows\")\n",
        "result = pd.concat(chunks, ignore_index=True)\n",
        "if result[\"manifest_sha256\"].nunique() != 1 or result[\"model_sha256\"].nunique() != 1:\n",
        "    raise RuntimeError(\"Service release changed during retrieval; rerun for consistent lineage.\")\n",
        "output = OUTPUT_DIR / \"Batch_subsurface_results.csv\"\n",
        "result.to_csv(output, index=False, encoding=\"utf-8-sig\")\n",
        "print(\"SAVED:\", output.resolve())\n",
        "print(result[\"row_status\"].value_counts().to_string())\n",
        "display(result.iloc[:5, :12])"
      ],
      "id": "cell-07"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4. Optional rough major-unit sticks\n",
        "Positions below are ordinal (input row order), not distance along a surveyed line. Repeated UWIs remain\n",
        "separate sticks. Outside-domain/invalid/unavailable rows keep their positions and have no invented fill.\n",
        "Use detailed CSV fields such as OCAPLPZ_top and OCAPLPZ_bot for later GIS or cross-section workflows.\n",
        "Parent aquifers and detailed zones must not be added as separate thicknesses."
      ],
      "id": "cell-08"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "execution_count": null,
      "outputs": [],
      "source": [
        "fig, ax = plt.subplots(figsize=(max(9, len(result)*.35), 7))\n",
        "for spec in model[\"major_units\"]:\n",
        "    key = spec[\"id\"]\n",
        "    for index, row in result.iterrows():\n",
        "        if row.get(key+\"_status\") != \"available\":\n",
        "            continue\n",
        "        top = pd.to_numeric(row.get(key+\"_top\"), errors=\"coerce\")\n",
        "        bot = pd.to_numeric(row.get(key+\"_bot\"), errors=\"coerce\")\n",
        "        if pd.notna(top) and pd.notna(bot) and top > bot:\n",
        "            ax.bar(index, top-bot, bottom=bot, width=.65, color=spec[\"color\"], edgecolor=\"#52616a\")\n",
        "from matplotlib.patches import Patch\n",
        "ax.legend(handles=[Patch(facecolor=u[\"color\"], edgecolor=\"#52616a\", label=u[\"label\"])\n",
        "                   for u in model[\"major_units\"] if u[\"id\"] != \"LCU\"],\n",
        "          loc=\"upper left\", bbox_to_anchor=(1.02,1), fontsize=8)\n",
        "ax.set_xticks(range(len(result)), result[\"UWI\"], rotation=90)\n",
        "ax.set_ylabel(\"Elevation (U.S. survey ft NAVD88)\")\n",
        "ax.set_xlabel(\"Input row order - not distance\")\n",
        "ax.set_title(\"Rough hydrogeologic sticks - regional point estimates\")\n",
        "ax.grid(axis=\"y\", alpha=.25)\n",
        "fig.savefig(OUTPUT_DIR / \"Batch_major_unit_sticks.svg\", bbox_inches=\"tight\")\n",
        "plt.show()"
      ],
      "id": "cell-09"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5. Use the CSV with care\n",
        "Numeric blanks mean missing or inapplicable: read the corresponding `_status` and `row_status`.\n",
        "Zero depth or thickness is a valid number. The LCU has no mapped lower boundary. TDS_reference may\n",
        "be FAS base for fresh-to-base areas; TDS_interface is the independently sampled raw interface.\n",
        "Do not confuse them. `model_sha256` and `manifest_sha256` identify the interpretation/data release.\n",
        "\n",
        "Technical methods: https://logiccloudgeo.com/subsurface/how-it-works/"
      ],
      "id": "cell-10"
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.12"
    },
    "authors": [
      {
        "name": "Lester J. Williams"
      }
    ]
  },
  "nbformat": 4,
  "nbformat_minor": 5
}