In [1]:
import os,sys,inspect
currentdir = os.path.dirname(os.path.abspath(inspect.getfile(inspect.currentframe())))
parentdir = os.path.dirname(currentdir)
sys.path.insert(0,parentdir) 
import loadOSCdata
import pandas as pd
import numpy as np
import geopandas as gpd
import matplotlib.pyplot as plt

import psycopg2
import osgeo.ogr
import shapely
import shapely.wkt
%matplotlib inline

In [2]:
data = loadOSCdata.downloadData(OSCid=401441, X = True, Y = True, Z = True, output = 'csv', outputFile = 'data.csv')
data.head()


Out[2]:
timestamp point_id v_value geometry image_url image_lab trip_id
359 2017-05-30 10:27:18.191175 0.0 10.769757 POINT (-73.974177 40.6807485) http://storage4.openstreetcam.org/files/photo/... acceptable 401441
464 2017-05-30 10:27:22.122909 1.0 10.747140 POINT (-73.97433599999999 40.68077) http://storage4.openstreetcam.org/files/photo/... acceptable 401441
548 2017-05-30 10:27:25.199758 2.0 4.727365 POINT (-73.97447149999999 40.6807915) http://storage4.openstreetcam.org/files/photo/... acceptable 401441
630 2017-05-30 10:27:28.167336 3.0 2.349433 POINT (-73.97454999999999 40.6807985) http://storage4.openstreetcam.org/files/photo/... bad 401441
741 2017-05-30 10:27:32.227059 4.0 6.292053 POINT (-73.974553 40.680786) http://storage4.openstreetcam.org/files/photo/... good 401441

In [3]:
connect_str = "dbname='squidbike' user='squidbike' \
host='rds-postgresql-10mintutorial.cbz1xmmdmpva.us-east-2.rds.amazonaws.com' \
port='5432' password='squidbikesql'"
conn = psycopg2.connect(connect_str)

In [4]:
cursor = conn.cursor()
cursor.execute("SELECT * from accelerometer2")
rows = cursor.fetchall()

In [ ]:
'''
link to aws PostGIS installation:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS

'''

In [6]:
cursor.execute("create extension postgis")

In [7]:
#cursor.execute("DROP TABLE IF EXISTS bike_trip_testing")
cursor.execute("CREATE TABLE bike_trip_testingFelipe \
               (id SERIAL PRIMARY KEY,\
               timestamp Text,\
               point_id numeric(10,5),\
               v_value numeric(10,5),\
               geometry Geometry,\
               image_url Text,\
               image_lab Text,\
               trip_id Text\
               )")

In [8]:
cursor.execute("CREATE INDEX postgisIndex ON bike_trip_testingFelipe USING GIST(Geometry)")

In [10]:
data.head()


Out[10]:
timestamp point_id v_value geometry image_url image_lab trip_id
359 2017-05-30 10:27:18.191175 0.0 10.769757 POINT (-73.974177 40.6807485) http://storage4.openstreetcam.org/files/photo/... acceptable 401441
464 2017-05-30 10:27:22.122909 1.0 10.747140 POINT (-73.97433599999999 40.68077) http://storage4.openstreetcam.org/files/photo/... acceptable 401441
548 2017-05-30 10:27:25.199758 2.0 4.727365 POINT (-73.97447149999999 40.6807915) http://storage4.openstreetcam.org/files/photo/... acceptable 401441
630 2017-05-30 10:27:28.167336 3.0 2.349433 POINT (-73.97454999999999 40.6807985) http://storage4.openstreetcam.org/files/photo/... bad 401441
741 2017-05-30 10:27:32.227059 4.0 6.292053 POINT (-73.974553 40.680786) http://storage4.openstreetcam.org/files/photo/... good 401441

In [13]:
index=0
timestamp = data.timestamp.iloc[index]

In [15]:
#Saving records in the table
for index in range(len(data)):
    timestamp = data.timestamp.iloc[index]
    
    point_id = data.point_id.iloc[index]
    v_value = data.v_value.iloc[index]
    geometry = data.geometry.iloc[index]
    image_url = data.image_url.iloc[index]
    image_lab = data.image_lab.iloc[index]
    trip_id = data.trip_id.iloc[index]
    cursor.execute("INSERT INTO bike_trip_testingFelipe (timestamp, point_id, v_value, geometry, image_url, image_lab,\
    trip_id) \
    VALUES ('%s', '%d', '%d', ST_GeomFromText('%s', 4326), '%s',\
    '%s', '%s')"\
    %(timestamp, point_id, v_value, geometry, image_url, \
    image_lab, trip_id))
#     if index == 1000:
#         break
conn.commit()

In [18]:
#v_value should be float and for some reason it looses the data after the decimal point

In [17]:
cursor.execute("SELECT * from bike_trip_testingFelipe")
rows = cursor.fetchall()
rows


Out[17]:
[(1,
  '2017-05-30 10:27:18.191175',
  Decimal('0.00000'),
  Decimal('10.00000'),
  '0101000020E6100000FCE07CEA587E52C0892650C422574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_3b530_592d8a896b182.jpg',
  'acceptable',
  '401441'),
 (2,
  '2017-05-30 10:27:22.122909',
  Decimal('1.00000'),
  Decimal('10.00000'),
  '0101000020E610000030D461855B7E52C0890CAB7823574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_a9b9c_592d8a89f1f27.jpg',
  'acceptable',
  '401441'),
 (3,
  '2017-05-30 10:27:25.199758',
  Decimal('2.00000'),
  Decimal('4.00000'),
  '0101000020E610000094D8B5BD5D7E52C088F2052D24574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_ba53c_592d8a8a891cd.jpg',
  'acceptable',
  '401441'),
 (4,
  '2017-05-30 10:27:28.167336',
  Decimal('3.00000'),
  Decimal('2.00000'),
  '0101000020E61000004694F6065F7E52C03B55BE6724574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_94bd6_592d8a8b1f391.jpg',
  'bad',
  '401441'),
 (5,
  '2017-05-30 10:27:32.227059',
  Decimal('4.00000'),
  Decimal('6.00000'),
  '0101000020E6100000FFCD8B135F7E52C08EC9E2FE23574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_e0a45_592d8a8bd66bd.jpg',
  'good',
  '401441'),
 (6,
  '2017-05-30 10:27:37.197610',
  Decimal('5.00000'),
  Decimal('37.00000'),
  '0101000020E61000003A00E2AE5E7E52C04E2844C021574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_ff07a_592d8a8cc7ac3.jpg',
  'good',
  '401441'),
 (7,
  '2017-05-30 10:27:45.203504',
  Decimal('6.00000'),
  Decimal('20.00000'),
  '0101000020E610000056975302627E52C0FAB31F2922574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_47b84_592d8a8d68243.jpg',
  'bad',
  '401441'),
 (8,
  '2017-05-30 10:27:48.238109',
  Decimal('7.00000'),
  Decimal('58.00000'),
  '0101000020E610000044C4CDA9647E52C0E257ACE122574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_7967e_592d8a8dec941.jpg',
  'bad',
  '401441'),
 (9,
  '2017-05-30 10:27:51.338804',
  Decimal('8.00000'),
  Decimal('25.00000'),
  '0101000020E6100000425C397B677E52C069F3FFAA23574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_e71d3_592d8a8e938f9.jpg',
  'bad',
  '401441'),
 (10,
  '2017-05-30 10:27:54.247092',
  Decimal('9.00000'),
  Decimal('60.00000'),
  '0101000020E61000008847E2E5697E52C0A034D42824574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_ffff1_592d8a8f24a5a.jpg',
  'bad',
  '401441'),
 (11,
  '2017-05-30 10:27:57.376539',
  Decimal('10.00000'),
  Decimal('29.00000'),
  '0101000020E6100000C95BAE7E6C7E52C0B85CFDD824574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_6c946_592d8a8fb5e88.jpg',
  'good',
  '401441'),
 (12,
  '2017-05-30 10:28:00.412310',
  Decimal('11.00000'),
  Decimal('82.00000'),
  '0101000020E6100000A39064566F7E52C0B037312427574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_8b47d_592d8a90460b3.jpg',
  'bad',
  '401441'),
 (13,
  '2017-05-30 10:28:02.293887',
  Decimal('12.00000'),
  Decimal('16.00000'),
  '0101000020E610000002D88008717E52C04A24D1CB28574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_dee7e_592d8a90cb4e5.jpg',
  'bad',
  '401441'),
 (14,
  '2017-05-30 10:28:05.249438',
  Decimal('13.00000'),
  Decimal('8.00000'),
  '0101000020E610000014437232717E52C091D0967329574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_84b13_592d8a915a1da.jpg',
  'bad',
  '401441'),
 (15,
  '2017-05-30 10:28:22.258383',
  Decimal('14.00000'),
  Decimal('5.00000'),
  '0101000020E61000008429CAA5717E52C0E4DC26DC2B574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_72e79_592d8a92022ee.jpg',
  'bad',
  '401441'),
 (16,
  '2017-05-30 10:28:25.359650',
  Decimal('15.00000'),
  Decimal('6.00000'),
  '0101000020E61000002507EC6A727E52C05A0D897B2C574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_597f0_592d8a928cb7f.jpg',
  'acceptable',
  '401441'),
 (17,
  '2017-05-30 10:27:18.191175',
  Decimal('0.00000'),
  Decimal('10.00000'),
  '0101000020E6100000FCE07CEA587E52C0892650C422574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_3b530_592d8a896b182.jpg',
  'acceptable',
  '401441'),
 (18,
  '2017-05-30 10:27:22.122909',
  Decimal('1.00000'),
  Decimal('10.00000'),
  '0101000020E610000030D461855B7E52C0890CAB7823574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_a9b9c_592d8a89f1f27.jpg',
  'acceptable',
  '401441'),
 (19,
  '2017-05-30 10:27:25.199758',
  Decimal('2.00000'),
  Decimal('4.00000'),
  '0101000020E610000094D8B5BD5D7E52C088F2052D24574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_ba53c_592d8a8a891cd.jpg',
  'acceptable',
  '401441'),
 (20,
  '2017-05-30 10:27:28.167336',
  Decimal('3.00000'),
  Decimal('2.00000'),
  '0101000020E61000004694F6065F7E52C03B55BE6724574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_94bd6_592d8a8b1f391.jpg',
  'bad',
  '401441'),
 (21,
  '2017-05-30 10:27:32.227059',
  Decimal('4.00000'),
  Decimal('6.00000'),
  '0101000020E6100000FFCD8B135F7E52C08EC9E2FE23574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_e0a45_592d8a8bd66bd.jpg',
  'good',
  '401441'),
 (22,
  '2017-05-30 10:27:37.197610',
  Decimal('5.00000'),
  Decimal('37.00000'),
  '0101000020E61000003A00E2AE5E7E52C04E2844C021574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_ff07a_592d8a8cc7ac3.jpg',
  'good',
  '401441'),
 (23,
  '2017-05-30 10:27:45.203504',
  Decimal('6.00000'),
  Decimal('20.00000'),
  '0101000020E610000056975302627E52C0FAB31F2922574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_47b84_592d8a8d68243.jpg',
  'bad',
  '401441'),
 (24,
  '2017-05-30 10:27:48.238109',
  Decimal('7.00000'),
  Decimal('58.00000'),
  '0101000020E610000044C4CDA9647E52C0E257ACE122574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_7967e_592d8a8dec941.jpg',
  'bad',
  '401441'),
 (25,
  '2017-05-30 10:27:51.338804',
  Decimal('8.00000'),
  Decimal('25.00000'),
  '0101000020E6100000425C397B677E52C069F3FFAA23574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_e71d3_592d8a8e938f9.jpg',
  'bad',
  '401441'),
 (26,
  '2017-05-30 10:27:54.247092',
  Decimal('9.00000'),
  Decimal('60.00000'),
  '0101000020E61000008847E2E5697E52C0A034D42824574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_ffff1_592d8a8f24a5a.jpg',
  'bad',
  '401441'),
 (27,
  '2017-05-30 10:27:57.376539',
  Decimal('10.00000'),
  Decimal('29.00000'),
  '0101000020E6100000C95BAE7E6C7E52C0B85CFDD824574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_6c946_592d8a8fb5e88.jpg',
  'good',
  '401441'),
 (28,
  '2017-05-30 10:28:00.412310',
  Decimal('11.00000'),
  Decimal('82.00000'),
  '0101000020E6100000A39064566F7E52C0B037312427574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_8b47d_592d8a90460b3.jpg',
  'bad',
  '401441'),
 (29,
  '2017-05-30 10:28:02.293887',
  Decimal('12.00000'),
  Decimal('16.00000'),
  '0101000020E610000002D88008717E52C04A24D1CB28574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_dee7e_592d8a90cb4e5.jpg',
  'bad',
  '401441'),
 (30,
  '2017-05-30 10:28:05.249438',
  Decimal('13.00000'),
  Decimal('8.00000'),
  '0101000020E610000014437232717E52C091D0967329574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_84b13_592d8a915a1da.jpg',
  'bad',
  '401441'),
 (31,
  '2017-05-30 10:28:22.258383',
  Decimal('14.00000'),
  Decimal('5.00000'),
  '0101000020E61000008429CAA5717E52C0E4DC26DC2B574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_72e79_592d8a92022ee.jpg',
  'bad',
  '401441'),
 (32,
  '2017-05-30 10:28:25.359650',
  Decimal('15.00000'),
  Decimal('6.00000'),
  '0101000020E61000002507EC6A727E52C05A0D897B2C574440',
  'http://storage4.openstreetcam.org/files/photo/2017/5/30/proc/401441_597f0_592d8a928cb7f.jpg',
  'acceptable',
  '401441')]

In [ ]: