{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting parts of the database to portable formats\n",
    "\n",
    "Users may not always want, or have the means, to setup a fully fledged PostGIS enabled server. For the sake of facilitating reproducibility, geoslurp allows exporting parts of the database to portable file-based formats such as [geopackage](http://www.geopackage.org/). This functionality, available since version 1.1.0, can be used according to the python examples below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "from geoslurp.config import setInfoLevel\n",
    "from geoslurp.db import geoslurpConnect\n",
    "setInfoLevel()\n",
    "\n",
    "gpcon=geoslurpConnect() # this will be a connection based on the readonly user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting a subset of the PSMSL table\n",
    "Here we export a query which extracts a subset (i.e. only stations within a certain polygon) of the tide gauge data from the permanent service of mean sea level (PSMSL). The resulting geopackage contains 2 layers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from geoslurp.db.exporter import exportGeoQuery\n",
    "from geoslurp.discover.oceanobs.psmsl import psmslQuery\n",
    "\n",
    "\n",
    "import os\n",
    "wkt=\"Polygon ((-0.91442683789107093 57.69161380668427341, 6.80867139814095879 60.46400804525987382, 10.76923459610610223 51.94879716963480121, -3.48879291656842838 50.95865637014351535, -0.91442683789107093 57.69161380668427341))\"\n",
    "geopout=\"/tmp/PSMSL_subset.gpkg\"\n",
    "try:\n",
    "    os.remove(geopout)\n",
    "except FileNotFoundError:\n",
    "    pass\n",
    "\n",
    "#export to queries as layers\n",
    "exportGeoQuery(psmslQuery(gpcon,\"psmsl_rlr_monthly\",wkt),geopout,layer=\"tgm\")\n",
    "exportGeoQuery(psmslQuery(gpcon,\"psmsl_rlr_annual\",wkt),geopout,layer=\"tga\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting an Argo query and the profile files in the South China Sea for the first 2 weeks of December 2010\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from geoslurp.discover.oceanobs.argoQuery import argoQuery\n",
    "from datetime import datetime\n",
    "\n",
    "southchinaDeepWKT=\"Polygon ((111.16091417910448058 13.48740671641791522, 113.98180970149255131 18.24766791044776326, 117.59608208955224029 20.3633395522388092, 119.97621268656716609 21.50932835820895761, 119.62360074626866435 16.92537313432836044, 119.88805970149255131 13.75186567164179507, 119.53544776119403537 12.6940298507462721, 116.36194029850747711 11.45988805970149826, 112.8358208955223887 10.40205223880597174, 110.54384328358209189 10.22574626865672087, 111.16091417910448058 13.48740671641791522))\"\n",
    "\n",
    "tspan=[datetime(2010,12,1),datetime(2010,12,15)]\n",
    "\n",
    "exportGeoQuery(argoQuery(gpcon,geoWKT=southchinaDeepWKT,tspan=tspan)\n",
    "               ,\"argoSouthChinaDec2010.gpkg\",layer='argo',packFiles=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting a selection of GRACE datasets and auxiliary datasets (without geographical information) to a local sqlite database.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "from geoslurp.db.exporter import exportQuery\n",
    "from geoslurp.discover.generic import regexQuery\n",
    "outdb=\"itsg2018_sample.db\"\n",
    "try:\n",
    "    os.remove(outdb)\n",
    "except FileNotFoundError:\n",
    "    pass\n",
    "\n",
    "#export GRACE monthly solutions\n",
    "exportQuery(regexQuery(gpcon,\"itsg_grace2018_monthly_n60\",scheme=\"gravity\",uri='n60_2003')\n",
    "            ,\"itsg2018_sample.db\",layer=\"grace\",packFiles=True)\n",
    "\n",
    "#export modelled ocean bottom pressure for restoring\n",
    "exportQuery(regexQuery(gpcon,\"itsg_grace2018_monthly_background\",scheme=\"gravity\",uri='oceanBottomPressure.+2003')\n",
    "            ,\"itsg2018_sample.db\",layer=\"obp\",packFiles=True)\n",
    "\n",
    "#export a  static gravity model\n",
    "exportQuery(regexQuery(gpcon,\"icgem_static\",scheme=\"gravity\",uri='GOCO06s')\n",
    "            ,\"itsg2018_sample.db\",layer=\"static\",packFiles=True)\n",
    "\n",
    "#export degree 1 corrections\n",
    "exportQuery(regexQuery(gpcon,\"geocenter_csrrl06_tn13\",scheme=\"gravity\",tspan=[datetime(2003,1,1),datetime(2003,12,31)])\n",
    "            ,\"itsg2018_sample.db\",layer=\"deg1\")\n",
    "\n",
    "#also export load Love numbers\n",
    "exportQuery(regexQuery(gpcon,\"loadlove\",scheme=\"earthmodels\",name='PREM$'),\"itsg2018_sample.db\",layer=\"loadlove\",packFiles=True)\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pyrr",
   "language": "python",
   "name": "pyrr"
  },
  "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}