{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# APSG tutorial - Part 7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``apsg.database`` module provides an SQLAlchemy interface to the PySDB database format, which stores structural geology field data in a sqlite3 file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setting up a database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start by creating a new database. The ``create=True`` argument removes any existing file and creates a fresh database with default units and structure types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:40.170469Z",
     "iopub.status.busy": "2026-05-26T10:14:40.170338Z",
     "iopub.status.idle": "2026-05-26T10:14:41.864069Z",
     "shell.execute_reply": "2026-05-26T10:14:41.863553Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PySDB database version: 3.1.0\n",
      "PySDB database CRS: EPSG:4326\n",
      "PySDB database created: 26.05.2026 12:14\n",
      "PySDB database updated: 26.05.2026 12:14\n",
      "Number of sites: 0\n",
      "Number of units: 1\n",
      "Number of structures: 2\n",
      "Number of measurements: 0\n"
     ]
    }
   ],
   "source": [
    "from apsg.database import SDBSession\n",
    "\n",
    "db = SDBSession('demo.sdb', create=True)\n",
    "print(db.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Units and Sites"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A **Unit** is a geological unit (formation, intrusion, etc.). A **Site** is a field station linked to a unit. Both are created with the get-or-create pattern: call the method with the name and metadata to create, or with just the name to retrieve."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.879978Z",
     "iopub.status.busy": "2026-05-26T10:14:41.879802Z",
     "iopub.status.idle": "2026-05-26T10:14:41.883395Z",
     "shell.execute_reply": "2026-05-26T10:14:41.883186Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unit:DMU"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unit = db.unit('DMU', description='Deamonic Magmatic Unit')\n",
    "unit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.884233Z",
     "iopub.status.busy": "2026-05-26T10:14:41.884162Z",
     "iopub.status.idle": "2026-05-26T10:14:41.887764Z",
     "shell.execute_reply": "2026-05-26T10:14:41.887553Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Site:LX001 (DMU)\n",
      "Site:LX002 (DMU)\n"
     ]
    }
   ],
   "source": [
    "site1 = db.site('LX001', unit=unit, x_coord=25934.36, y_coord=564122.5,\n",
    "                description='diorite dyke')\n",
    "site2 = db.site('LX002', unit=unit, x_coord=26100.0, y_coord=564200.0,\n",
    "                description='granite outcrop')\n",
    "print(site1)\n",
    "print(site2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Structure Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Structure types** define the kind of measurement (foliation, lineation, fracture, etc.). The ``planar`` argument distinguishes planar (1) from linear (0) features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.888571Z",
     "iopub.status.busy": "2026-05-26T10:14:41.888495Z",
     "iopub.status.idle": "2026-05-26T10:14:41.892068Z",
     "shell.execute_reply": "2026-05-26T10:14:41.891853Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Type:S2\n",
      "Type:L2\n"
     ]
    }
   ],
   "source": [
    "S2 = db.structype('S2', description='Solid-state foliation', planar=1)\n",
    "L2 = db.structype('L2', description='Solid-state lineation', planar=0)\n",
    "S3 = db.structype('S3', description='Crenulation cleavage', planar=1)\n",
    "print(S2)\n",
    "print(L2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding measurements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can add raw measurements by providing ``azimuth`` (dip direction / plunge direction) and ``inclination`` (dip / plunge) values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.892812Z",
     "iopub.status.busy": "2026-05-26T10:14:41.892702Z",
     "iopub.status.idle": "2026-05-26T10:14:41.894731Z",
     "shell.execute_reply": "2026-05-26T10:14:41.894449Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "S2:150/36\n",
      "L2:83/16\n"
     ]
    }
   ],
   "source": [
    "fol1 = db.add_structdata(site1, S2, 150, 36)\n",
    "fol2 = db.add_structdata(site1, S2, 160, 42)\n",
    "fol3 = db.add_structdata(site2, S2, 320, 65)\n",
    "lin1 = db.add_structdata(site2, L2, 83, 16)\n",
    "lin2 = db.add_structdata(site2, L2, 310, 45)\n",
    "print(fol1)\n",
    "print(lin1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or you can insert ``Foliation``, ``Lineation`` and ``Pair`` objects directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.895609Z",
     "iopub.status.busy": "2026-05-26T10:14:41.895532Z",
     "iopub.status.idle": "2026-05-26T10:14:41.897419Z",
     "shell.execute_reply": "2026-05-26T10:14:41.897153Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "S3:200/30\n",
      "L2:150/25\n"
     ]
    }
   ],
   "source": [
    "from apsg.feature import Foliation, Lineation, Pair\n",
    "\n",
    "f = Foliation(200, 30)\n",
    "l = Lineation(150, 25)\n",
    "sdata = db.add_fol(site2, S3, f)\n",
    "ldata = db.add_lin(site2, L2, l)\n",
    "print(sdata)\n",
    "print(ldata)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attaching planar to linear features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In structural geology, lineations are often measured on a specific foliation plane. The ``attach`` method links them and the ``add_pair`` method does it in one step:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.898514Z",
     "iopub.status.busy": "2026-05-26T10:14:41.898428Z",
     "iopub.status.idle": "2026-05-26T10:14:41.900372Z",
     "shell.execute_reply": "2026-05-26T10:14:41.899945Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S2:320/65 - L2:83/16"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Attach existing measurements\n",
    "pair = db.attach(fol3, lin1)\n",
    "pair"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.901349Z",
     "iopub.status.busy": "2026-05-26T10:14:41.901278Z",
     "iopub.status.idle": "2026-05-26T10:14:41.903343Z",
     "shell.execute_reply": "2026-05-26T10:14:41.903175Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S2:260.033/39.9562 - L2:218.808/32.2163"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create and attach a new pair in one step\n",
    "p = Pair(258, 42, 220, 30)  # (dip direction, dip, trend, plunge)\n",
    "ap = db.add_pair(site1, S2, L2, p)\n",
    "ap"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tags"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tags allow filtering and grouping measurements:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.904225Z",
     "iopub.status.busy": "2026-05-26T10:14:41.904146Z",
     "iopub.status.idle": "2026-05-26T10:14:41.914706Z",
     "shell.execute_reply": "2026-05-26T10:14:41.914507Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Tag:plot]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tag = db.tag('plot', description='to be plotted')\n",
    "fol_tagged = db.add_structdata(site1, S2, 190, 55, tags=[tag])\n",
    "db.commit()\n",
    "db.tags()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving data as APSG feature sets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``getset`` method returns a ``FoliationSet`` or ``LineationSet``, filtered by site, unit, or tag:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.915806Z",
     "iopub.status.busy": "2026-05-26T10:14:41.915734Z",
     "iopub.status.idle": "2026-05-26T10:14:41.919107Z",
     "shell.execute_reply": "2026-05-26T10:14:41.918829Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FoliationSet\n",
      "S(5) S2\n"
     ]
    }
   ],
   "source": [
    "g = db.getset('S2')\n",
    "print(type(g).__name__)\n",
    "print(g)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.919780Z",
     "iopub.status.busy": "2026-05-26T10:14:41.919694Z",
     "iopub.status.idle": "2026-05-26T10:14:41.924039Z",
     "shell.execute_reply": "2026-05-26T10:14:41.923818Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "S2 at LX001: S(4) S2\n",
      "S2 tagged \"plot\": S(1) S2\n"
     ]
    }
   ],
   "source": [
    "# Filter by site\n",
    "g_lx001 = db.getset('S2', site=dict(name='LX001'))\n",
    "print(f'S2 at LX001: {g_lx001}')\n",
    "\n",
    "# Filter by tag\n",
    "g_plot = db.getset('S2', tag=dict(name='plot'))\n",
    "print(f'S2 tagged \"plot\": {g_plot}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving pairs and faults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``getpairs`` method retrieves attached planar+linear measurements as a ``PairSet``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.924784Z",
     "iopub.status.busy": "2026-05-26T10:14:41.924709Z",
     "iopub.status.idle": "2026-05-26T10:14:41.932505Z",
     "shell.execute_reply": "2026-05-26T10:14:41.932327Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Too big misfit for pair S2:320/65-L2:83/16 on Site:LX002 (DMU)\n",
      "PairSet\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "P(1) S2-L2"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pairs = db.getpairs('S2', 'L2')\n",
    "print(type(pairs).__name__)\n",
    "pairs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If sense of movement information is available, use ``getfaults`` to get a ``FaultSet``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.933311Z",
     "iopub.status.busy": "2026-05-26T10:14:41.933231Z",
     "iopub.status.idle": "2026-05-26T10:14:41.944145Z",
     "shell.execute_reply": "2026-05-26T10:14:41.943929Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Too big misfit for pair S2:320/65-L2:83/16 on Site:LX002 (DMU)\n",
      "FaultSet\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "F(2) S2-L2"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from apsg.feature import Fault\n",
    "\n",
    "# Add fault-type data\n",
    "fa = Fault(280, 60, 210, 35, 1.0)  # (dd, dip, trend, plunge, sense)\n",
    "db.add_pair(site2, S2, L2, fa)\n",
    "db.commit()\n",
    "\n",
    "# Retrieve as FaultSet\n",
    "faults = db.getfaults('S2', 'L2', 1.0)\n",
    "print(type(faults).__name__)\n",
    "faults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving data as pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``df`` method returns a ``pandas.DataFrame`` with optional APSG feature columns and metadata."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.944980Z",
     "iopub.status.busy": "2026-05-26T10:14:41.944913Z",
     "iopub.status.idle": "2026-05-26T10:14:41.952198Z",
     "shell.execute_reply": "2026-05-26T10:14:41.952002Z"
    }
   },
   "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>site</th>\n",
       "      <th>x_coord</th>\n",
       "      <th>y_coord</th>\n",
       "      <th>unit</th>\n",
       "      <th>S2</th>\n",
       "      <th>tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LX001</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>DMU</td>\n",
       "      <td>S:150/36</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LX001</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>DMU</td>\n",
       "      <td>S:160/42</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LX002</td>\n",
       "      <td>26100.00</td>\n",
       "      <td>26100.00</td>\n",
       "      <td>DMU</td>\n",
       "      <td>S:320/65</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LX001</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>DMU</td>\n",
       "      <td>S:260/40</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LX001</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>25934.36</td>\n",
       "      <td>DMU</td>\n",
       "      <td>S:190/55</td>\n",
       "      <td>plot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LX002</td>\n",
       "      <td>26100.00</td>\n",
       "      <td>26100.00</td>\n",
       "      <td>DMU</td>\n",
       "      <td>S:279/61</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    site   x_coord   y_coord unit        S2  tags\n",
       "0  LX001  25934.36  25934.36  DMU  S:150/36      \n",
       "1  LX001  25934.36  25934.36  DMU  S:160/42      \n",
       "2  LX002  26100.00  26100.00  DMU  S:320/65      \n",
       "3  LX001  25934.36  25934.36  DMU  S:260/40      \n",
       "4  LX001  25934.36  25934.36  DMU  S:190/55  plot\n",
       "5  LX002  26100.00  26100.00  DMU  S:279/61      "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from apsg.pandas import pd\n",
    "\n",
    "df = db.df('S2', apsg=True, store=['site', 'unit', 'x_coord', 'y_coord'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Listing database contents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.953134Z",
     "iopub.status.busy": "2026-05-26T10:14:41.953067Z",
     "iopub.status.idle": "2026-05-26T10:14:41.955963Z",
     "shell.execute_reply": "2026-05-26T10:14:41.955664Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Units: [Unit:Default, Unit:DMU]\n",
      "Sites: [Site:LX001 (DMU), Site:LX002 (DMU)]\n",
      "Structure types: [Type:S, Type:L, Type:S2, Type:L2, Type:S3]\n",
      "Tags: [Tag:plot]\n"
     ]
    }
   ],
   "source": [
    "print('Units:', db.units())\n",
    "print('Sites:', db.sites())\n",
    "print('Structure types:', db.structypes())\n",
    "print('Tags:', db.tags())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Always close the database session when done:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-26T10:14:41.956686Z",
     "iopub.status.busy": "2026-05-26T10:14:41.956611Z",
     "iopub.status.idle": "2026-05-26T10:14:41.958028Z",
     "shell.execute_reply": "2026-05-26T10:14:41.957764Z"
    }
   },
   "outputs": [],
   "source": [
    "db.close()\n",
    "import os\n",
    "os.remove('demo.sdb')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "- Use ``SDBSession('file.sdb', create=True)`` to create a new database\n",
    "- ``unit()``, ``site()``, ``structype()``, ``tag()`` use a get-or-create pattern\n",
    "- ``add_structdata()`` adds raw azimuth/inclination measurements\n",
    "- ``add_fol()`` / ``add_lin()`` / ``add_pair()`` accept APSG feature objects\n",
    "- ``getset()`` returns ``FoliationSet`` or ``LineationSet`` with optional site/unit/tag filters\n",
    "- ``getpairs()`` / ``getfaults()`` retrieve attached measurements as ``PairSet`` / ``FaultSet``\n",
    "- ``df()`` exports data to a ``pandas.DataFrame``, optionally with APSG feature columns"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}