06. Add shapefiles to db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The only shapefile you really need is the following one which defines the London bounday and the UK boundary.  I've included it in the repo.  You can add it to PostGIS as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SET\n",
      "SET\n",
      "BEGIN\n",
      "CREATE TABLE\n",
      "ALTER TABLE\n",
      "                     addgeometrycolumn                      \n",
      "------------------------------------------------------------\n",
      " tt.gb_and_london.geom SRID:27700 TYPE:MULTIPOLYGON DIMS:2 \n",
      "(1 row)\n",
      "\n",
      "INSERT 0 1\n",
      "INSERT 0 1\n",
      "INSERT 0 1\n",
      "CREATE INDEX\n",
      "COMMIT\n",
      "ANALYZE\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Shapefile type: Polygon\n",
      "Postgis type: MULTIPOLYGON[2]\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "shp2pgsql -I -s 27700 /Users/robinlinacre/Documents/python_projects/moj_national/shapefiles/gb_london_simplified_final.shp tt.gb_and_london | psql -d postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# The shapefile doesn't contain the right attributes - this adds them.\n",
    "from mylibrary.connections import  cursor, conn\n",
    "sql = \"\"\"\n",
    "delete from tt.gb_and_london where gid = 3;\n",
    "alter table tt.gb_and_london ADD name text;\n",
    "update  tt.gb_and_london\n",
    "set name = 'london' where gid = 1;\n",
    "update  tt.gb_and_london\n",
    "set name = 'gb' where gid = 2;\n",
    "\"\"\"\n",
    "cursor.execute(sql)\n",
    "conn.commit()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}