规定时间段经过某条道路的车辆前后5分钟轨迹

今日工作目标：
提取在积水时间段内，经过广州路与云南路积水点车辆在经过积水前后5分钟的轨迹，认为这些过水轨迹在速度上表现为异常。并提取相同情况下（同一时间段，同一地点路段）车辆10分钟内的轨迹，并认为这些轨迹在速度上表现为正常。
通过tsfresh，比较异常轨迹与正常轨迹在速度上的差异，能够提取出异常轨迹在时间-速度序列上的特征。

提取轨迹的Python代码：

import psycopg2
import csv

conn = psycopg2.connect(database="test", user="postgres",password="postgres", host="127.0.0.1", port="5432")
cursor = conn.cursor()

table_name = ['TRACK_20190704_Snap','2019-07-04 20:35:00+08','2019-07-04 22:00:00+08','_0704']


sql_str = "select \"TM_SERIAL\", min(\"ctime\"),(min(\"ctime\")::timestamp with time zone - interval \'5 min\'),(min(\"ctime\")::timestamp with time zone + interval \'5 min\') from public.\"{0}\"" \
                "where \"ctime\" > \'{1}\' and \"ctime\" < \'{2}\'" \
                "and \"osm_id_new\" in (101111,101112,101113,112598,112599)" \
                "group by \"TM_SERIAL\" order by \"TM_SERIAL\"".format(table_name[0],table_name[1],table_name[2])
print(sql_str)
cursor.execute(sql_str)

## 获取SELECT返回的元组
rows = cursor.fetchall()

with open('0702_guiji.csv','a',newline='') as f:
    csv_writer = csv.writer(f)
    for row in rows:
        # print(row[0])
        # print(row[1])
        # print(row[2])
        # print(row[3])
        str1 = "select * from public.\"{0}\" where \"TM_SERIAL\" = {1} and \"ctime\" > \'{2}\' and \"ctime\" < \'{3}\'".format(table_name[0],row[0],row[2],row[3])
        print(str1)
        cursor.execute(str1)
        datas = cursor.fetchall()
        for data in datas:
            t_id = data[0]
            tm_serial = str(data[2]) + table_name[3]
            velocity = data[4]
            c_time = data[5]
            lat_84 = data[7]
            lon_84 = data[6]
            list1 = []
            list1.append(t_id)
            list1.append(tm_serial)
            list1.append(c_time)
            list1.append(velocity)
            list1.append(lat_84)
            list1.append(lon_84)
            csv_writer.writerow(list1)

## 关闭游标
cursor.close()

## 关闭数据库连接
conn.close()

得到的数据形式为：
1.轨迹点表
2.轨迹（正常异常）