Brooklyn Buses.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gp\n",
    "from datetime import datetime\n",
    "import json\n",
    "\n",
    "import shapely\n",
    "from fiona.crs import from_epsg\n",
    "import psycopg2\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Source of data - https://www.baruch.cuny.edu/confluence/pages/viewpage.action?pageId=28016896\n",
    "def download_data():\n",
    "    os.system(\"curl -O http://faculty.baruch.cuny.edu/geoportal/data/nyc_transit/may2019/bus_routes_nyc_may2019.zip\")\n",
    "    os.system(\"mkdir Data\")\n",
    "    os.system(\"unzip bus_routes_nyc_may2019.zip -d ./Data/bus_routes_nyc_may2019/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## https://gis.stackexchange.com/questions/309251/how-to-get-equidistant-points-from-a-linestring-geographical-coordinates\n",
    "\n",
    "def create_geo(busline, tpoints ,time_index):\n",
    "    '''\n",
    "    Doc: busline: row indicating busline, should contain geometry\n",
    "          time_index = pd.date_range obj with required cadance\n",
    "    '''\n",
    "    busLineFinal = []\n",
    "    totalIdx = len(time_index)\n",
    "    lastIdx = 0\n",
    "    flag = True\n",
    "    \n",
    "    \n",
    "    route_dir = busline['route_dir']\n",
    "    route_id = busline['route_id']\n",
    "    \n",
    "    #Clip line into 250 unique points\n",
    "    num_points = tpoints\n",
    "    points = [busline['geometry'].interpolate(i/float(num_points - 1),\n",
    "                                                                 normalized=True)\n",
    "                                                     for i in range(num_points)] \n",
    "\n",
    "    busline_geom = gp.GeoSeries(points)\n",
    "    while flag:\n",
    "        \n",
    "        busLine = gp.GeoDataFrame(geometry=busline_geom)\n",
    "        busLine['route_name'] = route_id\n",
    "        busLine['route_dir'] = route_dir\n",
    "        busLine['time'] = np.NaN  \n",
    "        \n",
    "        for idx in busLine.index:\n",
    "            busLine.loc[idx, 'time'] = time_index[lastIdx]\n",
    "            lastIdx = lastIdx + 1\n",
    "   \n",
    "            if lastIdx == totalIdx:\n",
    "                flag = False\n",
    "                break\n",
    "                \n",
    "        busLineFinal.append(busLine)\n",
    "    \n",
    "    return (pd.concat(busLineFinal).dropna().reset_index(drop=True)).to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download Bus route shape file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "download_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a dataframe by splitting Brooklyn Bus routes in 250 points and for starting 121 points add timestamps of every thirty secs between 10AM to 11AM on 14th Aug 2019 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "gdf = gp.read_file('./Data/bus_routes_nyc_may2019/bus_routes_nyc_may2019.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "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>color</th>\n",
       "      <th>dir_id</th>\n",
       "      <th>geometry</th>\n",
       "      <th>route_dir</th>\n",
       "      <th>route_id</th>\n",
       "      <th>route_long</th>\n",
       "      <th>route_shor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>#B933AD</td>\n",
       "      <td>0</td>\n",
       "      <td>(LINESTRING (996119.7547271665 160954.75802325...</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>B100</td>\n",
       "      <td>Mill Basin - Midwood</td>\n",
       "      <td>B100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>#B933AD</td>\n",
       "      <td>1</td>\n",
       "      <td>(LINESTRING (1001037.784615552 160426.83236815...</td>\n",
       "      <td>B100_1</td>\n",
       "      <td>B100</td>\n",
       "      <td>Mill Basin - Midwood</td>\n",
       "      <td>B100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     color  dir_id                                           geometry  \\\n",
       "0  #B933AD       0  (LINESTRING (996119.7547271665 160954.75802325...   \n",
       "1  #B933AD       1  (LINESTRING (1001037.784615552 160426.83236815...   \n",
       "\n",
       "  route_dir route_id            route_long route_shor  \n",
       "0    B100_0     B100  Mill Basin - Midwood       B100  \n",
       "1    B100_1     B100  Mill Basin - Midwood       B100  "
      ]
     },
     "execution_count": 308,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(505, 7)"
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(115, 7)"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.loc[(gdf.route_id.str.startswith(\"B\")) & \n",
    "        (~gdf.route_id.str.contains(\"BX\"))].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "points = 250\n",
    "brooklyn_lines = gdf.loc[(gdf.route_id.str.startswith(\"B\")) & \n",
    "                         (~gdf.route_id.str.contains(\"BX\"))].copy()\n",
    "bkPoints = brooklyn_lines.apply(lambda x: create_geo(x, \n",
    "                                                     points, \n",
    "                                                     pd.date_range('10:00', '11:00', freq='30s')), \n",
    "                                axis=1)\n",
    "\n",
    "dataframer = lambda x: pd.DataFrame(x)\n",
    "brooklynPoints_df = list(map(dataframer, bkPoints.tolist()));\n",
    "\n",
    "brooklynPoints = pd.concat(brooklynPoints_df, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13915, 4)"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brooklynPoints.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "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>geometry</th>\n",
       "      <th>route_dir</th>\n",
       "      <th>route_name</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POINT (996119.7547271665 160954.7580232588)</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>B100</td>\n",
       "      <td>2019-08-14 10:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POINT (996192.2380531991 160944.5904073866)</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>B100</td>\n",
       "      <td>2019-08-14 10:00:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POINT (996281.488913448 160957.3293115214)</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>B100</td>\n",
       "      <td>2019-08-14 10:01:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      geometry route_dir route_name  \\\n",
       "0  POINT (996119.7547271665 160954.7580232588)    B100_0       B100   \n",
       "1  POINT (996192.2380531991 160944.5904073866)    B100_0       B100   \n",
       "2   POINT (996281.488913448 160957.3293115214)    B100_0       B100   \n",
       "\n",
       "                 time  \n",
       "0 2019-08-14 10:00:00  \n",
       "1 2019-08-14 10:00:30  \n",
       "2 2019-08-14 10:01:00  "
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brooklynPoints.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "brooklynPoints = gp.GeoDataFrame(brooklynPoints, geometry='geometry')\n",
    "brooklynPoints.crs = from_epsg(2263)\n",
    "brooklynPoints.to_crs(epsg=4326, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "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>geometry</th>\n",
       "      <th>route_dir</th>\n",
       "      <th>route_name</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POINT (-73.957251 40.60845199999983)</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>B100</td>\n",
       "      <td>2019-08-14 10:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POINT (-73.95698996884438 40.60842399459272)</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>B100</td>\n",
       "      <td>2019-08-14 10:00:30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       geometry route_dir route_name  \\\n",
       "0          POINT (-73.957251 40.60845199999983)    B100_0       B100   \n",
       "1  POINT (-73.95698996884438 40.60842399459272)    B100_0       B100   \n",
       "\n",
       "                 time  \n",
       "0 2019-08-14 10:00:00  \n",
       "1 2019-08-14 10:00:30  "
      ]
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brooklynPoints.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "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>time</th>\n",
       "      <th>route_name</th>\n",
       "      <th>route_dir</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08-14 10:00:00</td>\n",
       "      <td>B100</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>-73.957251</td>\n",
       "      <td>40.608452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-14 10:00:30</td>\n",
       "      <td>B100</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>-73.956990</td>\n",
       "      <td>40.608424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08-14 10:01:00</td>\n",
       "      <td>B100</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>-73.956669</td>\n",
       "      <td>40.608459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08-14 10:01:30</td>\n",
       "      <td>B100</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>-73.956347</td>\n",
       "      <td>40.608494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08-14 10:02:00</td>\n",
       "      <td>B100</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>-73.956026</td>\n",
       "      <td>40.608529</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 time route_name route_dir        lon        lat\n",
       "0 2019-08-14 10:00:00       B100    B100_0 -73.957251  40.608452\n",
       "1 2019-08-14 10:00:30       B100    B100_0 -73.956990  40.608424\n",
       "2 2019-08-14 10:01:00       B100    B100_0 -73.956669  40.608459\n",
       "3 2019-08-14 10:01:30       B100    B100_0 -73.956347  40.608494\n",
       "4 2019-08-14 10:02:00       B100    B100_0 -73.956026  40.608529"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brooklynBuses = brooklynPoints[['time','route_name', 'route_dir', 'geometry']].copy()\n",
    "brooklynBuses.loc[:, 'lon'] = brooklynPoints.geometry.apply(lambda x: x.x)\n",
    "brooklynBuses.loc[:, 'lat'] = brooklynPoints.geometry.apply(lambda x: x.y)\n",
    "brooklynBuses.drop('geometry', axis=1, inplace=True)\n",
    "brooklynBuses.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "POPULATE bus.device_test table with DUMMY DATA\n",
    "\n",
    "\n",
    "Insert into the following table using attached shapefile (points along major Brooklyn Bus routes) where device_id = route_id\n",
    "you will need to create datetime such that each bus route starts at the same time and each point along the time represents an increment of 30 seconds.\n",
    "Copy this dummy data to cover all \"times of day\" so that we can load the dummy data into the interface\n",
    "insert into bus.device (datetime, device_id, lat, lon) VALUES (%s, %s, %s, %s);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "metadata": {},
   "outputs": [],
   "source": [
    "keys = json.load(open(\"./credentials.json\"))\n",
    "\n",
    "host = keys['postgres'][0]['host']\n",
    "database = keys['postgres'][0]['database']\n",
    "user = keys['postgres'][0]['user']\n",
    "password = keys['postgres'][0]['password']\n",
    "\n",
    "con = psycopg2.connect(host=host, database=database, user=user, password=password)\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = 'insert into bus.device_test (datetime, device_id, lat, lon, route_id) VALUES (%s, %s, %s, %s, %s);'\n",
    "for idx, row in brooklynBuses.iterrows():\n",
    "    cur.execute(query,\n",
    "                (datetime.strptime(str(row['time']), '%Y-%m-%d %H:%M:%S'), \n",
    "                 row['route_dir'],\n",
    "                 row['lat'], row['lon'],\n",
    "                 row['route_dir']))\n",
    "con.commit()\n",
    "# con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \"SELECT * FROM bus.device_test LIMIT 10;\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "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>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>device_id</th>\n",
       "      <th>datetime</th>\n",
       "      <th>geom</th>\n",
       "      <th>route_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-73.957251</td>\n",
       "      <td>40.60845199999983</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:00:00</td>\n",
       "      <td>0101000020E61000000DC4B299437D52C0B09750C1E14D...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-73.95698996884438</td>\n",
       "      <td>40.60842399459272</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:00:30</td>\n",
       "      <td>0101000020E610000059B2DA523F7D52C04F7063D6E04D...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-73.94863443804769</td>\n",
       "      <td>40.6093416264626</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:13:30</td>\n",
       "      <td>0101000020E61000008CD1376DB67C52C0C9DF0AE8FE4D...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-73.94831299573353</td>\n",
       "      <td>40.609376584471605</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:14:00</td>\n",
       "      <td>0101000020E610000007C3FD28B17C52C07AA04A0D004E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-73.94799141308184</td>\n",
       "      <td>40.609410790695655</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:14:30</td>\n",
       "      <td>0101000020E6100000D2042DE4AB7C52C005EF3B2C014E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-73.94766986621181</td>\n",
       "      <td>40.609445193122426</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:15:00</td>\n",
       "      <td>0101000020E610000034B2829FA67C52C02695D24C024E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-73.94734818765</td>\n",
       "      <td>40.609478852265255</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:15:30</td>\n",
       "      <td>0101000020E61000006CF84A5AA17C52C09C0A2D67034E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-73.94702616914623</td>\n",
       "      <td>40.6095105911042</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:16:00</td>\n",
       "      <td>0101000020E61000001C3CA6139C7C52C0D8AD6B71044E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-73.94670415033629</td>\n",
       "      <td>40.60954232904085</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:16:30</td>\n",
       "      <td>0101000020E6100000A32B01CD967C52C00A61A87B054E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-73.94638297417148</td>\n",
       "      <td>40.609578397504684</td>\n",
       "      <td>B100_0</td>\n",
       "      <td>2019-08-14 10:17:00</td>\n",
       "      <td>0101000020E6100000B2E3E489917C52C0BBD038AA064E...</td>\n",
       "      <td>B100_0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  lon                 lat device_id            datetime  \\\n",
       "0          -73.957251   40.60845199999983    B100_0 2019-08-14 10:00:00   \n",
       "1  -73.95698996884438   40.60842399459272    B100_0 2019-08-14 10:00:30   \n",
       "2  -73.94863443804769    40.6093416264626    B100_0 2019-08-14 10:13:30   \n",
       "3  -73.94831299573353  40.609376584471605    B100_0 2019-08-14 10:14:00   \n",
       "4  -73.94799141308184  40.609410790695655    B100_0 2019-08-14 10:14:30   \n",
       "5  -73.94766986621181  40.609445193122426    B100_0 2019-08-14 10:15:00   \n",
       "6     -73.94734818765  40.609478852265255    B100_0 2019-08-14 10:15:30   \n",
       "7  -73.94702616914623    40.6095105911042    B100_0 2019-08-14 10:16:00   \n",
       "8  -73.94670415033629   40.60954232904085    B100_0 2019-08-14 10:16:30   \n",
       "9  -73.94638297417148  40.609578397504684    B100_0 2019-08-14 10:17:00   \n",
       "\n",
       "                                                geom route_id  \n",
       "0  0101000020E61000000DC4B299437D52C0B09750C1E14D...   B100_0  \n",
       "1  0101000020E610000059B2DA523F7D52C04F7063D6E04D...   B100_0  \n",
       "2  0101000020E61000008CD1376DB67C52C0C9DF0AE8FE4D...   B100_0  \n",
       "3  0101000020E610000007C3FD28B17C52C07AA04A0D004E...   B100_0  \n",
       "4  0101000020E6100000D2042DE4AB7C52C005EF3B2C014E...   B100_0  \n",
       "5  0101000020E610000034B2829FA67C52C02695D24C024E...   B100_0  \n",
       "6  0101000020E61000006CF84A5AA17C52C09C0A2D67034E...   B100_0  \n",
       "7  0101000020E61000001C3CA6139C7C52C0D8AD6B71044E...   B100_0  \n",
       "8  0101000020E6100000A32B01CD967C52C00A61A87B054E...   B100_0  \n",
       "9  0101000020E6100000B2E3E489917C52C0BBD038AA064E...   B100_0  "
      ]
     },
     "execution_count": 329,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(sql=query, con=con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}