def create_db_table(tname=None, scenario=None, income=None, master_headers=None, data=None):
"creates a new table for this income group and scenario"
curs.execute('END')
sql = 'DROP TABLE IF EXISTS {}'.format(tname)
curs.execute(sql)
logger.debug('dropping table {}'.format(tname))
sql='CREATE TABLE IF NOT EXISTS {} ('.format(tname)
for col in master_headers:
sql+=' {} float,'.format(str(col))
sql = sql[:-1] + ');' #
print(sql)
curs.execute(sql)
conn.commit()
##TODO: should be a clever way simply to load the array (alt: dump file and load; also could just do INSERTs)
#for line in data:
#sql="INSERT INTO {} (".format(tname)
#for col in master_headers:
#sql+=' {}, '.format(col)
#sql=sql[:-2] + ") VALUES ("
#for value in line.split(','):
#sql+=' {},'.format(value)
#sql=sql[:-2] + ") "
#print(sql)
#curs.execute(sql)
pass
def store_data(arr_dict=None, db = DB, create_new_tables=CREATE_NEW_TABLES, zones=ZONES):
"""Stores data from all the benefits calculations made to date into a relational database.
The arr_dict contains all the arrays to be stored, the db table, and the db column. This
will create new tables if requested and take care of creating the necessary columns called
for by the arr_dict information"""
#Create a new db if needed. A bit convoluted in psql because you need to establish a new connection
# to address a different db. (In MySQL, it's as simple as going: USE database;)
if True:
conn = psycopg2.connect(
user=login_info['user'],
password=login_info['password']
)
curs = conn.cursor()
#if the database is not available, try to create it
try:
curs.execute('END')
curs.execute('CREATE DATABASE {}'.format(db))
logger.debug('creating new database')
except psycopg2.ProgrammingError:
pass #datbase already exists
#make a new connection to the right database
conn.close()
conn = psycopg2.connect(database = db,
user=login_info['user'],
password=login_info['password']
)
curs = conn.cursor()
#kill existing tables if instructed
if create_new_tables:
for arr in arr_dict:
#print('trying {}'.format(arr_dict[arr]['table']))
curs.execute("END")
curs.execute('DROP TABLE IF EXISTS {}'.format(arr_dict[arr]['table']))
conn.commit()
#print('done with {}'.format(arr_dict[arr]['table']))
#loop through our arrays dict, augmenting the database where needed
for arr in arr_dict:
#for readability, make pointers to elements of this dict entry
arr_table=arr_dict[arr]['table']
arr_data=arr_dict[arr]['data']
arr_column=arr_dict[arr]['column']
logger.info("storing data for table {} column {}".format(arr_table, arr_column))
#create a table if we need to; populate it with the zone data
try:
curs.execute('END')
curs.execute('SELECT * from {} LIMIT 1'.format(arr_table))
conn.commit()
except: #table does not exist
curs.execute('CREATE TABLE {} ({} float PRIMARY KEY )'.format(arr_table, 'zone', 'zone'))
for row in range(1, zones + 1):
sql="INSERT INTO {} ({}) VALUES ({})".format(arr_table, 'zone', row)
#print(sql)
curs.execute(sql)
conn.commit()
curs.execute("END")
#create a data column if we need to; popluate it with array data
try:
print(arr_column)
curs.execute("SELECT {} FROM {} LIMIT 1".format(arr_column, arr_table))
except:
#col does not exist
curs.execute('END')
sql="ALTER TABLE {} ADD {} float DEFAULT 0.0".format(arr_table, arr_column)
curs.execute(sql)
#print(sql)
for row in arr_data:
sql="UPDATE {} SET {}={} WHERE {}={}".format(arr_table, arr_column, row[1], 'zone', row[0])
curs.execute(sql)
#print(sql)
z=1
conn.commit()
a=1
def main(scenarios=scenarios, DB=DB, ROOT_DIR=ROOT_DIR, ZONES=ZONES, map_file=map_file):
"main entry point - loops over scenarios"
msg='{} Starting benefits calculations using input file {}'
logger.info(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), map_file))
print(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), map_file))
#This isn't the most efficient way to do it, but it's the most transparent: we'll loop through each base:scenario pair. For each, we'll read
# the input file a line at a time and draw our consumer surplus benefits
base_scenario = scenarios[0]
###for s in scenarios[1:]:
for s in scenarios[0:]: ###run this for the 'red line' case only
arr_dict={}
#grab a reader for the input file
reader=csv.DictReader(open(map_file))
#process one line at a time from the setup file
for line_ix, line in enumerate(reader, start=1):
# the info comes in as a dict - this cleans up the content, removing comments, etc. Return a dict.
dmap = grabinfo(line)
#these set processing parameters
transpose=dmap['transpose'] #use transposed trip matrix?
hov_adj=dmap['hov_adj'] #occupancy adjustment (hov2 implies double time costs, say)
pct_hb=dmap['pct_hb'] #fraction of benefits occuring to origin node ('home base')
#these set storage parameters
arr_name=dmap['aggregate_to']
column_name= arr_name
table_name=s['name']+"_"+dmap['dbtable']
#get information for the base case
base_dir=base_scenario['location'] #root directory location
base_name=base_scenario['name'] #name for this scenari
#Build fully specified path names built from locations in mappyings.py; subdirectory determined by file name
# then create np arrays out of them
base_cost_file=get_full_filename(location=base_dir, filename=dmap['cost_file'])
base_trips_file=get_full_filename(location=base_dir, filename=dmap['trip_file'])
#try to create npa arrays from the raw data files; if they don't exist go on to the next line
try:
base_trips_raw = npa_from_file( base_trips_file)
base_cost_per_trip_raw = npa_from_file( base_cost_file)
except:
exc_type, exc_value, exc_traceback = sys.exc_info()
msg='Scenario {}: could not open requisite files \n {} {} specified in line {} of {}'
logger.warning(msg.format(s['name'], exc_type, exc_value, line_ix, map_file))
continue
#Costs and trips for the base case - returns base costs, base trips as square np
# arrays w/o OD headers, trips transposed if needed
base_costs, base_trips=prep_data( base_cost_per_trip_raw , base_trips_raw, transpose, hov_adj )
##Process the scenario costs and trips the same way
#test_dir = s['location']
##grab the files and put them in np arrays
#try:
#test_cost_file=get_full_filename(location=test_dir, filename=dmap['cost_file'])
#test_trip_file=get_full_filename(location=test_dir, filename=dmap['trip_file'])
#except:
#msg='Scenario {}: could not open requisite files \n {} {} specified in line {} of {}'
#logger.warning(msg.format(s['name'], exc_type, exc_value, line_ix, map_file))
#test_trips_raw = npa_from_file( test_trip_file)
#test_cost_per_trip_raw = npa_from_file( test_cost_file)
#test_name=s['name']
##Scenario case trips*cost/trip and trips used
#test_costs, test_trips=prep_data( cost_per_trip=test_cost_per_trip_raw , trips=test_trips_raw, transpose=transpose, hov_adj=hov_adj )
#With all costs gathered, calculate the change in consumer surplus in square np array; produces a square np array
###cs_delta = get_cs_delta(base_trips, test_trips, base_costs, test_costs)
my_trips=get_base_trips_only(base_trips=base_trips)
#From the cs_delta matrix, assign benefits to the origin and destination node; produces a vector of nodes w/o OD headers
# For home-based transit trips, both outbound and return accrue to home node, as do am and pm highway trips.
# For mid-day and night-time highway trips, the benefit is split between origin and dest nodes.
###benefits_by_zone = calculate_benefits(cs_delta, pct_hb)
trips_by_zone=calculate_benefits(my_trips, pct_hb)
#the balance of this block stores the benefits and other information for posterity/more analysis
#We'll aggregate the cs_delta by benefit type, denominated in natural units (minutes, dollars, miles). We can use scalars to transform
#minutes to dollars, miles to CO2, etc. as a post-processing step.
#We'll create an aggregation array if requested in the 'aggregate to' column. This bit of code adds the array to the arr_dict if needed,
# then creates a null np array if needed. It adds a column of current benefits_by_zone to the array if the array is null; it increments
# the array by the benefits just calculated otherwise. This essentially creates a bundle of an array containing all the summary information
# we've gleaned from a scenario and where to store it when we're done.
#to hold the results, we'll make a dict arr_dict{'aggregate_to': {'data':npa_of_data,
# 'column': 'aggregate_to',
# 'table': 'db_table}}
#... where the 'aggregate_to' value comes from the input spreadsheet and serves as the name of the database column.
#create the dict if needed (it doesnt yet exist)
if not arr_name in arr_dict:
arr_dict[arr_name]={}
arr_dict[arr_name]['data']=None
#update the dict with current state of the roll-up array
arr_dict[arr_name]={ 'data': sum_col_to_np_array(npa=arr_dict[arr_name]['data'],
###vector=benefits_by_zone,
vector=trips_by_zone,
max_index_val=len(base_trips)),
'column': column_name,
'table': table_name
}
logger.debug('line {}\n\t{} -versus- {} \n\t {} \n\t {} \n\t base trips: {} test trips: {} sum dlta cs: {} (summary stats - not used in benefit calcs)'.format(
line_ix,
base_name, 'NA', ###test_name,
dmap['trip_file'],
dmap['cost_file'].split()[0],
np.sum(base_trips), 'NA', ###np.sum(test_trips),
np.sum(trips_by_zone)))
#store the arrays in db tables (written after all the processing for a scenario is completed.)
store_data(arr_dict=arr_dict, db=DB)
finish = datetime.now()
msg='Finished at {}. Processed {} files in {}.'
elapsed=str(finish-start).split('.')[0]
print(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), line_ix, elapsed))
logger.info(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), line_ix, elapsed))
# -*- coding: utf-8 -*-
import configparser
import json
import os
import logging
from version import VERSION
from logger_setup import logger
script_dir = os.path.dirname(__file__)
default_config_file = os.path.join(script_dir, "default_config.ini")
logger.debug("Reading from default_config.ini")
config = configparser.ConfigParser()
config.read(default_config_file)
if os.path.exists(os.path.join(script_dir, "config.ini")):
logger.debug("Reading also from config.ini")
specific_config_file = os.path.join(script_dir, "config.ini")
config.read(specific_config_file)
def get_env(section, var, default=None) -> str:
if "%s_%s" % (section, var) in os.environ:
return os.environ["%s_%s" % (section, var)]
elif section in config and var in config[section]:
return config[section][var]
else:
return default
def aggregate_bus_rail_fares(scenario=None,
income=None,
purposes=purposes,
purposes_round_trip=purposes_round_trip,
#purpose_peak_flag=purpose_peak_flag,
bus_modes=bus_modes,
rail_modes=rail_modes,
np_rows=NP_ROWS):
" rail/bus costs = SUM((peak trips * peak fare) + (op trips * op fare))"
"""
The main assumption is that work-related trips are on peak, and others are off. Here are the mappings:
purposes_pk=[('hbw', 1), ('nhbw', 2)]
purposes_op=[('obo', 3), ('hbs', 4), ('hbo', 5)]
The mode_choice_od table disaggregates to income, mode, purpose. For instance, we have data for
trips from home to work, walking to the express bus, on-peak for each OD pair. From the fares_fares table,
we know the fare for the express bus on-peak.
So we can simply aggregate across all the mode choice/purpose pairs to capture all trips associated with some
income group and assign the correct fair to each.
We do this by coming up with a bunch of SELECT statements like this pair:
'--adding bus_rail_fares for hbw. wexpbus - outbound leg
SELECT DISTINCT
test_fares_fares.origin,
test_fares_fares.dest,
test_yesred_nohwy_mode_choice_od.hbw_inc1_wexpbus * test_fares_fares.wexpbus_pk
FROM
test_yesred_nohwy_mode_choice_od , test_fares_fares
WHERE
test_yesred_nohwy_mode_choice_od.origin=test_fares_fares.origin AND
test_fares_fares.dest=test_yesred_nohwy_mode_choice_od.dest
ORDER BY
test_fares_fares.origin, test_fares_fares.dest
--adding bus_rail_fares for hbw. wexpbus - return leg
SELECT DISTINCT
test_fares_fares.origin,
test_fares_fares.dest,
test_yesred_nohwy_mode_choice_od.hbw_inc1_wexpbus * test_fares_fares.wexpbus_pk
FROM
test_yesred_nohwy_mode_choice_od , test_fares_fares
WHERE
test_yesred_nohwy_mode_choice_od.dest=test_fares_fares.origin AND
test_yesred_nohwy_mode_choice_od.origin=test_fares_fares.dest
ORDER BY
test_fares_fares.origin, test_fares_fares.dest
"""
#general info for this metric
topic = 'bus_rail_fares'
trips_table = '{}mode_choice_od'.format(scenario)
metrics_table= '{}fares_fares'.format(prefix)
logger.info('Beginning aggregation of {} data'.format(topic))
#initialize null np array
npa=np.zeros(((np_rows-1)**2, 3)) #orig, dest, value
fresh_npa_array=True
for purpose in purposes:
#peak or off peak as f(purpose)
pk_flag=purpose_peak_flag[purpose]
#round trip of one-way (round trip for home based journeys)?
trip_legs=['outbound']
if purpose in purposes_round_trip:
trip_legs.append('return')
#calculate each leg of the trip separately
for trip_leg in trip_legs:
#loop thru appropriate modes (no fares w/ auto) and compose SELECT
for mode in bus_modes+rail_modes:
# --adding {topic} for {purpose}. {mode} - {trip_leg} leg\n'
select= '--adding {} for {}. {} - {} leg\n'.format(topic, purpose, mode, trip_leg)
# SELECT DISTINCT
select += 'SELECT DISTINCT\n '
# {metrics_table}.origin
select += '\t{}.origin,\n'.format(metrics_table)
# {metrics_table}.dest
select += '\t{}.dest,\n'.format(metrics_table)
# '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{mode}_{pk_flag} '
stmt= '\t{}.{}_{}_{} * {}.{}_{}\n '
select+= stmt.format(trips_table, purpose, income, mode, metrics_table, mode, pk_flag)
# FROM {trips_table} , {metrics_table}
select += 'FROM \n\t {} , {} \n '.format( trips_table, metrics_table)
if trip_leg== 'outbound':
#use OD pairs from trip table same as metric table's
# WHERE {trips_table}.origin={metrics_table}.origin AND
select +='WHERE \n\t{}.origin={}.origin AND \n'.format( trips_table, metrics_table)
# {metrics_table}.dest={metrics_table}.dest)
select +='\t{}.dest={}.dest \n'.format(metrics_table, trips_table)
else:
#use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)
# WHERE {trips_table}.dest={metrics_table}.origin AND
select +='WHERE \n\t{}.dest={}.origin AND \n'.format( trips_table, metrics_table)
# {metrics_table}.origin={metrics_table}.dest)
select +='\t{}.origin={}.dest \n'.format(trips_table, metrics_table)
# ORDER BY {metrics_table}.origin, {metrics_table}.dest
select +='ORDER BY \n\t{}.origin, {}.dest\n\n'.format( metrics_table, metrics_table)
logger.debug('executing:\n' + select)
#some queries can't succeed because there are not tables to support them e.g., autodistance for wexpbus mode
good_table=True
try:
curs.execute(select)
except:
msg = '--Nevermind. NOT adding {} for {}. {} - {} leg - no table to support it\n'
logger.debug(msg.format(topic, purpose, mode, trip_leg))
good_table=False
break
if good_table:
res=np.array(curs.fetchall())
#add the results to the last column of the aggregation array
#grab the OD columns for the first result
if fresh_npa_array:
npa=res
fresh_npa_array=False
#add only the last column of subsequent ones
else:
npa[:,-1]+=res[:,-1]
logger.info('writing data for {} {} for {}: {} {} {}'.format(pk_flag, topic, scenario, income, purpose, mode))
logger.debug('executing {}\n'.format(select))
return npa
def make_rollup_tables():
for scenario in scenarios:
scenario=prefix + scenario+'_'
for income in incomes:
#initialize np array
master_npa=np.zeros(((NP_ROWS-1)**2, NP_COLS))
master_col =0 #numpy column index
master_headers=['origin', 'dest']
#************** bus/rail fares **************#
#add orig, dest columns to master_npa, since this is the first one loaded
col_name = 'bus_rail_fares' #column name in master_npa array
routine=aggregate_bus_rail_fares #the method run to process this
#add orig, dest columns to master_npa, since this is the first one loaded
master_npa = add_to_master( master_npa=master_npa,
npa=routine(scenario=scenario, income=income),
master_col=master_col,
include_od_cols=True)
master_headers.append(col_name)
master_col +=3 #accounts for addition of od cols
logger.info('done rolling up {} {} {}'.format(scenario, income, col_name))
#**************bus/ time time, distance**************#
#create an aggregate across all puropses, times,
routine=aggregate_transit_metrics
for metric in transit_metrics: #walktime, etc.
col_name=metric
master_npa = add_to_master( master_npa=master_npa,
npa=routine(scenario=scenario, income=income, topic = metric),
master_col=master_col,
include_od_cols=False
)
master_headers.append(col_name)
master_col +=1
logger.info('done rolling up {} {} {}'.format(scenario, income, col_name))
#**************highway distance/time**************#
#create an aggregate across all puropses, times,
routine=aggregate_hwy_costs
for metric in metrics: #toll, distance, time
for occupancy in occupancy_hwy_tod: #sov, hov2, hov3
col_name=metric+ '_' +occupancy
master_npa = add_to_master( master_npa=master_npa,
npa=routine(scenario=scenario, income=income, topic = metric, occupancy=occupancy),
master_col=master_col,
include_od_cols=False
)
master_headers.append(col_name)
master_col +=1
logger.info('done rolling up {} {} {}'.format(scenario, income, col_name))
db_table_name='aggregated_{}_{}' .format(scenario, income)
create_db_table(tname=db_table_name, scenario=scenario, income=income,
data=master_npa, master_headers=master_headers)
logger.debug('Success in creating aggregate db table for {} {}'.format(scenario, income))
a=1
def aggregate_transit_metrics(scenario=None,
income=None,
purposes=purposes,
purposes_round_trip=purposes_round_trip,
bus_modes=bus_modes,
rail_modes=rail_modes,
np_rows=NP_ROWS,
topic=None):
"""Aggregate time costs for mass transit. Roll up topics over purpose and mode.
Cf aggregate_bus_rail_fares() for more verbose documentation."""
"""Keeps topics (initialwaittime, bustime, etc.) separate for now. For final analysis it may makes sense to consolodate
waiting: initialwaittime, transfertime
bus time: wexpbus, dexpbus, wbus, dbus
train time: wrail, wcrail, drail, dcrail
... but it's easier to combine later than have to separate."""
"""Typical SELECTS
--adding xferwaittime for hbw. wbus - outbound leg
SELECT DISTINCT
test_yesred_nohwy_transit_od_timecost.origin,
test_yesred_nohwy_transit_od_timecost.dest,
test_yesred_nohwy_mode_choice_od.hbw_inc1_wbus * test_yesred_nohwy_transit_od_timecost.pk_wbus_xferwaittime
FROM
test_yesred_nohwy_mode_choice_od , test_yesred_nohwy_transit_od_timecost
WHERE
test_yesred_nohwy_mode_choice_od.origin=test_yesred_nohwy_transit_od_timecost.origin AND
test_yesred_nohwy_transit_od_timecost.dest=test_yesred_nohwy_mode_choice_od.dest
ORDER BY
test_yesred_nohwy_transit_od_timecost.origin, test_yesred_nohwy_transit_od_timecost.dest
--adding xferwaittime for hbw. wbus - return leg
SELECT DISTINCT
test_yesred_nohwy_transit_od_timecost.origin,
test_yesred_nohwy_transit_od_timecost.dest,
test_yesred_nohwy_mode_choice_od.hbw_inc1_wbus * test_yesred_nohwy_transit_od_timecost.pk_wbus_xferwaittime
--adding xferwaittime for hbw. wbus - return leg
SELECT DISTINCT
test_yesred_nohwy_transit_od_timecost.origin,
test_yesred_nohwy_transit_od_timecost.dest,
test_yesred_nohwy_mode_choice_od.hbw_inc1_wbus * test_yesred_nohwy_transit_od_timecost.pk_wbus_xferwaittime
FROM
test_yesred_nohwy_mode_choice_od , test_yesred_nohwy_transit_od_timecost
WHERE
test_yesred_nohwy_mode_choice_od.dest=test_yesred_nohwy_transit_od_timecost.origin AND
test_yesred_nohwy_mode_choice_od.origin=test_yesred_nohwy_transit_od_timecost.dest
ORDER BY
test_yesred_nohwy_transit_od_timecost.origin, test_yesred_nohwy_transit_od_timecost.dest
"""
#general info for this metric
trips_table = '{}mode_choice_od'.format(scenario)
metrics_table= '{}transit_od_timecost'.format(scenario)
logger.info('Beginning aggregation of {} data'.format(topic))
#initialize null np array
npa=np.zeros(((np_rows-1)**2, 3)) #orig, dest, value
fresh_npa_array=True
for purpose in purposes:
#peak or off peak as f(purpose)
pk_flag=purpose_peak_flag[purpose]
#round trip of one-way (round trip for home based journeys)?
trip_legs=['outbound']
if purpose in purposes_round_trip:
trip_legs.append('return')
#calculate each leg of the trip separately
for trip_leg in trip_legs:
#loop thru appropriate modes (no fares w/ auto) and compose SELECT
for mode in bus_modes+rail_modes:
# --adding {topic} for {purpose}. {mode} - {trip_leg} leg\n'
select= '--adding {} for {}. {} - {} leg\n'.format(topic, purpose, mode, trip_leg)
# SELECT DISTINCT
select += 'SELECT DISTINCT\n '
# {metrics_table}.origin
select += '\t{}.origin,\n'.format(metrics_table)
# {metrics_table}.dest
select += '\t{}.dest,\n'.format(metrics_table)
# '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{pk_flag}_{mode}_{topic} '
stmt= '\t{}.{}_{}_{} * {}.{}_{}_{}\n '
select+= stmt.format(trips_table, purpose, income, mode, metrics_table, pk_flag, mode, topic)
#print(select)
# FROM {trips_table} , {metrics_table}
select += 'FROM \n\t {} , {} \n '.format( trips_table, metrics_table)
if trip_leg== 'outbound':
#use OD pairs from trip table same as metric table's
# WHERE {trips_table}.origin={metrics_table}.origin AND
select +='WHERE \n\t{}.origin={}.origin AND \n'.format( trips_table, metrics_table)
# {metrics_table}.dest={metrics_table}.dest)
select +='\t{}.dest={}.dest \n'.format(metrics_table, trips_table)
else:
#use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)
# WHERE {trips_table}.dest={metrics_table}.origin AND
select +='WHERE \n\t{}.dest={}.origin AND \n'.format( trips_table, metrics_table)
# {metrics_table}.origin={metrics_table}.dest)
select +='\t{}.origin={}.dest \n'.format(trips_table, metrics_table)
# ORDER BY {metrics_table}.origin, {metrics_table}.dest
select +='ORDER BY \n\t{}.origin, {}.dest\n\n'.format( metrics_table, metrics_table)
logger.debug('executing:\n' +select)
#some queries can't succeed because there are not tables to support them e.g., autodistance for wexpbus mode
good_table=True
try:
curs.execute(select)
except:
msg = '--Nevermind. NOT adding {} for {}. {} - {} leg - no table to support it\n'
logger.debug(msg.format(topic, purpose, mode, trip_leg))
good_table=False
break
if good_table:
res=np.array(curs.fetchall())
#add the results to the last column of the aggregation array
#grab the OD columns for the first result
if fresh_npa_array:
npa=res
fresh_npa_array=False
#add only the last column of subsequent ones
else:
npa[:,-1]+=res[:,-1]
logger.info('writing data for {} {} for {}: {} {} {}'.format(pk_flag, topic, scenario, income, purpose, mode))
logger.debug('executing {}\n'.format(select))
a=1
return npa
def aggregate_hwy_costs(scenario=None,
income=None,
purposes=purposes,
purposes_round_trip=purposes_round_trip,
bus_modes=bus_modes,
rail_modes=rail_modes,
np_rows=NP_ROWS,
topic=None,
occupancy=None):
"""Aggregate costs for highway-only travel. Roll up topics over purpose, tod.
Keeps occupancies (sov, hov2, hov3) and costs (time, distance, toll) separate"""
"""Typical SELECTS
"""
"""
# hwy_toll_hov_am
# hbo_inc1_md_hov3"""
#general info for this metric
trips_table = '{}trips_purpose_income_tod_occ'.format(scenario)
metrics_table= '{}loaded_hwy_od_timecost'.format(scenario)
logger.info('Beginning aggregation of {} data'.format(topic))
#initialize null np array
npa=np.zeros(((np_rows-1)**2, 3)) #orig, dest, value
fresh_npa_array=True
for purpose in purposes:
#peak or off peak as f(purpose)
#pk_flag=purpose_peak_flag[purpose]
#round trip of one-way (round trip for home based journeys)?
trip_legs=['outbound']
if purpose in purposes_round_trip:
trip_legs.append('return')
#calculate each leg of the trip separately
for trip_leg in trip_legs:
#loop thru appropriate modes and compose SELECT
for mode in drive_modes:
for tod in tod_hwy_loaded:
# --adding {topic} for {purpose} {mode} {occupancy}- {trip_leg} leg\n'
select= '--adding {} for {} {} {} - {} leg\n'.format(topic, purpose, mode, occupancy, trip_leg)
# SELECT DISTINCT
select += 'SELECT DISTINCT\n '
# {metrics_table}.origin
select += '\t{}.origin,\n'.format(metrics_table)
# {metrics_table}.dest
select += '\t{}.dest,\n'.format(metrics_table)
if occupancy == 'hov2' or occupancy=='hov3':
occ_for_loaded = 'hov'
else:
occ_for_loaded='sov'
# '{trips_table}.{purpose}_{income}_{tod}_{occ} * {metrics_table}.hwy_{topic}_{occupancy}_{tod} '
stmt= '\t{}.{}_{}_{}_{} * {}.hwy_{}_{}_{}\n '
select+= stmt.format(trips_table, purpose, income, tod, occupancy, \
metrics_table, topic, occ_for_loaded, tod)
# FROM {trips_table} , {metrics_table}
select += 'FROM \n\t {} , {} \n '.format( trips_table, metrics_table)
if trip_leg== 'outbound':
#use OD pairs from trip table same as metric table's
# WHERE {trips_table}.origin={metrics_table}.origin AND
select +='WHERE \n\t{}.origin={}.origin AND \n'.format( trips_table, metrics_table)
# {metrics_table}.dest={metrics_table}.dest)
select +='\t{}.dest={}.dest \n'.format(metrics_table, trips_table)
else:
#use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)
# WHERE {trips_table}.dest={metrics_table}.origin AND
select +='WHERE \n\t{}.dest={}.origin AND \n'.format( trips_table, metrics_table)
# {metrics_table}.origin={metrics_table}.dest)
select +='\t{}.origin={}.dest \n'.format(trips_table, metrics_table)
# ORDER BY {metrics_table}.origin, {metrics_table}.dest
select +='ORDER BY \n\t{}.origin, {}.dest\n\n'.format( metrics_table, metrics_table)
logger.debug('executing:\n' +select)
#some queries can't succeed because there are not tables to support them e.g., autodistance for wexpbus mode
good_table=True
try:
print(select)
curs.execute('END')
curs.execute(select)
conn.commit()
except :
exc_type, exc_value, exc_traceback = sys.exc_info()
msg = '--Nevermind. NOT adding {} for {}. {} - {} leg - no table to support it\n'
logger.debug(msg.format(topic, purpose, mode, trip_leg))
good_table=False
break
if good_table:
res=np.array(curs.fetchall())
#add the results to the last column of the aggregation array
#grab the OD columns for the first result
if fresh_npa_array:
npa=res
fresh_npa_array=False
#add only the last column of subsequent ones
else:
npa[:,-1]+=res[:,-1]
logger.info('writing data for {} {} for {}: {} {} {}'.format(occupancy, topic, scenario, income, purpose, mode))
logger.debug('executing {}\n'.format(select))
return npa
def rollup_bus_rail_fares(
scenario=None,
base_scenario=None,
income=None,
purposes=purposes,
purposes_round_trip=purposes_round_trip,
# purpose_peak_flag=purpose_peak_flag,
bus_modes=bus_modes,
rail_modes=rail_modes,
np_rows=NP_ROWS,
):
" rail/bus costs = SUM((peak trips * peak fare) + (op trips * op fare))"
"""
The main assumption is that work-related trips are on peak, and others are off. Here are the mappings:
purposes_pk=[('hbw', 1), ('nhbw', 2)]
purposes_op=[('obo', 3), ('hbs', 4), ('hbo', 5)]
The mode_choice_od table disaggregates to income, mode, purpose. For instance, we have data for
trips from home to work, walking to the express bus, on-peak for each OD pair. From the fares_fares table,
we know the fare for the express bus on-peak.
So we can simply aggregate across all the mode choice/purpose pairs to capture all trips associated with some
income group and assign the correct fair to each.
--finding bus_rail_fares for hbw. wexpbus - outbound leg
SELECT DISTINCT
test_fares_fares.origin,
test_fares_fares.dest,
test_nored_nohwy_mode_choice_od.hbw_inc1_wexpbus * test_fares_fares.wexpbus_pk,
test_nored_nohwy_mode_choice_od.hbw_inc1_wexpbus
FROM
test_nored_nohwy_mode_choice_od , test_fares_fares
WHERE
test_nored_nohwy_mode_choice_od.origin=test_fares_fares.origin AND
test_fares_fares.dest=test_nored_nohwy_mode_choice_od.dest
ORDER BY
test_fares_fares.origin, test_fares_fares.dest
"""
# general info for this metric
topic = "bus_rail_fares"
base_trips_table = "{}_mode_choice_od".format(base_scenario)
test_trips_table = "{}_mode_choice_od".format(scenario)
base_metrics_table = "{}fares_fares".format(prefix)
test_metrics_table = "{}fares_fares".format(prefix)
cols_added = 5 # metric base, trips base, metric trial, trips trial, benefit
logger.info("Beginning aggregation of {} fares data".format(topic))
logger.info("Trips using {}\n and {}".format(base_trips_table, test_trips_table))
logger.info("Costs using {}\n and {}".format(base_metrics_table, test_metrics_table))
export_array = np.zeros(((np_rows - 1) ** 2, 3))
this_export_array_col = -1
# loop over purposes and bus/rail modes, accumulating the atomistic consumer surplus changes, including
# outbound + return for home-based trips
for purpose in purposes:
# peak or off peak as f(purpose)
pk_flag = purpose_peak_flag[purpose]
# round trip or one-way? (round trip for home based journeys)
trip_legs = ["outbound"]
if purpose in purposes_round_trip:
trip_legs.append("return")
# loop over all trips for both bus and rail modes
for mode in bus_modes + rail_modes:
logger.info("beginning benefit calcs for {} {} mode".format(purpose, mode))
# calculate benefits for each leg of the trip separately; combine the benefits from a round-trip at the end
# of the 'trip_leg' loop.
need_npa_combined = True # holds outbound+return benefits rollup
# flag for npa creation
this_np_col = -1
# construct the similar SELECT for both base and test scenarios (different tables), store results in np array 'npa',
# calculate separately the benefits for each leg then roll the benefits up in np array 'combined_npa'
# for each leg of the trip do a base versus trial case benefits calculation
for trip_leg in trip_legs:
if this_np_col < 0:
npa = np.zeros(((np_rows - 1) ** 2, cols_added + 2)) # scratch array
# this selects trips and costs from the base and trial case tables
for metrics_table, trips_table, name in zip(
[base_metrics_table, test_metrics_table], [base_trips_table, test_trips_table], ["base", scenario]
):
logger.info("running {} case for {} {} {}".format(name, purpose, mode, trip_leg))
# --finding {topic} for {purpose}. {mode} - {trip_leg} leg\n'
select = "--finding {} for {}. {} - {} leg - scenario: {}\n".format(
topic, purpose, mode, trip_leg, name
)
select += "SELECT DISTINCT\n "
# {metrics_table}.origin
select += "\t{}.origin,\n".format(metrics_table)
# {metrics_table}.dest
select += "\t{}.dest,\n".format(metrics_table)
# '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{mode}_{pk_flag} '
stmt = "\t{}.{}_{}_{} * {}.{}_{},\n "
select += stmt.format(trips_table, purpose, income, mode, metrics_table, mode, pk_flag)
# '{trips_table}.{purpose}_{income}_{mode} '
stmt = "\t{}.{}_{}_{}\n "
select += stmt.format(trips_table, purpose, income, mode)
# FROM {trips_table} , {metrics_table}
select += "FROM \n\t {} , {} \n ".format(trips_table, metrics_table)
if trip_leg == "outbound": # use OD pairs from trip table same as metric table's
# WHERE {trips_table}.origin={metrics_table}.origin AND
select += "WHERE \n\t{}.origin={}.origin AND \n".format(trips_table, metrics_table)
# {metrics_table}.dest={metrics_table}.dest)
select += "\t{}.dest={}.dest \n".format(metrics_table, trips_table)
else: # use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)
# WHERE {trips_table}.dest={metrics_table}.origin AND
select += "WHERE \n\t{}.dest={}.origin AND \n".format(trips_table, metrics_table)
# {metrics_table}.origin={metrics_table}.dest)
select += "\t{}.origin={}.dest \n".format(trips_table, metrics_table)
# ORDER BY {metrics_table}.origin, {metrics_table}.dest
select += "ORDER BY \n\t{}.origin, {}.dest\n\n".format(metrics_table, metrics_table)
logger.debug(select)
curs.execute(select)
res = np.array(curs.fetchall())
# This rolls up the costs and trips from both scenarios: npa is rows of: origin, dest, benefit base, trips base, benefit trial, trips trial
if this_np_col < 0:
# add first 4 columns of result to a the first 4 columns of the scratch array (origin, dest, base cost, base trips)
npa[:, :4] = res
this_np_col = 4
else:
# add the cost, trips columns from the result to cols 4-6 of the scratch array (trial cost, trial trips)
npa[:, 4:6] = res[:, -2:]
this_np_col += 2
# calculate the benefits
logger.info("calculating delta cs for {} {} {} ".format(scenario, purpose, mode))
npa = add_dlta_cons_surplus(npa)
# npa_combined rolls up the atomized benefits, calculated separately for each leg of the journey.
if need_npa_combined:
npa_combined = npa
need_npa_combined = False
logger.info("adding benefits to new npa_combined array")
else:
# otherwise add the benefits from the second leg (the last column) to the combined_npa array
npa_combined[:, -1] += npa[:, -1]
logger.info(
"done with both legs; adding return leg to npa_combined: {} {} ".format(purpose, mode)
)
# add consumer surplus improvements to the export array
if this_export_array_col < 0:
# not yet created; add the orig and destin columns, along with the cs deltas
export_array[:, :2] = npa_combined[:, :2]
export_array[:, -1] = npa_combined[:, -1]
this_export_array_col = 3
logger.debug("creating new export array")
else:
# ... otherwise just add the new benfits to the cumulative total
export_array[:, -1] += npa_combined[:, -1]
logger.info("adding additional cs deltas to export array")
logger.info("Done with mode {}\n\n".format(mode))
logger.info("Done with purpose {}".format(purpose))
return export_array
def xmake_rollup_tables():
"""This rolls up atomisitc improvements in cs between the base scenario and alternatives. The
result is a new db table for each scerario. Columns are aggregates of the cs improvments for
individual categories e.g., bus/rail fares."""
# note: race demograhics anticipated to be in table race_race. Stored by origin only
base = prefix + base_scenario # prefix is just a flag used for testing - null string in production
for scenario in test_scenarios:
scenario = prefix + scenario
master_headers = ["origin", "dest"]
for income in incomes:
tname = scenario + "_" + income + "_benefits"
# initialize np array
master_npa = np.zeros(((NP_ROWS - 1) ** 2, NP_COLS))
master_col = 0 # numpy column index
if True: # include for source code folding only
# ************** bus/rail fares **************#
# add orig, dest columns to master_npa, if this is the first one loaded
##TODO: consider changing the following for production
include_od_columns = True
col_name = "bus_rail_fares_{}".format(income) # column name in master_npa array
routine = rollup_bus_rail_fares # the method run to process this
# add orig, dest columns to master_npa, since this is the first one loaded
master_npa = add_to_master(
master_npa=master_npa,
npa=routine(scenario=scenario, base_scenario=base, income=income),
master_col=master_col,
include_od_cols=include_od_columns,
)
master_headers.append(col_name)
if include_od_columns:
master_col += 3 # accounts for addition of od cols
else:
master_col += 1
logger.info("done rolling up {} {} {}".format(scenario, income, col_name))
if True: # include for source code folding only
# **************bus/ rail time, distance**************#
# create an aggregate across all puropses, times,
##TODO: False for production
include_od_columns = False
routine = rollup_transit_metrics
# adding purposes loop so we can track work-related ('obo') and personal for differential VOT
for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]):
for metric in transit_metrics: # walktime, etc.
col_name = name + "_" + metric + "_" + income
npa = routine(
scenario=scenario, base_scenario=base, income=income, topic=metric, purposes=purposes
)
master_npa = add_to_master(
master_npa=master_npa, npa=npa, master_col=master_col, include_od_cols=False
)
master_headers.append(col_name)
if include_od_columns:
master_col += 3 # accounts for addition of od cols
else:
master_col += 1
logger.info("done rolling up transit metrics {} {} {}".format(scenario, income, name))
if True:
# **************highway distance/time**************#
# create an aggregate across all puropses, times,
##TODO: False for production
include_od_columns = False
routine = rollup_hwy_metrics
# adding purposes loop so we can track work-related ('obo') and personal for differential VOT
for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]):
for metric in metrics: # toll, distance, time
for occupancy in occupancy_hwy_tod: # sov, hov2, hov3
col_name = metric + "_" + occupancy
npa = routine(scenario=scenario, income=income, topic=metric, occupancy=occupancy)
master_npa = add_to_master(
master_npa=master_npa, npa=npa, master_col=master_col, include_od_cols=False
)
master_headers.append(col_name)
if include_od_columns:
master_col += 3 # accounts for addition of od cols
else:
master_col += 1
logger.info("done rolling up hwy metrics {} {} {}".format(scenario, income, name))
# income done
logger.info("done with income group {}".format(income))
# scenario done
logger.info("done with scenario {}".format(scenario))
db_table_name = "aggregated_{}_{}".format(scenario, income)
create_db_table(
tname=db_table_name, scenario=scenario, income=income, data=master_npa, master_headers=master_headers
)
logger.debug("Success in creating aggregate db table for {} {}".format(scenario, income))
a = 1
def make_rollup_tables():
"""This rolls up atomisitc improvements in cs between the base scenario and alternatives. The
result is a new db table for each scerario. Columns are aggregates of the cs improvments for
individual categories e.g., bus/rail fares."""
base = prefix + base_scenario # prefix is just a flag used for testing - null string in production
for scenario in test_scenarios:
scenario = prefix + scenario
master_headers = ["origin", "dest"]
# name of db table to save
tname = scenario + "_benefits"
for income in incomes:
# initialize np array; will hold all results for this scenario
master_npa = np.zeros(((NP_ROWS - 1) ** 2, NP_COLS))
master_col = 0 # numpy column index
# roll up rail + bus fares by income group.
# o_rollup_bus_rail_fares(base = None, scenario=None, income=None, master_npa=None,include_od_columns=False)
master_header, master_npa, master_col = do_rollup_bus_rail_fares(
base=base,
scenario=scenario,
income=income,
master_npa=master_npa,
master_col=master_col,
include_od_columns=True,
master_headers=master_headers,
)
# transit metrics (distance, toll, time) are calculated separately by type of travel (personal, biz) and metric
for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]):
for metric in transit_metrics: # walktime, etc.
master_headers, master_npa, master_col = do_rollup_transit_metrics(
base=base,
scenario=scenario,
income=income,
master_npa=master_npa,
master_col=master_col,
master_headers=master_headers,
purposes=purposes,
metric=metric,
name=name,
include_od_columns=False,
)
# highway (auto-only) metrics are calculated separately by type of travel (personal, biz) , occupancy and metri
for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]):
for metric in metrics: # toll, distance, time
for occupancy in occupancy_hwy_tod: # sov, hov2, hov3
master_header, master_npa, master_col = do_rollup_hwy_metrics(
base=base,
scenario=scenario,
occupancy=occupancy,
income=income,
master_npa=master_npa,
master_header=master_header,
master_col=master_col,
purposes=purposes,
metric=metric,
include_od_columns=False,
)
# income done
logger.info("done with income group {}".format(income))
# scenario done
logger.info("done with scenario {}".format(scenario))
db_table_name = "aggregated_{}_{}".format(scenario, income)
create_db_table(
tname=db_table_name, scenario=scenario, income=income, data=master_npa, master_headers=master_headers
)
logger.debug("Success in creating aggregate db table for {} {}".format(scenario, income))
a = 1
def rollup_hwy_metrics(
scenario=None,
income=None,
purposes=purposes,
master_header=None,
purposes_round_trip=purposes_round_trip,
np_rows=NP_ROWS,
topic=None,
occupancy=None,
):
"""Aggregate costs for highway-only travel. Roll up topics over purpose, tod.
Keeps occupancies (sov, hov2, hov3) and costs (time, distance, toll) separate"""
"""Typical SELECTS
"""
"""
# hwy_toll_hov_am
# hbo_inc1_md_hov3"""
# general info for this metric
base_trips_table = "{}trips_purpose_income_tod_occ".format(base_scenario)
test_trips_table = "{}trips_purpose_income_tod_occ".format(scenario)
base_metrics_table = "{}loaded_hwy_od_timecost".format(base_scenario)
test_metrics_table = "{}loaded_hwy_od_timecost".format(scenario)
# We'll need to leverage the time to reflect vehicle occupancy. Since the topic (metric) is an
# input parameter, we can calculate a uniform multiplier here. Occupancy leverages only time.
if topic == "time":
mult = time_adjust[occupancy]
else:
mult = 1
cols_added = 5 # metric base, trips base, metric trial, trips trial, benefit
logger.info("\n\n\n***** Beginning aggregation of highway data for {}".format(topic))
logger.info("Trips using {}\n and {}".format(base_trips_table, test_trips_table))
logger.info("Costs using {}\n and {}".format(base_metrics_table, test_metrics_table))
# this array will aggreate the info gleaned here
export_array = np.zeros(((np_rows - 1) ** 2, 3))
this_export_array_col = -1
# initialize null np array
npa = np.zeros(((np_rows - 1) ** 2, 3)) # orig, dest, value
fresh_npa_array = True
logger.info("Beginning aggregation of {} data".format(topic))
# we're passing in topic (metric), occupancy and income, purpose (grouped by business/personal as input, analyzed
# atomisticly here and provided as aggregate as output values)
for purpose in purposes: # rolls up whatever list of purposes provided (allows biz/personal segregation)
# round trip of one-way (round trip for home based journeys)?
trip_legs = ["outbound"]
if purpose in purposes_round_trip:
trip_legs.append("return")
for tod in tod_hwy_loaded: #'am', 'pm', 'md', 'nt'
logger.info("beginning benefit calcs for {} {} {} {}".format(purpose, tod, occupancy, topic))
need_npa_combined = True # holds outbound+return benefits rollup
# flag for npa creation
this_np_col = -1
# calculate benefits for each leg of the trip separately; combine the benefits from a round-trip at the end
# of the 'trip_leg' loop.
for trip_leg in trip_legs:
if this_np_col < 0:
npa = np.zeros(((np_rows - 1) ** 2, cols_added + 2)) # scratch array
# this selects from the base and trial case tables
for metrics_table, trips_table, name in zip(
[base_metrics_table, test_metrics_table], [base_trips_table, test_trips_table], ["base", scenario]
):
# --adding {topic} for {purpose} {tod} {occupancy}- {trip_leg} leg\n'
select = "--adding {} for {} {} {} - {} leg\n".format(topic, purpose, tod, occupancy, trip_leg)
select += "SELECT DISTINCT\n "
# {metrics_table}.origin
select += "\t{}.origin,\n".format(metrics_table)
# {metrics_table}.dest
select += "\t{}.dest,\n".format(metrics_table)
# '{trips_table}.{purpose}_{income}_{tod}_{occ} * {metrics_table}.hwy_{topic}_{occupancy}_{tod} * {mult}'
stmt = "\t{}.{}_{}_{}_{} * {}.hwy_{}_{}_{} * mult \n " # mult leverages time for HOVs
select += stmt.format(
trips_table, purpose, income, tod, occupancy, metrics_table, topic, occupancy, tod
)
# '{trips_table}.{purpose}_{income}_{tod}_{occ} '
stmt = "\t{}.{}_{}_{}_{}"
select += stmt.format(trips_table, purpose, income, tod, occupancy)
# FROM {trips_table} , {metrics_table}
select += "FROM \n\t {} , {} \n ".format(trips_table, metrics_table)
if trip_leg == "outbound": # use OD pairs from trip table same as metric table's
# WHERE {trips_table}.origin={metrics_table}.origin AND
select += "WHERE \n\t{}.origin={}.origin AND \n".format(trips_table, metrics_table)
# {metrics_table}.dest={metrics_table}.dest)
select += "\t{}.dest={}.dest \n".format(metrics_table, trips_table)
else: # use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)
# WHERE {trips_table}.dest={metrics_table}.origin AND
select += "WHERE \n\t{}.dest={}.origin AND \n".format(trips_table, metrics_table)
# {metrics_table}.origin={metrics_table}.dest)
select += "\t{}.origin={}.dest \n".format(trips_table, metrics_table)
# ORDER BY {metrics_table}.origin, {metrics_table}.dest
select += "ORDER BY \n\t{}.origin, {}.dest\n\n".format(metrics_table, metrics_table)
logger.debug(select)
try:
good_table = True
curs.execute(select)
except:
# some empty columns were not produced (e.g., wexpbus_autodistance) because they don't apply
relations = [
"\t{}.{}_{}_{}_{} * {}.hwy_{}_{}_{} * mult \n ".format(
trips_table, purpose, income, tod, occupancy, metrics_table, topic, occupancy, tod
),
"\t{}.{}_{}_{}".format(trips_table, purpose, income, tod),
]
logger.warning(
"This SELECT failed, probably because the data is n/a: {}".format(" ".join(relations))
)
good_table = False
# close out any
curs.execute("END")
# if the query failed, we've busted out; so go to the next tod
if good_table:
res = np.array(curs.fetchall())
# This rolls up the costs and trips from both scenarios: npa is rows of: origin, dest, benefit base, trips base, benefit trial, trips trial
if this_np_col < 0:
# add first 4 columns of result to a the first 4 columns of the scratch array (origin, dest, base cost, base trips)
npa[:, :4] = res
this_np_col = 4
else:
# add the cost, trips columns from the result to cols 4-6 of the scratch array (trial cost, trial trips)
npa[:, 4:6] = res[:, -2:]
this_np_col += 2
# calculate the benefits
logger.info("calculating delta cs for {} {} {} ".format(scenario, purpose, mode))
npa = add_dlta_cons_surplus(npa)
# npa_combined rolls up the atomized benefits, calculated separately for each leg of the journey.
if need_npa_combined:
npa_combined = npa
need_npa_combined = False
logger.info("adding benefits to new npa_combined array")
else:
# otherwise add the benefits from the second leg (the last column) to the combined_npa array
npa_combined[:, -1] += npa[:, -1]
logger.info(
"done with both legs; adding return leg to npa_combined: {} {} ".format(
purpose, mode
)
)
# next mode at col 12
# if a mode fails to produce a clean query, don't bother trying to add the info
if good_table:
if this_export_array_col < 0:
# not yet created; add the orig and destin columns, along with the cs deltas
export_array[:, :2] = npa_combined[:, :2]
export_array[:, -1] = npa_combined[:, -1]
this_export_array_col = 3
logger.debug("creating new export array")
else:
# ... otherwise just add the new benfits to the cumulative total
export_array[:, -1] += npa_combined[:, -1]
logger.info("adding additional cs deltas to export array")
###
logger.info("Done with mode {}\n\n".format(mode))
logger.info("Done with purpose {}".format(purpose))
return export_array
def rollup_transit_metrics(
scenario=None,
base_scenario=None,
income=None,
purposes=purposes,
master_col=None,
master_header=None,
purposes_round_trip=purposes_round_trip,
bus_modes=bus_modes,
rail_modes=rail_modes,
np_rows=NP_ROWS,
topic=None,
):
"""Aggregate time costs for mass transit. Roll up topics over purpose and mode.
Cf aggregate_bus_rail_fares() for more verbose documentation."""
"""Keeps topics (initialwaittime, bustime, etc.) separate for now. For final analysis it may makes sense to consolodate
waiting: initialwaittime, transfertime
bus time: wexpbus, dexpbus, wbus, dbus
train time: wrail, wcrail, drail, dcrail
... but it's easier to combine later than have to separate."""
"""Typical SELECTS
"""
# general info for this metric
scenario = scenario + "_"
base_scenario = base_scenario + "_"
base_trips_table = "{}mode_choice_od".format(base_scenario)
test_trips_table = "{}mode_choice_od".format(scenario)
base_metrics_table = "{}transit_od_timecost".format(base_scenario)
test_metrics_table = "{}transit_od_timecost".format(scenario)
cols_added = 5 # metric base, trips base, metric trial, trips trial, benefit
logger.info("\n\n\n***** Beginning aggregation of transit data for {}".format(topic))
logger.info("Trips using {}\n and {}".format(base_trips_table, test_trips_table))
logger.info("Costs using {}\n and {}".format(base_metrics_table, test_metrics_table))
# this array will aggreate the info gleaned here
export_array = np.zeros(((np_rows - 1) ** 2, 3))
this_export_array_col = -1
# initialize null np array
npa = np.zeros(((np_rows - 1) ** 2, 3)) # orig, dest, value
fresh_npa_array = True
for purpose in purposes:
# peak or off peak as f(purpose)
pk_flag = purpose_peak_flag[purpose]
# round trip of one-way (round trip for home based journeys)?
trip_legs = ["outbound"]
if purpose in purposes_round_trip:
trip_legs.append("return")
# loop thru appropriate modes and compose SELECT
for mode in bus_modes + rail_modes:
logger.info("beginning benefit calcs for {} {} {}".format(purpose, mode, topic))
# calculate benefits for each leg of the trip separately; combine the benefits from a round-trip at the end
# of the 'trip_leg' loop.
need_npa_combined = True # holds outbound+return benefits rollup
# flag for npa creation
this_np_col = -1
# calculate each leg of the trip separately
for trip_leg in trip_legs:
if this_np_col < 0:
npa = np.zeros(((np_rows - 1) ** 2, cols_added + 2)) # scratch array
# this selects from the base and trial case tables
for metrics_table, trips_table, name in zip(
[base_metrics_table, test_metrics_table], [base_trips_table, test_trips_table], ["base", scenario]
):
logger.info(
"running {} case for {} {} {} using {} and {}".format(
name, purpose, mode, trip_leg, trips_table, metrics_table
)
)
# create SELECT statements
# --adding {topic} for {purpose}. {mode} - {trip_leg} leg\n'
select = "--adding {} for {}. {} - {} leg\n".format(topic, purpose, mode, trip_leg)
select += "SELECT DISTINCT\n "
# {metrics_table}.origin origin
select += "\t{}.origin,\n".format(metrics_table)
# {metrics_table}.dest destination
select += "\t{}.dest,\n".format(metrics_table)
# '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{pk_flag}_{mode}_{topic} ' metric
stmt = "\t{}.{}_{}_{} * {}.{}_{}_{},\n "
select += stmt.format(trips_table, purpose, income, mode, metrics_table, pk_flag, mode, topic)
# '{trips_table}.{purpose}_{income}_{mode} ' trips
stmt = "\t{}.{}_{}_{}\n "
select += stmt.format(trips_table, purpose, income, mode)
# print(select)
# FROM {trips_table} , {metrics_table}
select += "FROM \n\t {} , {} \n ".format(trips_table, metrics_table)
if trip_leg == "outbound": # use OD pairs from trip table same as metric table's
# WHERE {trips_table}.origin={metrics_table}.origin AND
select += "WHERE \n\t{}.origin={}.origin AND \n".format(trips_table, metrics_table)
# {metrics_table}.dest={metrics_table}.dest)
select += "\t{}.dest={}.dest \n".format(metrics_table, trips_table)
else: # use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)
# WHERE {trips_table}.dest={metrics_table}.origin AND
select += "WHERE \n\t{}.dest={}.origin AND \n".format(trips_table, metrics_table)
# {metrics_table}.origin={metrics_table}.dest)
select += "\t{}.origin={}.dest \n".format(trips_table, metrics_table)
# ORDER BY {metrics_table}.origin, {metrics_table}.dest
select += "ORDER BY \n\t{}.origin, {}.dest\n\n".format(metrics_table, metrics_table)
logger.debug(select)
try:
good_table = True
curs.execute(select)
except:
# some empty columns were not produced (e.g., wexpbus_autodistance) because they don't apply
relations = [
"\t{}.{}_{}_{} * {}.{}_{}_{},\n ".format(
trips_table, purpose, income, mode, metrics_table, pk_flag, mode, topic
),
"\t{}.{}_{}_{}\n ".format(trips_table, purpose, income, mode),
]
logger.warning(
"This SELECT failed, probably because the data is n/a: {}".format(" ".join(relations))
)
good_table = False
# close out any
curs.execute("END")
# if the query failed, we've busted out; so go to the next mode
if good_table:
res = np.array(curs.fetchall())
# This rolls up the costs and trips from both scenarios: npa is rows of: origin, dest, benefit base, trips base, benefit trial, trips trial
if this_np_col < 0:
# add first 4 columns of result to a the first 4 columns of the scratch array (origin, dest, base cost, base trips)
npa[:, :4] = res
this_np_col = 4
else:
# add the cost, trips columns from the result to cols 4-6 of the scratch array (trial cost, trial trips)
npa[:, 4:6] = res[:, -2:]
this_np_col += 2
# calculate the benefits
logger.info("calculating delta cs for {} {} {} ".format(scenario, purpose, mode))
npa = add_dlta_cons_surplus(npa)
# npa_combined rolls up the atomized benefits, calculated separately for each leg of the journey.
if need_npa_combined:
npa_combined = npa
need_npa_combined = False
logger.info("adding benefits to new npa_combined array")
else:
# otherwise add the benefits from the second leg (the last column) to the combined_npa array
npa_combined[:, -1] += npa[:, -1]
logger.info(
"done with both legs; adding return leg to npa_combined: {} {} ".format(
purpose, mode
)
)
# next mode at col 12
# if a mode fails to produce a clean query, don't bother trying to add the info
if good_table:
if this_export_array_col < 0:
# not yet created; add the orig and destin columns, along with the cs deltas
export_array[:, :2] = npa_combined[:, :2]
export_array[:, -1] = npa_combined[:, -1]
this_export_array_col = 3
logger.debug("creating new export array")
else:
# ... otherwise just add the new benfits to the cumulative total
export_array[:, -1] += npa_combined[:, -1]
logger.info("adding additional cs deltas to export array")
###
logger.info("Done with mode {}\n\n".format(mode))
logger.info("Done with purpose {}".format(purpose))
return export_array
