{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query Coordianted Canyon Experiment database for BED information\n",
    "*Connect to a remote database, select specific data using Django queries*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Executing this Notebook requires a personal STOQS server. Follow the [steps to build your own development system](https://github.com/stoqs/stoqs/blob/master/README.md) — this will take a few hours and depends on a good connection to the Internet.  Once your server is up log into it (after a `cd ~/Vagrants/stoqsvm`) and activate your virtual environment with the usual commands:\n",
    "\n",
    "    vagrant ssh -- -X\n",
    "    cd /vagrant/dev/stoqsgit\n",
    "    source venv-stoqs/bin/activate\n",
    "    \n",
    "Connect to your Institution's STOQS database server using read-only credentials. (Note: firewalls typically limit unprivileged access to such resources.)\n",
    "\n",
    "    cd stoqs\n",
    "    ln -s mbari_campaigns.py campaigns.py\n",
    "    export DATABASE_URL=postgis://everyone:guest@kraken.shore.mbari.org:5433/stoqs\n",
    "    \n",
    "Launch Jupyter Notebook on your system with:\n",
    "\n",
    "    cd contrib/notebooks\n",
    "    ../../manage.py shell_plus --notebook\n",
    "    \n",
    "navigate to this file and open it. You will then be able to execute the cells and experiment with this notebook.\n",
    "\n",
    "For reference please see the [STOQS schema diagram](https://raw.githubusercontent.com/stoqs/stoqs/master/doc/stoqs_model.png).\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get list of Activities that are BED event trajectories:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "acts = (Activity.objects.using('stoqs_cce2015')\n",
    "        .filter(name__contains='trajectory')\n",
    "        .order_by('name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each BED event (activity) print start and end times and locations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30200078_trajectory.nc (stride=1)\n",
      "\tStart: 2016-01-15 21:35:52, -121.822032, 36.796560, 201.15\n",
      "\tEnd  : 2016-01-15 21:37:38.459898, -121.822032, 36.796560, 120.12\n",
      "30300004_trajectory.nc (stride=1)\n",
      "\tStart: 2016-02-17 21:53:58, -121.848407, 36.793873, 293.60\n",
      "\tEnd  : 2016-02-17 21:56:20.400034, -121.851421, 36.795098, 303.07\n",
      "30300016_trajectory.nc (stride=1)\n",
      "\tStart: 2016-03-06 09:23:36, -121.851242, 36.795019, 304.66\n",
      "\tEnd  : 2016-03-06 09:28:04.000064, -121.856867, 36.800122, 327.87\n",
      "40100037_trajectory.nc (stride=1)\n",
      "\tStart: 2015-12-01 15:37:45.500000, -121.847281, 36.793254, 291.40\n",
      "\tEnd  : 2015-12-01 15:42:04.899753, -121.854368, 36.796832, 295.52\n",
      "50200024_decimated_trajectory.nc (stride=1)\n",
      "\tStart: 2015-12-01 15:50:14, -121.870132, 36.794753, 386.79\n",
      "\tEnd  : 2015-12-01 15:55:14.000072, -121.877751, 36.792767, 404.18\n",
      "50200054_trajectory.nc (stride=1)\n",
      "\tStart: 2016-01-15 21:53:11, -121.878356, 36.792502, 406.80\n",
      "\tEnd  : 2016-01-15 21:55:34.400034, -121.880776, 36.792806, 414.09\n",
      "50200055_trajectory.nc (stride=1)\n",
      "\tStart: 2016-01-15 21:55:37, -121.880802, 36.792830, 414.79\n",
      "\tEnd  : 2016-01-15 21:59:53.800061, -121.883745, 36.796028, 424.66\n",
      "50200056_trajectory.nc (stride=1)\n",
      "\tStart: 2016-01-15 22:00:00, -121.883129, 36.795570, 424.68\n",
      "\tEnd  : 2016-01-15 22:01:10.400017, -121.884910, 36.796855, 428.44\n",
      "50200057_trajectory.nc (stride=1)\n",
      "\tStart: 2016-01-15 22:01:49, -121.885470, 36.797185, 426.72\n",
      "\tEnd  : 2016-01-15 22:02:36.000011, -121.887633, 36.797514, 429.29\n",
      "60100068_trajectory.nc (stride=1)\n",
      "\tStart: 2016-01-15 22:06:15, -121.904350, 36.791129, 521.07\n",
      "\tEnd  : 2016-01-15 22:07:11.339946, -121.904350, 36.791129, 491.45\n"
     ]
    }
   ],
   "source": [
    "fmt = '\\t{}: {}, {:.6f}, {:.6f}, {:.2f}'\n",
    "for activity in acts:\n",
    "    measuredparameters = (MeasuredParameter.objects.using('stoqs_cce2015')\n",
    "                           .filter(measurement__instantpoint__activity=activity)\n",
    "                           .order_by('measurement__instantpoint__timevalue'))\n",
    "    start = measuredparameters.earliest('measurement__instantpoint__timevalue')\n",
    "    end = measuredparameters.latest('measurement__instantpoint__timevalue')\n",
    "    \n",
    "    print('{}'.format(activity))\n",
    "    print(fmt.format('Start',\n",
    "                     start.measurement.instantpoint, \n",
    "                     start.measurement.geom.x, \n",
    "                     start.measurement.geom.y, \n",
    "                     start.measurement.depth))\n",
    "    print(fmt.format('End  ',\n",
    "                     end.measurement.instantpoint, \n",
    "                     end.measurement.geom.x, \n",
    "                     end.measurement.geom.y, \n",
    "                     end.measurement.depth))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Validate the query by a spot check of one of the NetCDF files for the start and end data. Let's choose the penultimate one: `50200057_trajectory.nc (stride=1)`. The OPeNDAP URL for the NetCDF file is http://elvis64.shore.mbari.org/opendap/data/CCE_Processed/BEDs/BED05/MBCCE_BED05_20151027_Event20160115/netcdf/50200057_trajectory.nc.html. From this form we can select the coordinate variables and choose the first and last indices to construct .ascii requests:\n",
    "\n",
    "First:\n",
    "```\n",
    "http://elvis64.shore.mbari.org:8080/opendap/data/CCE_Processed/BEDs/BED05/MBCCE_BED05_20151027_Event20160115/netcdf/50200057_trajectory.nc.ascii?time[0:1:0],latitude[0:1:0],longitude[0:1:0],depth[0:1:0]\n",
    "\n",
    "Dataset: 50200057_trajectory.nc\n",
    "time, 95896909\n",
    "latitude.time, 95896909\n",
    "latitude.latitude, 36.797185\n",
    "longitude.time, 95896909\n",
    "longitude.longitude, -121.88547\n",
    "depth.time, 95896909\n",
    "depth.depth, 426.721524445596\n",
    "```\n",
    "Last:\n",
    "```\n",
    "http://elvis64.shore.mbari.org:8080/opendap/data/CCE_Processed/BEDs/BED05/MBCCE_BED05_20151027_Event20160115/netcdf/50200057_trajectory.nc.ascii?time[235:1:235],latitude[235:1:235],longitude[235:1:235],depth[235:1:235]\n",
    "\n",
    "Dataset: 50200057_trajectory.nc\n",
    "time, 95896956.0000112\n",
    "latitude.time, 95896956.0000112\n",
    "latitude.latitude, 36.797514\n",
    "longitude.time, 95896956.0000112\n",
    "longitude.longitude, -121.887633\n",
    "depth.time, 95896956.0000112\n",
    "depth.depth, 429.287069222699\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The time values can be converted from 'seconds since 2013-01-01 00:00:00' to a string that we can compare to report from STOQS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2016-01-15 22:01:49\n",
      "2016-01-15 22:02:36.000011\n"
     ]
    }
   ],
   "source": [
    "from coards import from_udunits\n",
    "print(str(from_udunits(95896909, 'seconds since 2013-01-01 00:00:00')))\n",
    "print(str(from_udunits(95896956.0000112, 'seconds since 2013-01-01 00:00:00')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compare side-by-side (er, top-and-bottom) the data from STOQS and NetCDF:\n",
    "\n",
    "```\n",
    "50200057_trajectory.nc (stride=1)\n",
    "\tSTOQS  Start: 2016-01-15 22:01:49,        -121.885470, 36.797185, 426.72\n",
    "    NetCDF start: 2016-01-15 22:01:49,        -121.88547,  36.797185, 426.721524445596\n",
    "    \n",
    "\tSTOQS  End  : 2016-01-15 22:02:36.000011, -121.887633, 36.797514, 429.29\n",
    "    NetCDF End  : 2016-01-15 22:02:36.000011, -121.887633, 36.797514, 429.287069222699\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Comparison looks good!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}