Assemble SB Items for CRC Cores and Cuttings.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook explores a couple of the concepts we are pursuing for the next generation of the National Digital Catalog by using the USGS Core Research Center (CRC) as an example use case. The CRC manages collections of rock cores and cuttings at the USGS facility in Lakewood. The CRC has had a databased inventory for decades, the \"CRC Well Catalog,\" that has been through several different generations of technology. This situation is generally typical across many of the other institutions managing geoscience collections (or other scientific collections for that matter). The CRC inventory management system consists of the following major technical components:\n",
    "\n",
    "* A highly normalized relational database in PostgreSQL\n",
    "* A custom web application for managing the database, accessed internally by CRC staff\n",
    "* An ArcGIS MapServer [service](https://my.usgs.gov/arcgis/rest/services/crcwc/crcwc/MapServer) that provides feature services for the cores and cuttings collections along with supporting layers\n",
    "* A custom [web application](https://my.usgs.gov/crcwc/) for public search and browse\n",
    "\n",
    "One of the principles that we are pursuing for the next gen catalog is to try and make maximum use of whatever exists as operational infrastructure from NDC contributors. We want to move from a \"push to\" to a \"draw from\" modality. In the draw from mode, we are talking about software that is able to read whatever is available online and integrate the information into the NDC. So, the fundamental question is, how well does the CRC present its inventory to the world in a way that a software algorithm can read it and digest the information for use. We are examining this in terms of two main categories of data integration problem solving: syntactic and semantic.\n",
    "\n",
    "So far, the CRC has only been represented in the NDC with a one-time data dump to CSV files of a small subset of its inventory. These records were organized into two collections in ScienceBase ([Cuttings](https://www.sciencebase.gov/catalog/item/4f4e49d8e4b07f02db5df2d2) and [Cores](https://www.sciencebase.gov/catalog/item/4f4e49dae4b07f02db5e0486)). In the process I lay out here, I removed the existing items and then replaced them with a new structure for the full set of items.\n",
    "\n",
    "All in all, this process does work, and I used it to build out 65K items or so of new information in the NDC. However, there are a number of pretty major pitfalls encountered here that I discuss in the notes below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sciencebasepy import SbSession\n",
    "from IPython.display import display\n",
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To actually put items in ScienceBase, you need to be logged in. Since I am both deleting old items and inserting new items in the code below, I login with my credentials. NDC contributors who have access credentials of their own and one or more collections where write permissions have been granted could do something similar. If we are to run any \"bots\" like this in production, we will need to think about some type of service account(s) that have access permissions to build out various parts of the catalog."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Username:  sbristol@usgs.gov\n",
      "········\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sciencebasepy.SbSession.SbSession at 0x11c06a750>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sb = SbSession()\n",
    "username = input(\"Username:  \")\n",
    "sb.loginc(str(username))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This codeblock ran through the two collections (cores and cuttings) and deleted all of the existing child items imported under an older process. These items were severely out of date with the current live inventory, having been put into the system in 2011. Note: this is actually kind of pseudo-code as the ScienceBase API is not stable enough to support running through this entire deletion process without being restarted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for parent in [\"4f4e49d8e4b07f02db5df2d2\",\"4f4e49dae4b07f02db5e0486\"]:\n",
    "    items = sb.find_items({\"max\": 1000, \"parentId\": parent})\n",
    "    while items and 'items' in items:\n",
    "        for item in items['items']:\n",
    "            sb.delete_item(item)\n",
    "        items = sb.next(items)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I wrote all of the basic logic needed to make this system work into a set of functions called from a companion file here in this repo. I called it crcwc_to_sb.py, deliberately, because the majority of the logic contained in the file is very specific to this particular use case and will not really be functional anywhere else other than serving as a design pattern for a similar specialized case. Some aspects of the code, such as querying an ArcGIS MapServer service may be extensible beyond this specialized case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from crcwc_to_sb import *\n",
    "\n",
    "crc_root_collections = {\n",
    "    \"cores\": \"4f4e49dae4b07f02db5e0486\",\n",
    "    \"cuttings\": \"4f4e49d8e4b07f02db5df2d2\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get data from ArcGIS MapServer\n",
    "The first thing we do here is go out and retrieve all the records from the two MapServer layers that provide the basic \"raw\" records from the CRC Well Catalog. These two service layers were really set up for the purpose of driving the web map tool on the CRC web site. This is a generally common thing where a web service of some type was set up to drive a web tool, but it was not really intended as a data distribution service. This means that the services may provide reasonably good geometry for viewing points or other features on a map and some set of properties deemed appropriate for a \"getFeatureInfo popup box,\" but are not necessarily the properties we would want to pull together into some other system.\n",
    "\n",
    "In particular, the naming of the properties and the structure of the values may not be semantically conducive to integration with data from other systems. What seems to happen a lot of times is that folks will set up a web search app like the CRC has and then build some type of translational logic into the web app code that displays more human friendly property names. This is a problem when we want to write software to work with the information and take it off into some other context. That translational logic (part of the semantics) isn't encoded in any accessible form that the software can consult.\n",
    "\n",
    "In the case of the CRC, the two MapServer layers are backed by views in the PostGIS database that assemble the set of properties that the designers decided they wanted to include. Not all of those properties are expressed through the web app, and the main manager for the CRC inventory was actually not even aware that the MapServer made them available at all (likely not an uncommon situation). Because it is driven by a view, meaning there is a configuration principal that sits between the data and its online API expression, we have the opportunity to go in and tweak it to potentially modify the field names and contents that are expressed to align with some standard.\n",
    "\n",
    "There are about 65K records between these two layers, which takes a while to bring back from ArcGIS Server at its max of 1000 records per query, so I go ahead and cache two files locally for use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.49 s, sys: 168 ms, total: 1.65 s\n",
      "Wall time: 1.83 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "required_files = [\"core.json\",\"cutting.json\"]\n",
    "\n",
    "if required_files == [f for f in required_files if os.path.isfile(f)]:\n",
    "    with open('core.json', 'r') as f:\n",
    "        cores = json.load(f)\n",
    "        f.close()\n",
    "\n",
    "    with open('cutting.json', 'r') as f:\n",
    "        cuttings = json.load(f)\n",
    "        f.close()\n",
    "        \n",
    "    df_cores = pd.DataFrame([f[\"properties\"] for f in cores])\n",
    "    df_cuttings = pd.DataFrame([f[\"properties\"] for f in cuttings])\n",
    "\n",
    "else:\n",
    "    for sample_type in [\"core\",\"cutting\"]:\n",
    "        offset = 0\n",
    "        crc_records = list()\n",
    "        server_response = crcwc_items(sample_type=sample_type, offset=offset)\n",
    "        while len(server_response[\"features\"]) > 0:\n",
    "            crc_records.extend(server_response[\"features\"])\n",
    "            offset += len(server_response[\"features\"])\n",
    "            server_response = crcwc_items(sample_type=sample_type, offset=offset)\n",
    "        with open(f'{sample_type}.json', 'w') as f:\n",
    "            json.dump(crc_records, f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mapping the Data\n",
    "Figuring out how to map the CRC items from the two MapServer layers to ScienceBase took a little exploration through the data. I focused on meaningful identifiers along with the properties that I used to create titles and contacts. Everything else will have to be teased out at a later time.\n",
    "\n",
    "As you can see in the next codeblocks, the property names and the contents are quite specific to the CRC and not easily translatable to some higher level standards or conventions. The information in this database is reflective of its long history. It started life in a FoxPro database (or possibly something older still that is now lost to history) and has been through various translations since without any significant work on reorganizing. This is really because of the core use of the database, which is for expert collection managers who know their information well to manage the physical plant itself. Select information from the collections are made available online for a specialized user community, but that information was never intended for the masses.\n",
    "\n",
    "I think this is actually a very fundamental question that we should ask ourselves as a community as we head toward a new structure and design for the next gen catalog - just how much do we want to invest in harmonizing information across the catalog and moving it from a purely specialized audience to something at least somewhat broader.\n",
    "\n",
    "I shoved the properties data from the two MapServer layers into Pandas dataframes for ease of use. The following tests run through the miniomal things I needed to know about the data in order to determine how to map into the ScienceBase Items. I include it here for future reference and to share a little bit about what's in these data for others to think about in different use cases.\n",
    "\n",
    "## Common and Uncommon Properties\n",
    "In this first codeblock, I take a look at the properties in each collection and compare them to see what's in common. Visually and content wise, it looks like there should be some additional properties in common that are just labeled a little differently in each collection for whatever historic reason. At the stage of expressing non-standardized and semantically aligned data to the NDC from various providers, it might be a good idea to at least harmonize across collections from the same provider source wherever possible to reduce the confusion at least a little bit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cores_properties = list(df_cores.columns)\n",
    "cores_properties.sort()\n",
    "cuttings_properties = list(df_cuttings.columns)\n",
    "cuttings_properties.sort()\n",
    "common_properties = list(set(cuttings_properties).intersection(set(cores_properties)))\n",
    "common_properties.sort()\n",
    "\n",
    "print(\"Properties in Cores\")\n",
    "print(cores_properties)\n",
    "print(\"---------------\")\n",
    "print(\"Properties in Cuttings\")\n",
    "print(cuttings_properties)\n",
    "print(\"---------------\")\n",
    "print(\"Properties in Common\")\n",
    "print(common_properties)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifiers and Contacts\n",
    "I'm going to try to pull any meaningful identifiers and contacts from the properties as possible as these are two of the available data structures in ScienceBase that we can leverage. In the following codeblock, I take a look at select properties from each collection that seem to have the information I want to use and show how many nulls are in each so I know how to write my item building code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Primary identifiers in Cores\")\n",
    "print(\"id\", len(df_cores.loc[df_cores[\"id\"].isnull()]))\n",
    "print(\"libno\", len(df_cores.loc[df_cores[\"libno\"].isnull()]))\n",
    "print(\"chlibno\", len(df_cores.loc[df_cores[\"chlibno\"].isnull()]))\n",
    "print(\"apiwel\", len(df_cores.loc[df_cores[\"apiwel\"].isnull()]))\n",
    "print(\"oper\", len(df_cores.loc[df_cores[\"oper\"].isnull()]))\n",
    "print(\"====================\")\n",
    "print(\"Primary identifiers in Cuttings\")\n",
    "print(\"id\", len(df_cuttings.loc[df_cuttings[\"id\"].isnull()]))\n",
    "print(\"chlibno\", len(df_cuttings.loc[df_cuttings[\"chlibno\"].isnull()]))\n",
    "print(\"apinum\", len(df_cuttings.loc[df_cuttings[\"apinum\"].isnull()]))\n",
    "print(\"operator\", len(df_cuttings.loc[df_cuttings[\"operator\"].isnull()]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Operator Contacts\n",
    "The operator field in each collection contains a string name for what seems to usually be the drilling company that drilled the core or provided the cuttings. It seems to be reasonably populated, it could provide useful information linking to other systems, and there is a reasonable part of the ScienceBase data model to support the information that is added to the index as a search facet. However, when you look at the content, it's pretty messy and so of kind of limited use outside its original context. The following codeblock shows the top 100 records grouped on operator name from the cores collection to demonstrate this dynamic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for item in [(operator, len(group)) for operator, group in df_cores.groupby(\"oper\")][:100]:\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Build Items and Submit to ScienceBase\n",
    "Now that we have the basic information to start with from the two MapServer layers, we can assemble the particular data structure in current use for the NDC. This is based on the ScienceBase Item model as we've currently instantiated the NDC as collections of items in the ScienceBase Catalog. As we'll discuss later, this model has a number of limitations, but it is what we are using today, so I'm making the best use of it that I can.\n",
    "\n",
    "All of the logic for building these items comes from the set of helper functions in crcwc_to_sb. It starts with the sb_item_from_crcwc function, which specifies the type of item and sends the item content (GeoJSON feature) from the MapServer retrieval we ran earlier. We send the \"sample_type\" variable to the functions as a convenience to switch between the configurations needed for each source schema, which are similar but have distinct differences. I started down the path of detecting the inbound schema, mappoing everything to common properties, and then acting accordingly, but that process would take a fair bit longer to work out and code up, so it is ugly code for the time being.\n",
    "\n",
    "I made a few design choices here, essentially because ScienceBase is cranky about how many requests we can pass in short order. This would be a heck of a lot faster with multiprocessing, and we should be able to run it in parallel except for the limitations of working over HTTP with cranky APIs. So, I take some advantage of this need to slow things down and go ahead and assemble some related information into the items in real time.\n",
    "\n",
    "The CRC database has some additional interesting content that is not expressed through the MapServer layers. This includes interval information on the depth ranges within a core, including geologic formation and age values, tables of thin sections that were taken from some samples, links to core photos, and links to downloadable files (variety of related information referred to as \"analysis\" files). I use the Beautifulsoup package to scrape this information from the web pages, which is the only way that it is currently expressed, and inject it into the prepared items.\n",
    "\n",
    "I also used this opportunity to demonstrate something that we would likely provide as an information enhancement across the entire NDC rather than collection by collection. The Macrostrat folks have built a nicely usable API to all of the various datasets they have assembled from multiple sources. Their relatively simple and fast API route used for mobile applications provides a method for retrieving basic information derived from geologic maps for any given point location. I used this to add tags to the items for geologic formation, age, and rock types. At least for these collections, we will be able to examine the use of these tags as value-added facets in the data that come into play because we are able to link out to a related and relevant information source and bring data into our context.\n",
    "\n",
    "I parse out information from these processes into identifiers, title, body, contacts, tags, and web links within the ScienceBase Items. I went ahead and shoved the full set of raw data structures from the MapServer item properties, web site scrape, and Macrostrat API into the body of the items. While this is ugly looking, it does add a bunch of content to the search index as items are added to the catalog. It does not allow for directed searching, categorizing, and faceting, but it does enhance the potential findability of the records. This also represents a possible strategy we might follow for the nextgen NDC - start every collection that wants to contribute by harvesting every bit of content that we possibly can, map anything we can figure out into a core set of commonly understood properties, shove everything else into an \"anytext\" area to make it available for search, and improve semantic mapping incrementally over time.\n",
    "\n",
    "The way we are going about this today, using ScienceBase as our backbone for the NDC, has advantages and disadvantages. As I've found in the past, trying to do batch operations with thousands of items runs into issues with ScienceBase aborting the operations after a time and having to be started fresh. Because of that, I first run through here and get the current identifiers for the collection I'm operating against, subset the list of source items for everything not in that list, and then run that through the process to generate and insert ScienceBase Items. This lets me pick up where the process fails and start anew. An alternative approach would be to prepare the full batch of items for ScienceBase, load that as a file to the ScienceBase Catalog, and then trigger some process on the ScienceBase side to process those into items. Most of the other collections in the NDC work this way today using the custom file processors for NGGDPP CSV or XML formats. There is another type of file processor that uses a custom JSON configuration snippet to map source properties to ScienceBase Item properties, but it still works with flat text files, which is not what we have at the end of this process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.sciencebase.gov/catalog/item/5d84c770e4b0c4f70d072c33\n",
      "https://www.sciencebase.gov/catalog/item/5d84c771e4b0c4f70d072c43\n",
      "https://www.sciencebase.gov/catalog/item/5d84c773e4b0c4f70d072c4e\n",
      "https://www.sciencebase.gov/catalog/item/5d84c774e4b0c4f70d072c59\n",
      "https://www.sciencebase.gov/catalog/item/5d84c775e4b0c4f70d072c65\n",
      "https://www.sciencebase.gov/catalog/item/5d84c776e4b0c4f70d072c71\n",
      "https://www.sciencebase.gov/catalog/item/5d84c779e4b0c4f70d072c74\n",
      "https://www.sciencebase.gov/catalog/item/5d84c77ae4b0c4f70d072c76\n",
      "https://www.sciencebase.gov/catalog/item/5d84c77be4b0c4f70d072c78\n",
      "https://www.sciencebase.gov/catalog/item/5d84c77de4b0c4f70d072c7a\n",
      "https://www.sciencebase.gov/catalog/item/5d84c77ee4b0c4f70d072c7c\n",
      "https://www.sciencebase.gov/catalog/item/5d84c77fe4b0c4f70d072c7e\n",
      "https://www.sciencebase.gov/catalog/item/5d84c780e4b0c4f70d072c80\n",
      "https://www.sciencebase.gov/catalog/item/5d84c782e4b0c4f70d072c82\n",
      "https://www.sciencebase.gov/catalog/item/5d84c782e4b0c4f70d072c84\n",
      "https://www.sciencebase.gov/catalog/item/5d84c784e4b0c4f70d072c86\n",
      "https://www.sciencebase.gov/catalog/item/5d84c785e4b0c4f70d072c88\n",
      "https://www.sciencebase.gov/catalog/item/5d84c786e4b0c4f70d072c8a\n",
      "https://www.sciencebase.gov/catalog/item/5d84c787e4b0c4f70d072c8c\n",
      "https://www.sciencebase.gov/catalog/item/5d84c789e4b0c4f70d072c8e\n",
      "https://www.sciencebase.gov/catalog/item/5d84c78ae4b0c4f70d072c90\n",
      "https://www.sciencebase.gov/catalog/item/5d84c78be4b0c4f70d072c92\n",
      "https://www.sciencebase.gov/catalog/item/5d84c78ce4b0c4f70d072c94\n",
      "https://www.sciencebase.gov/catalog/item/5d84c78de4b0c4f70d072c96\n",
      "https://www.sciencebase.gov/catalog/item/5d84c78ee4b0c4f70d072c98\n",
      "https://www.sciencebase.gov/catalog/item/5d84c78fe4b0c4f70d072c9a\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "current_ids = list()\n",
    "items = sb.find_items({\n",
    "    \"max\": 1000, \n",
    "    \"parentId\": crc_root_collections[\"cores\"], \n",
    "    \"fields\": \"identifiers\"\n",
    "})\n",
    "while items and 'items' in items:\n",
    "    for identifier in [item[\"identifiers\"] for item in items[\"items\"]]:\n",
    "        this_id = next(i[\"key\"] for i in identifier if i[\"scheme\"] == \"CRC Well Catalog Database ID\")\n",
    "        current_ids.append(this_id)\n",
    "    items = sb.next(items)\n",
    "\n",
    "for item in [i for i in cores if i[\"id\"] not in current_ids]:\n",
    "    new_item = sb.create_item(sb_item_from_crcwc(\"core\", item))\n",
    "    print(new_item[\"link\"][\"url\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}