Esempio n. 1
0
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
Esempio n. 2
0
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
Esempio n. 3
0
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))
Esempio n. 4
0
# -*- 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
Esempio n. 5
0
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
Esempio n. 6
0
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
Esempio n. 7
0
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
Esempio n. 8
0
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
Esempio n. 9
0
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
Esempio n. 10
0
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
Esempio n. 11
0
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
Esempio n. 12
0
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
Esempio n. 13
0
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