File name
Commit message
Commit date
File name
Commit message
Commit date
{
"cells": [
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import glob "
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\\Accelerometer.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\AccelerometerUncalibrated.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\Barometer.csv (145, 4) Index(['time', 'seconds_elapsed', 'relativeAltitude', 'pressure'], dtype='object')\n",
"\\Gravity.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\Gyroscope.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\GyroscopeUncalibrated.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\Location.csv (153, 12) Index(['time', 'seconds_elapsed', 'altitude', 'speedAccuracy',\n",
" 'bearingAccuracy', 'latitude', 'altitudeAboveMeanSeaLevel', 'bearing',\n",
" 'horizontalAccuracy', 'verticalAccuracy', 'longitude', 'speed'],\n",
" dtype='object')\n",
"\\Magnetometer.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\MagnetometerUncalibrated.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
"\\Metadata.csv (1, 8) Index(['version', 'device name', 'recording time', 'platform', 'appVersion',\n",
" 'device id', 'sensors', 'sampleRateMs'],\n",
" dtype='object')\n",
"\\Orientation.csv (15201, 9) Index(['time', 'seconds_elapsed', 'yaw', 'qx', 'qz', 'roll', 'qw', 'qy',\n",
" 'pitch'],\n",
" dtype='object')\n"
]
}
],
"source": [
"filepath = r'C:\\Users\\User\\Downloads\\포트홀 주행 데이터\\2023-05-30_05-10-55'\n",
"for filename in glob.glob(filepath+'\\*.csv'):\n",
" try :\n",
" df= pd.read_csv(filename)\n",
" print(filename.replace(filepath,''),df.shape,df.columns) \n",
" except:\n",
" continue\n",
" #"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(df.shape,df.columns)"
]
},
{
"cell_type": "code",
"execution_count": 49,
"metadata": {},
"outputs": [],
"source": [
"import psycopg2\n",
"import time\n",
"from datetime import timedelta,datetime\n",
"\n",
"class DB():\n",
" def __init__(self):\n",
" self.conn=psycopg2.connect(\n",
" host='localhost',\n",
" dbname='postgres',\n",
" user='postgres',\n",
" password='ts4430!@',\n",
" port='5432'\n",
" ) # db에 접속\n",
" self.conn.autocommit=True\n",
" \n",
" def db_add_report(self,report_id,report_x,report_y) :\n",
" cur = self.conn.cursor() # 커서를 생성한다\n",
" now=time.localtime()\n",
" d=time.strftime('%Y-%m-%d %X', now)\n",
" cur.execute(f'''\n",
" insert into \"TRAFFICAGENCY\".report (report_id,report_x,report_y,timestamp)\n",
" values ('{report_id}','{report_x}','{report_y}','{d}')\n",
" ''')\n",
" \n",
" def db_get_near_point(self,dest_x,dest_y):\n",
" cur = self.conn.cursor() # 커서를 생성한다\n",
" now=datetime.now() \n",
" d_plus=now +timedelta(hours=1)\n",
" d_plus=str(\"'\"+d_plus.strftime('%Y-%m-%d %X')+\"'\")\n",
" d_minus=now -timedelta(hours=1)\n",
" d_minus=str(\"'\"+d_minus.strftime('%Y-%m-%d %X')+\"'\")\n",
" cur.execute(f'''\n",
" select report_x, report_y\n",
" from \"TRAFFICAGENCY\".report \n",
" where {dest_y} > report_y - 0.000498 and {dest_y} <= report_y + 0.000498\n",
" and {dest_x} > report_x - 0.000498 and {dest_x} <= report_x + 0.000498\n",
" and timestamp between {d_minus} and {d_plus};\n",
"\n",
"\n",
" ''')\n",
" result=cur.fetchall()\n",
" return result\n",
" \n",
" def db_add_pothole(pothole_id,pothole_location_x,pothole_location_y) :\n",
" cur = self.conn.cursor() # 커서를 생성한다\n",
" now=time.localtime()\n",
" d=time.strftime('%Y-%m-%d %X', now)\n",
" cur.execute(f'''\n",
" insert into \"TRAFFICAGENCY\".pothole (pothole_id,pothole_location_x,pothole_location_y,timestamp)\n",
" values ('{pothole_id}','{pothole_location_x}','{pothole_location_y}','{d}')\n",
" ''') \n",
" \n",
"\n",
"\n",
"db = DB()\n",
"pothole_x = 123.123\n",
"pothole_y = 32.123\n",
"db.db_add_report(2,pothole_x,pothole_y)\n",
"result=db.db_get_near_point(pothole_x,pothole_y)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"123.123\n",
"32.123\n"
]
}
],
"source": [
"if len(result) >3 :\n",
" value = 0\n",
" for i in range(len(result)):\n",
" value += result[i][0]\n",
" average_x= (value / len(result))\n",
" value2 = 0\n",
" for i in range(len(result)):\n",
" value2 += result[i][1]\n",
" average_y= (value2 / len(result))\n",
" db.db_add_pothole(1,average_x,average_y)\n"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {},
"outputs": [],
"source": [
"from datetime import timedelta,datetime\n",
"now=datetime.now() \n",
"d_plus=now +timedelta(hours=1)\n",
"d_plus=str(d_plus.strftime('%Y-%m-%d %X'))\n",
"d_minus=now -timedelta(hours=1)\n",
"d_minus=str(d_minus.strftime('%Y-%m-%d %X'))\n"
]
},
{
"cell_type": "code",
"execution_count": 46,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'2023-07-17 17:43:29'"
]
},
"execution_count": 46,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"d_plus"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"time.struct_time(tm_year=2023, tm_mon=7, tm_mday=17, tm_hour=16, tm_min=30, tm_sec=2, tm_wday=0, tm_yday=198, tm_isdst=0)"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"time.localtime() "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"select report_x, report_y \n",
"from \"TRAFFICAGENCY\".report \n",
"where 32.123 > report_y - 0.000498 and 32.123 <= report_y + 0.000498\n",
"and 123.123 > report_x - 0.000498 and 123.123 <= report_x + 0.000498\n",
"and timestamp between {d_plus} and {d_minus}\n",
"\n",
"pothole_x = 123.123\n",
"pothole_y = 32.123"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "base",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.4"
},
"orig_nbformat": 4
},
"nbformat": 4,
"nbformat_minor": 2
}