if grouper == 'taz': df[grouper] = df[grouper].astype(int) df['count'] = 1 summary = df.groupby(grouper).sum() if grouper == 'taz': summary['residential_units_target'] = targetunits taz_df = summary output_tablename = 'summary_{}'.format(grouper) df_to_db(summary, output_tablename, schema=loader.tables.public) parcel_output_dir = loader.get_path('out/regeneration/summaries/parcels') config = load_config() db_config = dict(config.items('database')) if 'taz_id' in db_to_df("SELECT column_name FROM information_schema.columns WHERE table_name='parcel'").column_name.values: exec_sql("ALTER TABLE parcel RENAME COLUMN taz_id to zone_id;") if 'parcel_acres' in db_to_df("SELECT column_name FROM information_schema.columns WHERE table_name='parcel'").column_name.values: exec_sql("ALTER TABLE parcel RENAME COLUMN parcel_acres to acres;") ## Export parcel shapefile to output directory os.system('pgsql2shp -f "%s" -h %s -u %s -P %s %s parcel' % (parcel_output_dir, db_config['host'], db_config['user'], db_config['password'], db_config['database'])) ## Export buildings as csv building_output_path = loader.get_path('out/regeneration/summaries/buildings.csv') buildings = db_to_df('select * from building').set_index('building_id') buildings.to_csv(building_output_path)
import os from spandex import TableLoader from spandex.io import exec_sql #spandex localhost exec_sql func from spandex.utils import load_config import psycopg2 loader = TableLoader() ### UrbanCanvas ## spandex localhost db config db_config = dict(load_config().items('database')) ## UrbanCanvas db config urbancanvas_db_config = {'database': 'sandag', 'host': 'urbancanvas.cp2xwchuariu.us-west-2.rds.amazonaws.com', 'password': '******', 'port': '5432', 'user': '******'} ## if 'loading' schema not on localhost db, create. This schema is for tables to load to UrbanCanvas exec_sql("CREATE SCHEMA IF NOT EXISTS loading;") #UrbanCanvas exec_sql func, for executing sql on UrbanCanvas database def exec_sql2(query): print query conn_string = "host=urbancanvas.cp2xwchuariu.us-west-2.rds.amazonaws.com dbname='sandag' user='******' password='******' port=5432" import psycopg2 conn=psycopg2.connect(conn_string)
### UrbanCanvas import os from spandex import TableLoader from spandex.io import exec_sql # spandex localhost exec_sql func from spandex.utils import load_config import psycopg2 loader = TableLoader() ## spandex localhost db config db_config = dict(load_config().items("database")) ## UrbanCanvas db config urbancanvas_db_config = { "database": "mtc", "host": "67.225.185.54", "password": "******", "port": "5432", "user": "******", } ## if 'loading' schema not on localhost db, create. This schema is for tables to load to UrbanCanvas exec_sql("CREATE SCHEMA IF NOT EXISTS loading;") # UrbanCanvas exec_sql func, for executing sql on UrbanCanvas database def exec_sql2(query): print query conn_string = "host=67.225.185.54 dbname='mtc' user='******' password='******' port=5432" import psycopg2
### UrbanCanvas import os from spandex import TableLoader from spandex.io import exec_sql #spandex localhost exec_sql func from spandex.utils import load_config import psycopg2 loader = TableLoader() ## spandex localhost db config db_config = dict(load_config().items('database')) ## UrbanCanvas db config urbancanvas_db_config = { 'database': 'mtc', 'host': '67.225.185.54', 'password': '******', 'port': '5432', 'user': '******' } ## if 'loading' schema not on localhost db, create. This schema is for tables to load to UrbanCanvas exec_sql("CREATE SCHEMA IF NOT EXISTS loading;") #UrbanCanvas exec_sql func, for executing sql on UrbanCanvas database def exec_sql2(query): print query conn_string = "host=67.225.185.54 dbname='mtc' user='******' password='******' port=5432" import psycopg2
if grouper == 'taz': df[grouper] = df[grouper].astype(int) df['count'] = 1 summary = df.groupby(grouper).sum() if grouper == 'taz': summary['residential_units_target'] = targetunits taz_df = summary output_tablename = 'summary_{}'.format(grouper) df_to_db(summary, output_tablename, schema=loader.tables.public) parcel_output_dir = loader.get_path('out/regeneration/summaries/parcels') config = load_config() db_config = dict(config.items('database')) if 'taz_id' in db_to_df( "SELECT column_name FROM information_schema.columns WHERE table_name='parcel'" ).column_name.values: exec_sql("ALTER TABLE parcel RENAME COLUMN taz_id to zone_id;") if 'parcel_acres' in db_to_df( "SELECT column_name FROM information_schema.columns WHERE table_name='parcel'" ).column_name.values: exec_sql("ALTER TABLE parcel RENAME COLUMN parcel_acres to acres;") ## Export parcel shapefile to output directory os.system('pgsql2shp -f "%s" -h %s -u %s -P %s %s parcel' % (parcel_output_dir, db_config['host'], db_config['user'], db_config['password'], db_config['database']))