def create_link_counts_table():
sql = """CREATE TABLE link_counts (
begin_node_id BIGINT,
end_node_id BIGINT,
avg_num_trips FLOAT);"""
try:
db_main.execute(sql)
except:
print "Error creating link counts table."
db_main.commit()
def save_link_counts(count_dict):
sqls = []
for (begin_node_id, end_node_id) in count_dict:
count = count_dict[(begin_node_id, end_node_id)]
sql = "INSERT INTO link_counts VALUES(%d,%d,%f);" % (
begin_node_id,end_node_id,count)
sqls.append(sql)
db_main.execute("DELETE FROM link_counts;")
db_main.execute("\n".join(sqls))
db_main.commit()
def save_link_counts(count_dict, perc_dict):
sqls = []
for (begin_node_id, end_node_id) in count_dict:
count = count_dict[(begin_node_id, end_node_id)]
perc_obs = perc_dict[(begin_node_id, end_node_id)]
sql = "INSERT INTO link_counts VALUES(%d,%d,%f,%f);" % (
begin_node_id,end_node_id,count, perc_obs)
sqls.append(sql)
db_main.execute("DELETE FROM link_counts;")
db_main.execute("\n".join(sqls))
db_main.commit()
def create_link_counts_table():
drop_link_counts_table()
sql = """CREATE TABLE link_counts (
begin_node_id BIGINT,
end_node_id BIGINT,
avg_num_trips FLOAT,
perc_obs FLOAT);"""
try:
db_main.execute(sql)
except:
print "Error creating link counts table."
db_main.commit()
def create_travel_time_table():
sql = """CREATE TABLE travel_times (
begin_node_id BIGINT,
end_node_id BIGINT,
datetime TIMESTAMP,
travel_time REAL,
num_trips INTEGER);"""
try:
db_main.execute(sql)
sql = "CREATE INDEX idx_tt_datetime ON travel_times using BTREE (datetime);"
db_main.execute(sql)
except:
pass
db_main.commit()
def create_travel_time_table():
sql = """CREATE TABLE travel_times (
begin_node_id BIGINT,
end_node_id BIGINT,
datetime TIMESTAMP,
travel_time REAL,
num_trips INTEGER);"""
try:
db_main.execute(sql)
sql = "CREATE INDEX idx_tt_datetime ON travel_times using BTREE (datetime);"
db_main.execute(sql)
except:
pass
db_main.commit()
def save_travel_times(road_map, datetime):
date_str = "'" + str(datetime) + "'"
# First remove any existing travel times for the given datetime
delete_travel_times(datetime)
# Next, add one row with the default speed. This will have nodes 0, 0
# The default speed will be saved in the travel time field
default_speed = road_map.get_default_speed()
if(default_speed!=None):
sql = "INSERT INTO travel_times VALUES(0, 0, %s, %f, 0);" % (date_str, default_speed)
db_main.execute(sql)
BULK_SIZE=5000
# Create a prepared statement
db_main.execute("PREPARE tt_plan (BIGINT, BIGINT, TIMESTAMP, REAL, INTEGER) AS "
"INSERT INTO travel_times VALUES($1, $2, $3, $4, $5);")
sqls = []
# Loop through the Links and create a bunch of EXECUTE statements
for link in road_map.links:
if(link.num_trips > 0):
sql = "EXECUTE tt_plan(%d, %d, %s, %f, %d);" % (
link.origin_node_id, link.connecting_node_id, date_str, link.time, link.num_trips)
sqls.append(sql)
if(len(sqls) >= BULK_SIZE):
# Concatenate EXECUTE statements and run them
final_sql = "\n".join(sqls)
sqls = []
db_main.execute(final_sql)
db_main.commit()
# Run the last few EXECUTE statements if necessary
if(len(sqls) > 1):
final_sql = "\n".join(sqls)
db_main.execute(final_sql)
db_main.commit()
# Clean up the prepared statement
db_main.execute("DEALLOCATE tt_plan;")
db_main.commit()
def save_travel_times(road_map, datetime):
date_str = "'" + str(datetime) + "'"
# First remove any existing travel times for the given datetime
delete_travel_times(datetime)
# Next, add one row with the default speed. This will have nodes 0, 0
# The default speed will be saved in the travel time field
default_speed = road_map.get_default_speed()
if(default_speed!=None):
sql = "INSERT INTO travel_times VALUES(0, 0, %s, %f, 0);" % (date_str, default_speed)
db_main.execute(sql)
BULK_SIZE=5000
# Create a prepared statement
db_main.execute("PREPARE tt_plan (BIGINT, BIGINT, TIMESTAMP, REAL, INTEGER) AS "
"INSERT INTO travel_times VALUES($1, $2, $3, $4, $5);")
sqls = []
# Loop through the Links and create a bunch of EXECUTE statements
for link in road_map.links:
if(link.num_trips > 0):
sql = "EXECUTE tt_plan(%d, %d, %s, %f, %d);" % (
link.origin_node_id, link.connecting_node_id, date_str, link.time, link.num_trips)
sqls.append(sql)
if(len(sqls) >= BULK_SIZE):
# Concatenate EXECUTE statements and run them
final_sql = "\n".join(sqls)
sqls = []
db_main.execute(final_sql)
db_main.commit()
# Run the last few EXECUTE statements if necessary
if(len(sqls) > 1):
final_sql = "\n".join(sqls)
db_main.execute(final_sql)
db_main.commit()
# Clean up the prepared statement
db_main.execute("DEALLOCATE tt_plan;")
db_main.commit()
def delete_travel_times(datetime):
sql = "DELETE FROM travel_times where datetime=%s;"
db_main.execute(sql, (datetime,))
db_main.commit()
def drop_travel_time_table():
try:
db_main.execute("DROP TABLE travel_times;")
except:
pass
db_main.commit()
def delete_travel_times(datetime):
sql = "DELETE FROM travel_times where datetime=%s;"
db_main.execute(sql, (datetime,))
db_main.commit()
def drop_travel_time_table():
try:
db_main.execute("DROP TABLE travel_times;")
except:
pass
db_main.commit()