ctx_db_load.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "import numpy as np\n",
    "import pvl\n",
    "import ogr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Tested on a single images - this needs to be a list down below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "fname = '/scratch/Mars_Reconnaissance_Orbiter/CTX/level1/P06_003545_2022_XI_22N218W.cal.cub'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Funcs to grab metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_footprint(label):\n",
    "    seek = label['Polygon']['StartByte']\n",
    "    readlength = label['Polygon']['Bytes']\n",
    "\n",
    "    with open(fname, 'rb') as f:\n",
    "        f.seek(seek-1)\n",
    "        wkb = f.read(readlength)\n",
    "\n",
    "    return ogr.CreateGeometryFromWkt(wkb.decode('ascii'))\n",
    "\n",
    "def get_camstats(label):\n",
    "    for table in label.getlist('Table'):\n",
    "        if table['Name'] == 'CameraStatistics':\n",
    "            continue\n",
    "    startbyte = table['StartByte']\n",
    "    nrecords = table['Records']\n",
    "    dtype = np.dtype('S45,d,d,d,d')\n",
    "    with open(fname, 'rb') as f:\n",
    "        f.seek(startbyte - 1)\n",
    "        # Min, Max, Mean, STD\n",
    "        stats = np.fromfile(f, dtype=dtype, count=nrecords )\n",
    "    return stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column, String, Float, Integer\n",
    "from geoalchemy2 import Geometry\n",
    "from geoalchemy2.shape import to_shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a table mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Base = declarative_base()\n",
    "class CTXImage(Base):\n",
    "    __tablename__ = 'ctx'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    productid = Column(String)\n",
    "    orbitnumber = Column(Integer)\n",
    "    footprint = Column(Geometry('MULTIPOLYGON', srid=949900, dimension=3))\n",
    "    path = Column(String)\n",
    "    emission = Column(Float)\n",
    "    incidence = Column(Float)\n",
    "    lineresolution = Column(Float)\n",
    "    sampleresolution = Column(Float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Func to create an entry in the db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def image_to_entry(path):\n",
    "    \"\"\"\n",
    "    Takes an image path and creates a DB entry.\n",
    "    \"\"\"\n",
    "    label = pvl.load(path)\n",
    "    # Binary or packed metadata\n",
    "    footprint = get_footprint(label)\n",
    "    camstats = get_camstats(label)\n",
    "\n",
    "    # Standard metadata\n",
    "    archive = label['IsisCube']['Archive']\n",
    "    productid = archive['ProductId']\n",
    "    orbitnumber = archive['OrbitNumber']\n",
    "    path = fname\n",
    "    \n",
    "    entry = CTXImage(productid=productid,\n",
    "                     orbitnumber=orbitnumber,\n",
    "                     path=path,\n",
    "                     footprint=footprint,\n",
    "                     emission=camstats[10][3],\n",
    "                     incidence=camstats[11][3],\n",
    "                     lineresolution=camstats[3][3],\n",
    "                     sampleresolution=camstats[3][2])\n",
    "    \n",
    "    return entry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a database connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import create_session, scoped_session, sessionmaker\n",
    "\n",
    "def new_connection(config):\n",
    "    \"\"\"\n",
    "    Using the user supplied config create a NullPool database connection.\n",
    "    Returns\n",
    "    -------\n",
    "    Session : object\n",
    "              An SQLAlchemy session object\n",
    "    engine : object\n",
    "             An SQLAlchemy engine object\n",
    "    \"\"\"\n",
    "    db_uri = 'postgresql://{}:{}@{}:{}/{}'.format(config['database_username'],\n",
    "                                                  config['database_password'],\n",
    "                                                  config['database_host'],\n",
    "                                                  config['database_port'],\n",
    "                                                  config['database_name'])    \n",
    "    engine = sqlalchemy.create_engine(db_uri,\n",
    "                                      poolclass=sqlalchemy.pool.NullPool)\n",
    "    Session = sqlalchemy.orm.sessionmaker(bind=engine, autocommit=True)\n",
    "    return Session(), engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Setup the db connection and iterate over the list of images, pushing chunks\n",
    "# of 1000 images at a time to the DB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "config = {'database_username':'postgres',\n",
    "          'database_password':'idunnoyet',\n",
    "          'database_host':'host',\n",
    "          'database_port':1234,\n",
    "          'database_name':'ctxlevelone'}\n",
    "\n",
    "session, _ = new_connection(config)\n",
    "objs = []\n",
    "chunksize = 1000\n",
    "\n",
    "for i, path in enumerate(list_of_images):\n",
    "    objs.append(image_to_entry(path))\n",
    "    if i % chunksize == 0:\n",
    "        session.bulk_save_objects(objs)\n",
    "        objs = []\n",
    "session.bulk_save_objects(objs)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "autocnet",
   "language": "python",
   "name": "autocnet"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}