WBRef USMIN.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook takes a look at the USMIN source of mine symbols pulled from US Topo maps. These data essentially give us more exact locations for mining facilities denoted on maps. There is not, however, anything in the dataset that links us (unambiguously at least) to any other source of information about mines. Fundamentally, USMIN introduces the following to the GeoKB:\n",
    "\n",
    "* Pointer to a useful classification mechanism we need to incorporate on mining facility types from the 1997 AGI dictionary of mining terms\n",
    "* Specific, vetted locations for mining facility features\n",
    "* Names and geo-location context we may be able to use to link to some already known mine concepts along with clues about new mines the GeoKB doesn't know about yet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import xmltodict\n",
    "from wbmaker import WikibaseConnection\n",
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "from io import BytesIO\n",
    "from zipfile import ZipFile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "geokb = WikibaseConnection(\"GEOKB_CLOUD\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mine Facility Classification\n",
    "\n",
    "One of the key properties in the USMIN topo map symbols dataset is the facility type (ftr_type). In metadata, most of these values point to a 1997 dictionary reference from AGI. It will be useful, in a number of ways, for the GeoKB to be aware of these classifiers. In the following code blocks, we pull metadata and these labels/definitions into the GeoKB.\n",
    "\n",
    "We need to do some further work from this point:\n",
    "* subclassification of things like quarries, at least\n",
    "* consultation back to the AGI source material to see what else is useful for these concepts\n",
    "* exploration of a broader community effort to tie this into as a better foundation (e.g., ESIP SWEET)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get USMIN dataset item and pull metadata for use\n",
    "usmin_item_url = \"https://www.sciencebase.gov/catalog/item/5a1492c3e4b09fc93dcfd574?format=json\"\n",
    "usmin_item_json = requests.get(usmin_item_url).json()\n",
    "usmin_fgdc_meta_file_item = next((f for f in usmin_item_json[\"files\"] if f[\"contentType\"] == \"application/fgdc+xml\"), None)\n",
    "if usmin_fgdc_meta_file_item is not None:\n",
    "    usmin_fgdc_meta_url = usmin_fgdc_meta_file_item[\"url\"]\n",
    "    usmin_fgdc_meta_xml = requests.get(usmin_fgdc_meta_url)\n",
    "    d_usmin_fgdc_meta_xml = xmltodict.parse(usmin_fgdc_meta_xml.text, dict_constructor=dict)\n",
    "\n",
    "    ftr_type = next((i for i in d_usmin_fgdc_meta_xml[\"metadata\"][\"eainfo\"][\"detailed\"][\"attr\"] if i[\"attrlabl\"] == \"Ftr_Type\"), None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "agi_ref = geokb.datatypes.Item(\n",
    "    prop_nr=geokb.prop_lookup[\"knowledge source\"],\n",
    "    value=geokb.ref_lookup[\"Dictionary of mining, mineral, and related terms, 2nd Ed.\"]\n",
    ")\n",
    "\n",
    "usmin_ref = geokb.datatypes.Item(\n",
    "    prop_nr=geokb.prop_lookup[\"knowledge source\"],\n",
    "    value=\"Q44146\" # Not coming up in SPARQL query currently\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adit Q44148\n",
      "Air Shaft Q44149\n",
      "Bentonite Pit Q44150\n",
      "Borrow Pit Q44151\n",
      "Caliche Pit Q44152\n",
      "Chert Pit Q44153\n",
      "Cinder Pit Q44154\n",
      "Clay Pit Q44155\n",
      "Coal Mine Q44156\n",
      "Diggings Q44157\n",
      "Disturbed Surface Q44158\n",
      "Disturbed Surface - Pit Q44159\n",
      "Evaporation Pond Q44160\n",
      "Glory Hole Q44161\n",
      "Gravel Pit Q44162\n",
      "Gravel/Borrow Pit - Undifferentiated Q44163\n",
      "Hydraulic Mine Q44164\n",
      "Iron Pit Q44165\n",
      "Leach Pond Q44166\n",
      "Lignite Pit Q44167\n",
      "Marl Pit Q44168\n",
      "Mill Site Q44169\n",
      "Mine Dump Q44170\n",
      "Mine Shaft Q44171\n",
      "Open Pit Mine Q44172\n",
      "Open Pit Mine or Quarry Q44173\n",
      "Ore Stockpile/Storage Q44174\n",
      "Placer Mine Q44175\n",
      "Prospect Pit Q44176\n",
      "Quarry Q44177\n",
      "Quarry - Gypsum Q44178\n",
      "Quarry - Limestone Q44179\n",
      "Quarry - Pumice Q44180\n",
      "Quarry - Rock Q44181\n",
      "Salt Evaporator Q44182\n",
      "Sand and Gravel Pit Q44183\n",
      "Sand Pit Q44184\n",
      "Scoria Pit Q44185\n",
      "Settling Pond Q44186\n",
      "Shale Pit Q44187\n",
      "Shell Pit Q44188\n",
      "Silica Mine Q44189\n",
      "Slag Pile Q44190\n",
      "Strip Mine Q44191\n",
      "Tailings - Dredge Q44192\n",
      "Tailings - Mill Q44193\n",
      "Tailings - Placer Q44194\n",
      "Tailings - Pond Q44195\n",
      "Tailings - Undifferentiated Q44196\n",
      "Tipple Q44197\n",
      "Trench Q44198\n",
      "Uranium Mine Q44199\n"
     ]
    }
   ],
   "source": [
    "for typ in ftr_type[\"attrdomv\"]:\n",
    "    if typ[\"edom\"][\"edomv\"] != \"Mine\":\n",
    "        item = geokb.wbi.item.new()\n",
    "        item.labels.set('en', typ[\"edom\"][\"edomv\"])\n",
    "        item.descriptions.set('en', typ[\"edom\"][\"edomvd\"].split(\".\")[0][:250])\n",
    "\n",
    "        references = geokb.models.References()\n",
    "        if typ[\"edom\"][\"edomvds\"] == \"American Geological Institute (1997)\":\n",
    "            references.add(agi_ref)\n",
    "        elif typ[\"edom\"][\"edomvds\"] == \"USGS Authors\":\n",
    "            references.add(usmin_ref)\n",
    "\n",
    "        item.claims.add(\n",
    "            geokb.datatypes.Item(\n",
    "                prop_nr=geokb.prop_lookup[\"subclass of\"],\n",
    "                value=geokb.class_lookup[\"mining facility\"],\n",
    "                references=references\n",
    "            )\n",
    "        )\n",
    "        \n",
    "        response = item.write(\n",
    "            summary=\"Added classification of mining facility from USMIN source metadata\"\n",
    "        )\n",
    "        print(typ[\"edom\"][\"edomv\"], response.id)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# USMIN Exploration\n",
    "\n",
    "Note: I'll end up dumping this aspect of the notebook or moving it elsewhere. I include it in a commit for reference.\n",
    "\n",
    "The trick with the USMIN data and the GeoKB will be connecting dots on mines we already know about in the GeoKB, identifying clues about new mines, and deciding what we want to do with those clues. Since this is a good discrete example of a case where one of two methods we are exploring for \"live knowledge-banking,\" I'm going to leave off building a bot approach on USMIN digestion at this point. We'll look instead at the following:\n",
    "\n",
    "* an OpenRefine pathway where we use Ftr_Name, State, and County values to reconcile existing items in the GeoKB\n",
    "* an AGOL project pathway where we pull in the WFS from the USMIN record in ScienceBase and a query of mines from GeoKB and use both property intersections and geospatial proximity to reconcile and tease out potentially new records\n",
    "\n",
    "In the following, I "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "usmin_gdb_file_item = next((f for f in usmin_item_json[\"files\"] if \"_Geodatabase.zip\" in f[\"name\"]), None)\n",
    "r_usmin_gdb_file = requests.get(usmin_gdb_file_item[\"url\"])\n",
    "with ZipFile(BytesIO(r_usmin_gdb_file.content)) as zip_usmin_gdb:\n",
    "    zip_usmin_gdb.extractall('data/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_usmin = gpd.read_file('data/USGS_TopoMineSymbols_ver9.gdb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a simple \"identifier\" field with name:state:county from USMIN\n",
    "gdf_usmin[\"name_location\"] = gdf_usmin.apply(lambda x: f\"{x.Ftr_Name}:{x.State}:{x.County}\" if isinstance(x.Ftr_Name, str) else None, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query existing mines in GeoKB\n",
    "query_wb_mines = \"PREFIX%20wd%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fentity%2F%3E%0APREFIX%20wdt%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fprop%2Fdirect%2F%3E%0APREFIX%20p%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fprop%2F%3E%0APREFIX%20pr%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fprop%2Freference%2F%3E%0A%0ASELECT%20%3Fmine%20%3FmineLabel%20%3Floc_typeLabel%20%3FlocationAltLabel%0AWHERE%20%7B%0A%20%20%3Fmine%20wdt%3AP1%20wd%3AQ3646%20.%0A%20%20%3Fmine%20p%3AP1%20%3Finstance_of_statement%20.%0A%20%20%3Finstance_of_statement%20prov%3AwasDerivedFrom%20%3Fref%20.%0A%20%20%3Fref%20pr%3AP3%20wd%3AQ3624%20.%0A%20%20%3Fmine%20wdt%3AP11%20%3Flocation%20.%0A%20%20VALUES%20%3Flocation_type%20%7B%20wd%3AQ229%20wd%3AQ481%20%7D%0A%20%20%3Flocation%20wdt%3AP1%20%3Flocation_type%20.%0A%20%20%3Flocation%20wdt%3AP1%20%3Floc_type%20.%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22en%22%20.%20%7D%0A%7D\"\n",
    "df_wb_mines = geokb.wb_ref_data(query=query_wb_mines)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/t1/qqyjvcfx2zj0_p25jmxlnw0d86yzt7/T/ipykernel_63903/3427099717.py:5: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  df_wb_mines_grouped = df_wb_mines.sort_values([\"mine\",\"loc_typeLabel\"], ascending=False).groupby(\"mine\", as_index=False)[\"mineLabel\",\"loc_name\"].agg(list)\n"
     ]
    }
   ],
   "source": [
    "# Do some reorganization on the GeoKB records and build similar name:state:county property\n",
    "df_wb_mines[\"loc_name\"] = df_wb_mines.apply(lambda x: x[\"locationAltLabel\"].split(\",\")[0].strip() if x[\"loc_typeLabel\"] == \"U.S. County (or equivalent)\" else x[\"locationAltLabel\"], axis=1)\n",
    "df_wb_mines[\"mine\"] = df_wb_mines.mine.apply(lambda x: x.split(\"/\")[-1])\n",
    "\n",
    "df_wb_mines_grouped = df_wb_mines.sort_values([\"mine\",\"loc_typeLabel\"], ascending=False).groupby(\"mine\", as_index=False)[\"mineLabel\",\"loc_name\"].agg(list)\n",
    "df_wb_mines_grouped[\"name_location\"] = df_wb_mines_grouped.apply(lambda x: \":\".join([x[\"mineLabel\"][0], x[\"loc_name\"][0], x[\"loc_name\"][1]]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assemble what should be reasonable matches between USMIN features and GeoKB items\n",
    "geokb_usmin_probable_matches = pd.merge(\n",
    "    left=gdf_usmin[gdf_usmin.name_location.isin(df_wb_mines_grouped.name_location)][[\"name_location\",\"Ftr_Type\",\"Topo_Date\",\"geometry\"]],\n",
    "    right=df_wb_mines_grouped[[\"mine\",\"name_location\"]],\n",
    "    how=\"left\",\n",
    "    on=\"name_location\"\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For now, I took the first one of these and added a couple of claims to the existing GeoKB item as an example to work from."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>name_location</th>\n",
       "      <th>Ftr_Type</th>\n",
       "      <th>Topo_Date</th>\n",
       "      <th>geometry</th>\n",
       "      <th>mine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Northernmost Mines:AL:Franklin</td>\n",
       "      <td>Adit</td>\n",
       "      <td>1946</td>\n",
       "      <td>POINT (-87.62939 34.31061)</td>\n",
       "      <td>Q5414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Northernmost Mines:AL:Franklin</td>\n",
       "      <td>Adit</td>\n",
       "      <td>1946</td>\n",
       "      <td>POINT (-87.62951 34.31126)</td>\n",
       "      <td>Q5414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lowler Mines:AL:Marion</td>\n",
       "      <td>Adit</td>\n",
       "      <td>1946</td>\n",
       "      <td>POINT (-87.65932 34.29053)</td>\n",
       "      <td>Q5413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lowler Mines:AL:Marion</td>\n",
       "      <td>Adit</td>\n",
       "      <td>1946</td>\n",
       "      <td>POINT (-87.65651 34.29070)</td>\n",
       "      <td>Q5413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hager Mine:AL:Marion</td>\n",
       "      <td>Adit</td>\n",
       "      <td>1946</td>\n",
       "      <td>POINT (-87.65836 34.28951)</td>\n",
       "      <td>Q5412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3520</th>\n",
       "      <td>Old Michigan Mine:TX:Culberson</td>\n",
       "      <td>Open Pit Mine</td>\n",
       "      <td>1973</td>\n",
       "      <td>POINT (-104.15893 31.66827)</td>\n",
       "      <td>Q25333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3521</th>\n",
       "      <td>Whites Mine:TX:Uvalde</td>\n",
       "      <td>Open Pit Mine</td>\n",
       "      <td>1974</td>\n",
       "      <td>POINT (-100.09062 29.16093)</td>\n",
       "      <td>Q25345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3522</th>\n",
       "      <td>Southwest Ledge Quarries:TX:Lampasas</td>\n",
       "      <td>Quarry</td>\n",
       "      <td>1959</td>\n",
       "      <td>POINT (-98.39506 31.12322)</td>\n",
       "      <td>Q25342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3523</th>\n",
       "      <td>Gato Quarry:TX:Uvalde</td>\n",
       "      <td>Quarry</td>\n",
       "      <td>1974</td>\n",
       "      <td>POINT (-100.04606 29.15270)</td>\n",
       "      <td>Q25324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3524</th>\n",
       "      <td>Smyth Quarry:TX:Uvalde</td>\n",
       "      <td>Quarry</td>\n",
       "      <td>1974</td>\n",
       "      <td>POINT (-100.09484 29.13977)</td>\n",
       "      <td>Q25341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3525 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             name_location       Ftr_Type  Topo_Date  \\\n",
       "0           Northernmost Mines:AL:Franklin           Adit       1946   \n",
       "1           Northernmost Mines:AL:Franklin           Adit       1946   \n",
       "2                   Lowler Mines:AL:Marion           Adit       1946   \n",
       "3                   Lowler Mines:AL:Marion           Adit       1946   \n",
       "4                     Hager Mine:AL:Marion           Adit       1946   \n",
       "...                                    ...            ...        ...   \n",
       "3520        Old Michigan Mine:TX:Culberson  Open Pit Mine       1973   \n",
       "3521                 Whites Mine:TX:Uvalde  Open Pit Mine       1974   \n",
       "3522  Southwest Ledge Quarries:TX:Lampasas         Quarry       1959   \n",
       "3523                 Gato Quarry:TX:Uvalde         Quarry       1974   \n",
       "3524                Smyth Quarry:TX:Uvalde         Quarry       1974   \n",
       "\n",
       "                         geometry    mine  \n",
       "0      POINT (-87.62939 34.31061)   Q5414  \n",
       "1      POINT (-87.62951 34.31126)   Q5414  \n",
       "2      POINT (-87.65932 34.29053)   Q5413  \n",
       "3      POINT (-87.65651 34.29070)   Q5413  \n",
       "4      POINT (-87.65836 34.28951)   Q5412  \n",
       "...                           ...     ...  \n",
       "3520  POINT (-104.15893 31.66827)  Q25333  \n",
       "3521  POINT (-100.09062 29.16093)  Q25345  \n",
       "3522   POINT (-98.39506 31.12322)  Q25342  \n",
       "3523  POINT (-100.04606 29.15270)  Q25324  \n",
       "3524  POINT (-100.09484 29.13977)  Q25341  \n",
       "\n",
       "[3525 rows x 5 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geokb_usmin_probable_matches"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "geokb",
   "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.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}