def year(base_year):
if 'iter_var' in orca.list_injectables():
year = orca.get_injectable('iter_var')
if year is not None:
return year
# outside of a run, return the base/default
return base_year
def year(base_year):
"""
The current year. This will be the base unless called within
the context of a run.
"""
if 'iter_var' in orca.list_injectables():
year = orca.get_injectable('iter_var')
if year is not None:
return year
return base_year
def schema():
"""
All tables, columns, steps, injectables and broadcasts registered with
Orca. Inc.des local columns on tables.
"""
tables = orca.list_tables()
cols = {t: orca.get_table(t).columns for t in tables}
steps = orca.list_steps()
injectables = orca.list_injectables()
broadcasts = orca.list_broadcasts()
return jsonify(tables=tables, columns=cols, steps=steps, injectables=injectables, broadcasts=broadcasts)
def schema():
"""
All tables, columns, steps, injectables and broadcasts registered with
Orca. Inc.des local columns on tables.
"""
tables = orca.list_tables()
cols = {t: orca.get_table(t).columns for t in tables}
steps = orca.list_steps()
injectables = orca.list_injectables()
broadcasts = orca.list_broadcasts()
return jsonify(
tables=tables, columns=cols, steps=steps, injectables=injectables,
broadcasts=broadcasts)
import orca
import numpy as np
import pandas as pd
import zipfile
# reading data directly from zipfiles is nice, but it doesn't work with symlinks (on my
# Mac at least) - is there a workaround?
# data documentation: https://berkeley.app.box.com/notes/282712547032
# Set data directory
d = 'data/'
if 'data_directory' in orca.list_injectables():
d = orca.get_injectable('data_directory')
############################################################
# Tables from MTC Bay Area UrbanSim
@orca.table(cache=True)
def parcels():
df = pd.read_csv(
# d + 'mtc_data_platform_format_7-6-18/' + 'parcel_attr.csv',
d + 'parcels_with_nodes.csv',
index_col='primary_id',
dtype={
'primary_id': int,
'block_id': str
def buildings_to_uc(new_buildings, year):
"""
Parameters
----------
new_buildings : pandas.DataFrame
DataFrame of buildings to export.
year : int
Simulation year
Returns
-------
None
"""
# Checking that building_id is index or exists as column
if (new_buildings.index.name != 'building_id') & ('building_id' not in new_buildings.columns):
print 'Index of buildings must be "building_id" or "building_id" column must exist. Skipping export-to-Urban-Canvas.'
return None
if 'building_id' not in new_buildings.columns:
new_buildings = new_buildings.reset_index()
# Urban Canvas database connection
conn_string = orca.get_injectable('conn_string')
if len(conn_string) == 0:
print 'A "conn_string" injectable must be registered and populated. Skipping export-to-Urban-Canvas.'
return None
if 'uc_conn' not in orca.list_injectables():
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable('uc_conn', conn)
orca.add_injectable('uc_cur', cur)
else:
conn = orca.get_injectable('uc_conn')
cur = orca.get_injectable('uc_cur')
def exec_sql_uc(query):
try:
cur.execute(query)
conn.commit()
except:
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable('uc_conn', conn)
orca.add_injectable('uc_cur', cur)
cur.execute(query)
conn.commit()
def get_val_from_uc_db(query):
try:
result = sql.read_frame(query, conn)
return result.values[0][0]
except:
conn=psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable('uc_conn', conn)
orca.add_injectable('uc_cur', cur)
result = sql.read_frame(query, conn)
result2 = sql.read_frame("select column_name from Information_schema.columns where table_name like 'building' ", conn)
print result2
return result.values[0][0]
max_bid = get_val_from_uc_db("select max(building_id) FROM building where building_id<100000000;")
new_buildings.building_id = np.arange(max_bid+1, max_bid+1+len(new_buildings))
if 'projects_num' not in orca.list_injectables():
exec_sql_uc("INSERT INTO scenario(id, name, type) select nextval('scenario_id_seq'), 'Run #' || cast(currval('scenario_id_seq') as character varying), 1;")
nextval = get_val_from_uc_db("SELECT MAX(ID) FROM SCENARIO WHERE ID < 100000;")
exec_sql_uc("INSERT INTO scenario(id, name, type) select nextval('scenario_id_seq'), 'Run #' || cast(currval('scenario_id_seq') as character varying), 1;")
nextval = get_val_from_uc_db("SELECT MAX(ID) FROM SCENARIO WHERE ID < 1000000;")
orca.add_injectable('projects_num', nextval)
exec_sql_uc("INSERT INTO scenario_project(scenario, project) VALUES(%s, 1);" % nextval)
exec_sql_uc("INSERT INTO scenario_project(scenario, project) VALUES(%s, %s);" % (nextval, nextval))
else:
nextval = orca.get_injectable('projects_num')
nextval = '{'+str(nextval)+ '}'
new_buildings['projects'] = nextval
valid_from = '{'+ str(year) + '-1-1}'
new_buildings['valid_from'] = valid_from
print 'Exporting %s buildings to Urban Canvas database for project %s and year %s.' % (len(new_buildings),nextval,year)
output = cStringIO.StringIO()
new_buildings.to_csv('buildings_for_eddie.csv')
new_buildings.to_csv(output, sep='\t', header=False, index=False)
output.seek(0)
cur.copy_from(output, 'building', columns=tuple(new_buildings.columns.values.astype('U').tolist()))
test = pd.read_sql("select projects from building where (year_built>2010)", conn)
print test
conn.commit()
def list_injectables():
"""
List all registered injectables.
"""
return jsonify(injectables=orca.list_injectables())
def buildings_to_uc(new_buildings, year):
"""
Parameters
----------
new_buildings : pandas.DataFrame
DataFrame of buildings to export.
year : int
Simulation year
Returns
-------
None
"""
# Checking that building_id is index or exists as column
if (new_buildings.index.name != "building_id") & ("building_id" not in new_buildings.columns):
print 'Index of buildings must be "building_id" or "building_id" column must exist. Skipping export-to-Urban-Canvas.'
return None
if "building_id" not in new_buildings.columns:
new_buildings = new_buildings.reset_index()
# Urban Canvas database connection
conn_string = orca.get_injectable("conn_string")
if len(conn_string) == 0:
print 'A "conn_string" injectable must be registered and populated. Skipping export-to-Urban-Canvas.'
return None
if "uc_conn" not in orca.list_injectables():
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable("uc_conn", conn)
orca.add_injectable("uc_cur", cur)
else:
conn = orca.get_injectable("uc_conn")
cur = orca.get_injectable("uc_cur")
def exec_sql_uc(query):
try:
cur.execute(query)
conn.commit()
except:
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable("uc_conn", conn)
orca.add_injectable("uc_cur", cur)
cur.execute(query)
conn.commit()
def get_val_from_uc_db(query):
try:
result = sql.read_frame(query, conn)
return result.values[0][0]
except:
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable("uc_conn", conn)
orca.add_injectable("uc_cur", cur)
result = sql.read_frame(query, conn)
return result.values[0][0]
max_bid = get_val_from_uc_db("select max(building_id) FROM building where building_id<100000000;")
new_buildings.building_id = np.arange(max_bid + 1, max_bid + 1 + len(new_buildings))
if "projects_num" not in orca.list_injectables():
exec_sql_uc(
"INSERT INTO scenario(id, name, type) select nextval('scenario_id_seq'), 'Run #' || cast(currval('scenario_id_seq') as character varying), 1;"
)
nextval = get_val_from_uc_db("SELECT MAX(ID) FROM SCENARIO WHERE ID < 1000000;")
orca.add_injectable("projects_num", nextval)
exec_sql_uc("INSERT INTO scenario_project(scenario, project) VALUES(%s, 1);" % nextval)
exec_sql_uc("INSERT INTO scenario_project(scenario, project) VALUES(%s, %s);" % (nextval, nextval))
else:
nextval = orca.get_injectable("projects_num")
nextval = "{" + str(nextval) + "}"
new_buildings["projects"] = nextval
valid_from = "{" + str(year) + "-1-1}"
new_buildings["valid_from"] = valid_from
new_buildings["valid_to"] = "{infinity}"
print "Exporting %s buildings to Urban Canvas database for project %s and year %s." % (
len(new_buildings),
nextval,
year,
)
output = cStringIO.StringIO()
new_buildings.to_csv(output, sep="\t", header=False, index=False)
output.seek(0)
cur.copy_from(output, "building", columns=tuple(new_buildings.columns.values.tolist()))
conn.commit()
def test_injectable_list(expected_injectables):
print(orca.list_injectables())
assert orca.list_injectables() == expected_injectables
def list_injectables():
"""
List all registered injectables.
"""
return jsonify(injectables=orca.list_injectables())
def buildings_to_uc(new_buildings, year):
"""
Parameters
----------
new_buildings : pandas.DataFrame
DataFrame of buildings to export.
year : int
Simulation year
Returns
-------
None
"""
# Checking that building_id is index or exists as column
if (new_buildings.index.name !=
'building_id') & ('building_id' not in new_buildings.columns):
print 'Index of buildings must be "building_id" or "building_id" column must exist. Skipping export-to-Urban-Canvas.'
return None
if 'building_id' not in new_buildings.columns:
new_buildings = new_buildings.reset_index()
# Urban Canvas database connection
conn_string = orca.get_injectable('conn_string')
if len(conn_string) == 0:
print 'A "conn_string" injectable must be registered and populated. Skipping export-to-Urban-Canvas.'
return None
if 'uc_conn' not in orca.list_injectables():
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable('uc_conn', conn)
orca.add_injectable('uc_cur', cur)
else:
conn = orca.get_injectable('uc_conn')
cur = orca.get_injectable('uc_cur')
def exec_sql_uc(query):
try:
cur.execute(query)
conn.commit()
except:
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable('uc_conn', conn)
orca.add_injectable('uc_cur', cur)
cur.execute(query)
conn.commit()
def get_val_from_uc_db(query):
try:
result = sql.read_frame(query, conn)
return result.values[0][0]
except:
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
orca.add_injectable('uc_conn', conn)
orca.add_injectable('uc_cur', cur)
result = sql.read_frame(query, conn)
return result.values[0][0]
max_bid = get_val_from_uc_db(
"select max(building_id) FROM building where building_id<100000000;")
new_buildings.building_id = np.arange(max_bid + 1,
max_bid + 1 + len(new_buildings))
if 'projects_num' not in orca.list_injectables():
exec_sql_uc(
"INSERT INTO scenario(id, name, type) select nextval('scenario_id_seq'), 'Run #' || cast(currval('scenario_id_seq') as character varying), 1;"
)
nextval = get_val_from_uc_db(
"SELECT MAX(ID) FROM SCENARIO WHERE ID < 100000;")
orca.add_injectable('projects_num', nextval)
exec_sql_uc(
"INSERT INTO scenario_project(scenario, project) VALUES(%s, 1);" %
nextval)
exec_sql_uc(
"INSERT INTO scenario_project(scenario, project) VALUES(%s, %s);" %
(nextval, nextval))
else:
nextval = orca.get_injectable('projects_num')
nextval = '{' + str(nextval) + '}'
new_buildings['projects'] = nextval
valid_from = '{' + str(year) + '-1-1}'
new_buildings['valid_from'] = valid_from
print 'Exporting %s buildings to Urban Canvas database for project %s and year %s.' % (
len(new_buildings), nextval, year)
output = cStringIO.StringIO()
new_buildings.to_csv(output, sep='\t', header=False, index=False)
output.seek(0)
cur.copy_from(output,
'building',
columns=tuple(new_buildings.columns.values.tolist()))
conn.commit()
