notebook.community

Edit and run



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 [ ]:

	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