{
 "metadata": {
  "name": "vectorsdemo"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import ogr\n",
      "import netCDF4\n",
      "import psycopg2\n",
      "import simplekml\n",
      "import subprocess"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import matplotlib.pyplot as plt\n",
      "import pandas\n",
      "import pandas.io.sql"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uname = \"dbices\"\n",
      "pwd   = \"vectors\"\n",
      "host  = \"postgresx03.infra.xtr.deltares.nl\"\n",
      "dbname= \"ICES\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn.close()\n",
      "conn = psycopg2.connect(\"dbname=\"+dbname+\" host=\"+host+\" user=\"+uname+\" password=\"+pwd)\n",
      "cur = conn.cursor()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "strSql =\"\"\"\n",
      "select distinct on (statsq) st_x(the_geom),st_y(the_geom),statsq \n",
      "from unique_locations;\n",
      "\"\"\"\n",
      "cur.execute(strSql)\n",
      "p = cur.fetchall()\n",
      "\n",
      "df = pandas.io.sql.read_frame(strSql,conn)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kml = simplekml.Kml(open=1)\n",
      "fol = kml.newfolder(name=\"ICES Observations\")\n",
      "#label part of the KML\n",
      "for i, row in df.iterrows():\n",
      "    coords = [tuple(row[['st_x', 'st_y']])]\n",
      "    fol.newpoint(description=str(row[['statsq']].item(0)), coords=coords)\n",
      "a =kml.kml()\n",
      "fkml = r'd:\\temp\\icesobservations.kml'\n",
      "kml.save(fkml)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "1"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "strSql =\"\"\"\n",
      "select st_askml(the_geom),box2d(the_geom),statsq \n",
      "from icessquares;\n",
      "\"\"\"\n",
      "df = pandas.io.sql.read_frame(strSql,conn)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kml = simplekml.Kml(open=1)\n",
      "pfol = kml.newfolder(name=\"ICES Rectangles\")\n",
      "sharedstyle = simplekml.Style()\n",
      "sharedstyle.polystyle.color = '7d00ff00'  # Green\n",
      "sharedstyle.linestyle.color = '7d00ff00'  # Green\n",
      "#label part of the KML\n",
      "for i, row in df.iterrows():\n",
      "    netlink = pfol.newnetworklink(name=row['statsq'])\n",
      "    netlink.link.href = \"{}.kmz\".format(row[\"statsq\"])\n",
      "    netlink.link.viewrefreshmode = simplekml.ViewRefreshMode.onregion\n",
      "    ws, en = row['box2d'].replace('BOX(','').replace(')','').split(',')\n",
      "    w,s = ws.split()\n",
      "    e,n = en.split()\n",
      "    box = simplekml.LatLonBox(north=float(n),south=float(s),east=float(e),west=float(w))\n",
      "    lod = simplekml.Lod(minlodpixels=100,maxlodpixels=2000,minfadeextent=10, maxfadeextent=20)\n",
      "    region = simplekml.Region(box, lod)\n",
      "    netlink.region = region\n",
      "    pol = pfol.newpolygon()\n",
      "    pol.style = sharedstyle\n",
      "    coords = row['st_askml'].replace('<Polygon><outerBoundaryIs><LinearRing><coordinates>','').replace('</coordinates></LinearRing></outerBoundaryIs></Polygon>','').split(' ')\n",
      "    pol.outerboundaryis = ([(float(coords[j].split(',')[0]),float(coords[j].split(',')[1])) for j in range(len(coords))])\n",
      "    pol.description=row['statsq']\n",
      "    pol.region =region\n",
      "fkml = r'd:\\temp\\icessquares.kmz'\n",
      "kml.savekmz(fkml)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "strsql = \"\"\"\n",
      "select platform,station, st_astext(st_union(the_point)) as collect from\n",
      "ocean where statsq = %s\n",
      "group by platform, station\n",
      "\"\"\"\n",
      "statsq = '32F3'\n",
      "cur.execute(strsql, vars=[statsq])\n",
      "df2 = pandas.DataFrame(cur.fetchall())\n",
      "df2.columns = [rec[0] for rec in cur.description]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kml = simplekml.Kml(open=1)\n",
      "pfol = kml.newfolder(name=statsq)\n",
      "sharedstyle = simplekml.Style()\n",
      "sharedstyle.labelstyle.color = 'ad30ff00'  # Green\n",
      "sharedstyle.linestyle.color = 'ad30ff00'  # Green\n",
      "sharedstyle.iconstyle.icon.href = 'http://maps.google.com/mapfiles/kml/shapes/placemark_circle.png'\n",
      "#label part of the KML\n",
      "for i, row in df2.iterrows():\n",
      "    mp = pfol.newmultigeometry()\n",
      "    mp.style = sharedstyle\n",
      "    if row['collect'].startswith('POINT'):\n",
      "        lon, lat = row['collect'].replace('POINT(','').replace(')','').split()\n",
      "        mp.newpoint(coords=[(lon, lat)])\n",
      "    elif row['collect'].startswith('MULTIPOINT'):\n",
      "        mptext = row['collect'].replace('POINT(','').replace(')','')\n",
      "        coords = [point.split() for point in mptext.split(',')]\n",
      "        for coord in coords:\n",
      "            mp.newpoint(coords=[coord])\n",
      "    else:\n",
      "        raise ValueError(row['collect'])\n",
      "    \n",
      "fkml = r'd:\\temp\\{}.kmz'.format(statsq)\n",
      "kml.savekmz(fkml)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}