Pulling FWS TESS info from TIR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We periodically cache information about taxa of interest from the USFWS Threatened and Endangered Species System (TESS) in the Taxonomic Information Registry. This information is displayed in places like the State Wildlife Action Plan (SWAP) online app and the National Biogeographic Map.\n",
    "\n",
    "TESS data are stored as key/value pairs in an hstore column of the \"tir2\" data store in the experimental GC2 platform we are working with. This notebook shows a couple of methods for retrieving TESS information via the GC2 API for use in web applications.\n",
    "\n",
    "There is a TESS record for every taxon that is registered in the TIR. When no information from TESS was available with the query (based on ITIS TSN), there will be a result=>none key/value and a cacheDate, incidating the date/time the query was run.\n",
    "\n",
    "The following queries, based on the SQL API, are probably the best options with hstore values. The data have also been piped to ElasticSearch in the GC2 instance, but they will come back as strings that need to be parsed instead of simple JSON. Note that there should only be a single result coming back for any given TSN or any originally submitted scientific name from the SGCN. However, there is not an absolute requirement that only a single record exist in the database for any of these key identifying features. You might harden your code by checking to make sure that there is only a single record and dealing with any duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CommonName': 'Pahranagat roundtail chub',\n",
       " 'ListingDate': '1970-10-13',\n",
       " 'PopulationDescription': 'Wherever found',\n",
       " 'SpeciesCode': 'E00R',\n",
       " 'Status': 'E',\n",
       " 'StatusText': 'Endangered',\n",
       " 'cacheDate': '2017-05-07T16:38:34.510363',\n",
       " 'entityId': '226',\n",
       " 'result': 'success',\n",
       " 'tsn': '201922'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Query based on ITIS tsn\n",
    "tsn = 201922\n",
    "\n",
    "q = \"SELECT to_json(tess) as tess FROM tir.tir2 \\\n",
    "    WHERE tess->'tsn' = '\"+str(tsn)+\"'\"\n",
    "\n",
    "r = requests.get(\"https://gc2.mapcentia.com/api/v1/sql/bcb?q=\"+q).json()\n",
    "\n",
    "display (r[\"features\"][0][\"properties\"][\"tess\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CommonName': 'Popolo',\n",
       " 'ListingDate': '2016-10-31',\n",
       " 'PopulationDescription': 'Wherever found',\n",
       " 'SpeciesCode': 'Q21R',\n",
       " 'Status': 'E',\n",
       " 'StatusText': 'Endangered',\n",
       " 'cacheDate': '2017-05-07T16:38:54.242871',\n",
       " 'entityId': '6870',\n",
       " 'result': 'success',\n",
       " 'tsn': '30483'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Query based on a submitted SGCN scientific name\n",
    "scientificname = \"Solanum nelsonii\"\n",
    "\n",
    "q = \"SELECT to_json(tess) as tess FROM tir.tir2 \\\n",
    "    WHERE registration->'SGCN_ScientificName_Submitted' = '\"+scientificname+\"'\"\n",
    "\n",
    "r = requests.get(\"https://gc2.mapcentia.com/api/v1/sql/bcb?q=\"+q).json()\n",
    "\n",
    "display (r[\"features\"][0][\"properties\"][\"tess\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}