csv_to_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import sqlalchemy\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next few lines establish a connection to the database. The json credential file uses the following format:\n",
    "{\n",
    "    \"database\": \"*****\",\n",
    "     \"host\": \"*****\",\n",
    "     \"port\": 5432,\n",
    "     \"user\": \"*****\",\n",
    "     \"password\": \"*****\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open(\"/home/btphilip/notebooks/SQL_access.json\") as f:\n",
    "    db_conn_dict = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cred_string = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**db_conn_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dbeng = sqlalchemy.create_engine(cred_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "The next few lines read in a csv, selects a subset of the data, stores it as a new variable and prints the variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day.dec</th>\n",
       "      <th>latitude</th>\n",
       "      <th>N/S</th>\n",
       "      <th>longitude</th>\n",
       "      <th>E/W</th>\n",
       "      <th>GPSfix</th>\n",
       "      <th>NSats</th>\n",
       "      <th>HDOP</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>736382.673322</td>\n",
       "      <td>37.905610</td>\n",
       "      <td>N</td>\n",
       "      <td>107.707907</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3344.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>736382.673333</td>\n",
       "      <td>37.905610</td>\n",
       "      <td>N</td>\n",
       "      <td>107.707903</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3344.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>736382.673345</td>\n",
       "      <td>37.905609</td>\n",
       "      <td>N</td>\n",
       "      <td>107.707899</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3343.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>736382.673356</td>\n",
       "      <td>37.905608</td>\n",
       "      <td>N</td>\n",
       "      <td>107.707896</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3343.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>736382.673368</td>\n",
       "      <td>37.905607</td>\n",
       "      <td>N</td>\n",
       "      <td>107.707892</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3342.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         day.dec   latitude N/S   longitude E/W  GPSfix  NSats  HDOP  \\\n",
       "0  736382.673322  37.905610   N  107.707907   W       1      7  3.33   \n",
       "1  736382.673333  37.905610   N  107.707903   W       1      7  3.33   \n",
       "2  736382.673345  37.905609   N  107.707899   W       1      7  3.33   \n",
       "3  736382.673356  37.905608   N  107.707896   W       1      7  3.33   \n",
       "4  736382.673368  37.905607   N  107.707892   W       1      7  3.33   \n",
       "\n",
       "   elevation  \n",
       "0     3344.7  \n",
       "1     3344.1  \n",
       "2     3343.6  \n",
       "3     3343.3  \n",
       "4     3342.7  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('/home/btphilip/snowex/SBBgps20170222A.csv')\n",
    "temp_df = df[0:99]\n",
    "temp_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following line writes the temp_df variable to a database table specified by the db_table_name on the geohack server (stored here as dbeng). This server is hosted on AWS for the duration of Geohackweek 2017 and was created using the AWS RDS service. I've been using the dBeaver GUI to query and edit the database.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda/envs/vectorenv/lib/python3.6/site-packages/pandas/core/generic.py:3110: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[name] = value\n"
     ]
    }
   ],
   "source": [
    "db_table_name = 'philip_ice_2'\n",
    "temp_df.longitude= temp_df['longitude'].copy()*-1\n",
    "temp_df.head(3)\n",
    "temp_df.to_sql(db_table_name, dbeng)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now create a new geometry column within the database table to fill with GeoDataFrame values. Converting to geodataframe will allow for additional SQL queries (e.g. nearest values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x7f13e2623320>"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dbeng.execute(\"\"\"ALTER TABLE %s ADD COLUMN geom geometry(Point, 32612);\"\"\" %(db_table_name)) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now populate the geometry field using ST_transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x7f13b6abbba8>"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dbeng.execute(\"\"\"UPDATE %s SET geom = ST_Transform(ST_setSRID(ST_MakePoint(longitude,latitude),4326),32612);\"\"\" %(db_table_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python(vectorenv)",
   "language": "python",
   "name": "vectorenv"
  },
  "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}