def create_final_table_deterministic(name):
query = "SELECT NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename='{0}')".format(
name)
create_table = db.execute_query(query)[0][0]
if create_table:
query = 'CREATE TABLE {0} (' \
'time VARCHAR (50), ' \
'sensor_id numeric , ' \
'speed numeric'.format(name)
query = query + ')'
db.execute_command(query, 'Successfully created d4_raw_table')
def create_final_table(name):
query = "SELECT NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename='{0}')".format(
name)
create_table = db.execute_query(query)[0][0]
if create_table:
query = 'CREATE TABLE {0} (' \
'time VARCHAR (50), ' \
'sensor_id numeric , '.format(name)
for i in range(11):
query += 'bucket_{} numeric, '.format(i)
query = query[:-2] + ')'
db.execute_command(query, 'Successfully created d4_raw_table')
def create_raw_table(name):
query = "SELECT NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename='{0}')".format(
name)
create_table = db.execute_query(query)[0][0]
if create_table:
query = 'CREATE TABLE {0} (' \
'id serial PRIMARY KEY, ' \
'time VARCHAR (50), ' \
'sensor_id numeric , '.format(name)
for i in range(8):
query += 'occupancy_lane_{0} VARCHAR(10), '.format(i)
query += 'speed_lane_{0} VARCHAR(10), '.format(i)
query += 'cars_lane_{0} VARCHAR(10), '.format(i)
query = query[:-2] + ')'
db.execute_command(query, 'Successfully created d4_raw_table')
output_dir = 'final_data/PEMS-BAY/medium/estimation/'
num_dates = 8404
no_features = 11
num_sensors = 87
sensor_ids = load_pickle('final_data/PEMS-BAY/medium/adj_mx_medium.pkl')[0]
values_sensor_ids = str(['(' + str(x) + ')'
for x in sensor_ids]).replace("'", "")[1:-1]
select_query = "SELECT time, sensor_id, bucket_0::float, bucket_1::float, bucket_2::float, bucket_3::float," \
" bucket_4::float, bucket_5::float, bucket_6::float, bucket_7::float, bucket_8::float, bucket_9::float," \
" bucket_10::float from {0} where sensor_id = ANY(VALUES {1})" \
"order by time, sensor_id asc"
data = db.execute_query(
select_query.format('pems_final_normalized', values_sensor_ids))
data = np.array([x[2:] for x in data])
data = data.reshape([num_dates, num_sensors, no_features])
np.save(os.path.join(output_dir, "%s.npz" % 'data'), data)
# Compute the average distribution
data = db.execute_query(select_query.format('pems_final', values_sensor_ids))
data = np.array([x[2:] for x in data])
data = data.reshape([num_dates, num_sensors, no_features])
sensor_cars_counts = np.sum(data, axis=0) # (sensor, buckets)
mean_sensor_cars_counts = sensor_cars_counts / data.shape[0]
total = np.sum(mean_sensor_cars_counts, keepdims=True, axis=1)
total[total == 0] = 1
average_histogram = mean_sensor_cars_counts / np.broadcast_to(
# " COALESCE(pems_buckets.bucket_7_lane_0, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_1, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_2, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_3, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_4, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_5, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_6, 0::double precision) + COALESCE(pems_buckets.bucket_7_lane_7, 0::double precision) AS bucket_7," \
# " COALESCE(pems_buckets.bucket_8_lane_0, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_1, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_2, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_3, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_4, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_5, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_6, 0::double precision) + COALESCE(pems_buckets.bucket_8_lane_7, 0::double precision) AS bucket_8," \
# " COALESCE(pems_buckets.bucket_9_lane_0, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_1, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_2, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_3, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_4, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_5, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_6, 0::double precision) + COALESCE(pems_buckets.bucket_9_lane_7, 0::double precision) AS bucket_9," \
# " COALESCE(pems_buckets.bucket_10_lane_0, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_1, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_2, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_3, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_4, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_5, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_6, 0::double precision) + COALESCE(pems_buckets.bucket_10_lane_7, 0::double precision) AS bucket_10" \
# " FROM pems_buckets;"
#
# db.execute_command(query)
#
# print('Starting to fill in missing data')
# populate with 0 if there are missing rows
select_dates = "SELECT distinct(time) from pems_final;"
select_sensors = "SELECT distinct(sensor_id) from pems_final;"
select_data = "SELECT time, sensor_id from pems_final;"
dates = db.execute_query(select_dates)
sensors = db.execute_query(select_sensors)
data = db.execute_query(select_data)
start_time = time.time()
missing_rows = []
data_dict = {}
for row in data:
date = row[0]
sensor_id = row[1]
if date not in data_dict:
data_dict[date] = {}
data_dict[date][sensor_id] = 1