Pulling NatureServe info from TIR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We periodically cache information about taxa of interest from the NatureServe species service 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",
    "NatureServe 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 NatureServe information via the GC2 API for use in web applications.\n",
    "\n",
    "There is a NatureServe record for every taxon that is registered in the TIR. When no information from NatureServe was available with the query (based on scientific name lookup of the Element Global ID and retrieval based on that ID), there will be a status=>Not Found (or status=>error in cases where the code ran into a problem with the NatureServe service) 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": 2,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'GlobalStatusRank': 'G3T1',\n",
       " 'StateCode:Nevada': 'S1',\n",
       " 'cacheDate': '2017-05-07T18:16:00.816966',\n",
       " 'elementGlobalID': 'ELEMENT_GLOBAL.2.102241',\n",
       " 'globalStatusLastReviewed': '2001-07-02',\n",
       " 'roundedGlobalStatusRankDescription': 'Critically Imperiled',\n",
       " 'usNationalStatusLastReviewed': '2001-07-02',\n",
       " 'usNationalStatusRankCode': 'N1'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Query based on ITIS tsn\n",
    "tsn = 201922\n",
    "\n",
    "q = \"SELECT to_json(natureserve) as natureserve FROM tir.tir2 \\\n",
    "    WHERE itis->'discoveredTSN' = '\"+str(tsn)+\"' \\\n",
    "    OR itis->'acceptedTSN' = '\"+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\"][\"natureserve\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'GlobalStatusRank': 'G2',\n",
       " 'StateCode:Illinois': 'SX',\n",
       " 'StateCode:Iowa': 'S1',\n",
       " 'StateCode:Minnesota': 'S2',\n",
       " 'StateCode:North Dakota': 'S2',\n",
       " 'StateCode:South Dakota': 'S2',\n",
       " 'cacheDate': '2017-05-07T18:17:19.612952',\n",
       " 'elementGlobalID': 'ELEMENT_GLOBAL.2.119247',\n",
       " 'globalStatusLastReviewed': '2011-03-22',\n",
       " 'roundedGlobalStatusRankDescription': 'Imperiled',\n",
       " 'usNationalStatusLastReviewed': '2003-04-08',\n",
       " 'usNationalStatusRankCode': 'N2'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Query based on a submitted SGCN scientific name\n",
    "scientificname = \"Hesperia dacotae\"\n",
    "\n",
    "q = \"SELECT to_json(natureserve) as natureserve 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\"][\"natureserve\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'GlobalStatusRank': 'G5',\n",
       " 'StateCode:Alaska': 'S1',\n",
       " 'StateCode:New York': 'S2',\n",
       " 'StateCode:Vermont': 'S1',\n",
       " 'cacheDate': '2017-05-07T18:17:08.592186',\n",
       " 'elementGlobalID': 'ELEMENT_GLOBAL.2.140209',\n",
       " 'globalStatusLastReviewed': '2015-08-04',\n",
       " 'roundedGlobalStatusRankDescription': 'Secure',\n",
       " 'usNationalStatusLastReviewed': 'Unknown',\n",
       " 'usNationalStatusRankCode': 'NNR'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Query based on a known NatureServe ID\n",
    "elementGlobalID = \"ELEMENT_GLOBAL.2.140209\"\n",
    "\n",
    "q = \"SELECT to_json(natureserve) as natureserve FROM tir.tir2 \\\n",
    "    WHERE natureserve->'elementGlobalID' = '\"+elementGlobalID+\"'\"\n",
    "\n",
    "r = requests.get(\"https://gc2.mapcentia.com/api/v1/sql/bcb?q=\"+q).json()\n",
    "\n",
    "display (r[\"features\"][0][\"properties\"][\"natureserve\"])"
   ]
  },
  {
   "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
}