Ejemplo n.º 1
0
def loader(request):
    """Recreate sample schema from shapefiles and tear down when done."""
    # Configure TableLoader to use directory containing sample shapefiles.
    root_path = os.path.dirname(__file__)
    data_path = os.path.join(root_path, '../../test_data')
    loader = TableLoader(directory=data_path)

    # Recreate PostgreSQL sample schema.
    with loader.database.cursor() as cur:
        cur.execute("""
            CREATE EXTENSION IF NOT EXISTS postgis;
            DROP SCHEMA IF EXISTS sample CASCADE;
            CREATE SCHEMA sample;
        """)
    loader.database.refresh()

    # Load all shapefiles in test data directory.
    for filename in os.listdir(data_path):
        file_root, file_ext = os.path.splitext(filename)
        if file_ext.lower() == '.shp':
            shp_path = os.path.join(data_path, filename)
            table_name = 'sample.' + file_root
            loader.load_shp(shp_path, table_name)

    # Reproject all non-conforming SRIDs into project SRID.
    conform_srids(loader.srid, schema=loader.tables.sample)

    # Tear down sample schema when done.
    def teardown():
        with loader.database.cursor() as cur:
            cur.execute("DROP SCHEMA sample CASCADE;")
    request.addfinalizer(teardown)

    return loader
Ejemplo n.º 2
0
    'built/parcel/2010/son/PAR_PARCELS.shp',

    # Geometry type is MultiPolygonZM.
    #'staging.parcels_son_exlu':
    #'built/parcel/2010/son/parcels2010_son/Final2010exlu.shp',

    'staging.taz':
    'juris/reg/zones/taz1454.shp',
    
    'staging.establishment_points':
    'emp/micro/est10_gt1/est10_esri_gt1.shp',
}


# Install PostGIS and create staging schema.
loader = TableLoader()
with loader.database.cursor() as cur:
    cur.execute("""
        CREATE EXTENSION IF NOT EXISTS postgis;
        CREATE SCHEMA IF NOT EXISTS staging;
    """)
loader.database.refresh()

# Load shapefiles specified above to the project database.
loader.load_shp_map(shapefiles)

# Fix invalid geometries and reproject.
staging = loader.tables.staging
conform_srids(loader.srid, schema=staging, fix=True)

# Load county land use code mapping.
Ejemplo n.º 3
0
#Imports
import pandas as pd, numpy as np
import pandas.io.sql as sql
from spandex import TableLoader
from spandex.io import exec_sql, df_to_db
from spandex.targets import scaling as scl
import hashlib

#Connect to the database
loader = TableLoader()


def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)


##Load parcels as dataframes for the imputation
parcels = db_to_df('select * from parcels;')
parcels = parcels.set_index('gid')

#Standardize the res_type field
parcels.res_type[parcels.res_type.isnull()] = 'other'
parcels.res_type[parcels.res_type == ''] = 'other'
parcels.res_type[np.in1d(
    parcels.res_type,
    ['FLATS', 'APTS', 'CONDO', 'SRO', 'LIVEWORK', 'mixed'])] = 'multi'
parcels.res_type[parcels.res_type == 'SINGLE'] = 'single'

# Load TAZ residential unit control totals and other zonal targets.
Ejemplo n.º 4
0
import pandas as pd, numpy as np
import pandas.io.sql as sql
from spandex import TableLoader

loader = TableLoader()


def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)


# Read from database (public schema)
parcels = db_to_df('select * from parcels').set_index('parcel_id')
jobs = db_to_df('select * from jobs').set_index('job_id')
households = db_to_df('select * from households').set_index('household_id')
buildings = db_to_df('select * from buildings').set_index('building_id')
hh_controls = db_to_df(
    'select year, activity_id, luz_id, total_number_of_households from annual_household_control_totals'
)
pecas_prices = db_to_df(
    'select year, luz_id, development_type_id, price from pecas_prices')
assessor_transactions = db_to_df(
    'select * from assessor_transactions').set_index('building_id')
zoning = db_to_df('select * from zoning')
zoning_allowed_uses = db_to_df(
    'select zoning_id, development_type_id from zoning_allowed_uses')
fee_schedule = db_to_df(
    'select fee_schedule_id, development_type_id, development_fee_per_unit_space_initial from staging.fee_schedule'
)
import pandas as pd, numpy as np
import pandas.io.sql as sql
from spandex import TableLoader
from spandex.io import exec_sql,  df_to_db

#Connect to the database
loader = TableLoader()

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

def unit_choice(chooser_ids, alternative_ids, probabilities):
    """
    Have a set of choosers choose from among alternatives according
    to a probability distribution. Choice is binary: each
    alternative can only be chosen once.

    Parameters
    ----------
    chooser_ids : 1d array_like
        Array of IDs of the agents that are making choices.
    alternative_ids : 1d array_like
        Array of IDs of alternatives among which agents are making choices.
    probabilities : 1d array_like
        The probability that an agent will choose an alternative.
        Must be the same shape as `alternative_ids`. Unavailable
        alternatives should have a probability of 0.

    Returns
Ejemplo n.º 6
0
import zipfile
from spandex import TableLoader
from spandex.io import exec_sql
from spandex.spatialtoolz import conform_srids, tag
import pandas as pd, numpy as np

import pandas.io.sql as sql


def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)


loader = TableLoader()

# Download puma 2000 geometry zip files
for i in range(73):
    if i < 10:
        filename = 'p50%s_d00_shp.zip' % i
    else:
        filename = 'p5%s_d00_shp.zip' % i

    try:
        pumageom_file = urllib.URLopener()
        pumageom_file.retrieve(
            "http://www2.census.gov/geo/tiger/PREVGENZ/pu/p500shp/%s" %
            filename, os.path.join(loader.get_path('puma_geom'), filename))
        print 'Downloading %s' % filename
    except:
import pandas as pd, numpy as np
import pandas.io.sql as sql
from pandas.io.excel import read_excel
from spandex.io import exec_sql,  df_to_db
from spandex import TableLoader

loader = TableLoader()

##Read Redfin CSV and load to database
redfin_csv_path = loader.get_path('built/bldg/homeprices/redfin_03feb14.csv')
redfin = pd.read_csv(redfin_csv_path)
redfin.index.name = 'idx'
df_to_db(redfin, 'redfin', schema=loader.tables.staging)

##Lat/long to point geometry, with the right SRID
exec_sql("ALTER TABLE staging.redfin ADD COLUMN geom geometry;")
exec_sql("UPDATE staging.redfin SET geom = ST_GeomFromText('POINT(' || longitude || ' ' || latitude || ')',4326);")
exec_sql("CREATE INDEX redfin_gidx on staging.redfin using gist (geom);")
exec_sql("SELECT UpdateGeometrySRID('staging', 'redfin', 'geom', 2768);")
exec_sql("UPDATE staging.redfin SET geom = ST_TRANSFORM(ST_SetSRID(geom, 4326), 2768);")

##Append the unique parcel identifier to the Redfin records
exec_sql("alter table staging.redfin add gid integer default 0;")
exec_sql("update staging.redfin set gid = a.gid from parcels a where st_within(staging.redfin.geom, a.geom);")

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)
Ejemplo n.º 8
0
import os
import re
import zipfile
import urllib, urllib2
import pandas as pd, numpy as np
from bs4 import BeautifulSoup
from spandex import TableLoader

loader = TableLoader()

soup = BeautifulSoup(urllib2.urlopen("http://www2.census.gov/acs2013_5yr/pums/"))

tags = soup.find_all(href=re.compile("csv_h..\.zip"))
hpums_links = []
for t in tags:
    hpums_links.append(t['href'])
    
tags = soup.find_all(href=re.compile("csv_p..\.zip"))
ppums_links = []
for t in tags:
    ppums_links.append(t['href'])

pums_links = hpums_links + ppums_links
for pums_file in pums_links:
    print pums_file
    pums_file_dl = urllib.URLopener()
    pums_file_dl.retrieve("http://www2.census.gov/acs2013_5yr/pums/%s" % pums_file, 
                      os.path.join(loader.get_path('pums'), pums_file))

for pums_file in pums_links:
    filepath = os.path.join(loader.get_path('pums'), pums_file)
Ejemplo n.º 9
0
import pandas as pd, numpy as np
import pandas.io.sql as sql
from spandex import TableLoader

loader = TableLoader()

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

# Read from database (public schema)
parcels = db_to_df('select * from parcels').set_index('parcel_id')
jobs = db_to_df('select * from jobs').set_index('job_id')
households = db_to_df('select * from households').set_index('household_id')
buildings = db_to_df('select * from buildings').set_index('building_id')
hh_controls = db_to_df('select year, activity_id, luz_id, total_number_of_households from annual_household_control_totals')
pecas_prices = db_to_df('select year, luz_id, development_type_id, price from pecas_prices')
assessor_transactions = db_to_df('select * from assessor_transactions').set_index('building_id')
zoning = db_to_df('select * from zoning')
zoning_allowed_uses = db_to_df('select zoning_id, development_type_id from zoning_allowed_uses')
fee_schedule = db_to_df('select fee_schedule_id, development_type_id, development_fee_per_unit_space_initial from staging.fee_schedule')
parcel_fee_schedule = db_to_df('select parcelid as parcel_id, fee_schedule_id, portion from staging.parcel_fee_schedule')


# Remove uneccesary id columns appended by spandex
for df in [buildings, jobs, households, assessor_transactions, zoning]:
    if 'id' in df.columns:
        del df['id']
        
zoning = zoning.set_index('zoning_id')
Ejemplo n.º 10
0
import os
import pandas as pd
from spandex import TableLoader, TableFrame
from spandex.utils import load_config
from spandex.io import exec_sql, df_to_db
import pandas.io.sql as sql


def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)


# Build parcels TableFrame.
loader = TableLoader()
table = loader.database.tables.public.parcels
tf = TableFrame(table, index_col='gid')

# Load TAZ residential unit control totals.
taz_controls_csv = loader.get_path('hh/taz2010_imputation.csv')
targetunits = pd.read_csv(taz_controls_csv, index_col='taz1454')['targetunits']

# Get CSV output file directory.
output_dir = loader.get_path('out/regeneration/summaries')

# Generate summary CSV by county and TAZ.
for grouper in ['county_id', 'taz']:
    df = tf[[grouper, 'non_residential_sqft', 'residential_units']]
    df.dropna(subset=[grouper], inplace=True)
Ejemplo n.º 11
0
import os
import urllib
import zipfile
from spandex import TableLoader
from spandex.io import exec_sql
from spandex.spatialtoolz import conform_srids, tag
import pandas as pd, numpy as np

import pandas.io.sql as sql
def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

loader = TableLoader()

# Download puma 2000 geometry zip files
for i in range(73): 
    if i < 10:
        filename = 'p50%s_d00_shp.zip' % i
    else:
        filename = 'p5%s_d00_shp.zip' % i
    
    try:
        pumageom_file = urllib.URLopener()
        pumageom_file.retrieve("http://www2.census.gov/geo/tiger/PREVGENZ/pu/p500shp/%s" % filename, 
                          os.path.join(loader.get_path('puma_geom'), filename))
        print 'Downloading %s' % filename
    except:
        continue
import pandas as pd, numpy as np
import pandas.io.sql as sql
from pandas.io.excel import read_excel
from spandex.io import exec_sql,  df_to_db
from spandex import TableLoader

loader = TableLoader()

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

def csv_to_staging(path, table_name):
    """Loads csv to staging schema on database"""
    df = pd.read_csv(path)
    df.index.name = 'idx'
    print 'Loading %s.' % table_name
    df_to_db(df, table_name, schema=loader.tables.staging)
    
def field_in_table(field_name, table_name):
    """True if field in table else False"""
    return field_name in db_to_df("SELECT column_name FROM information_schema.columns  WHERE table_name='%s'" % table_name).column_name.values
    
def lat_long_to_point_geometry(tbl, schema, x_col = 'longitude', y_col = 'latitude', geom_field_name = 'geom', target_srid = 2768):
    """Creates point geometry on db table based on lat long fields"""
    print 'Creating point geometry from lat/long in table %s.' % tbl 
    if not field_in_table(geom_field_name, tbl):
        exec_sql("ALTER TABLE %s.%s ADD COLUMN %s geometry;" % (schema, tbl, geom_field_name))
    exec_sql("UPDATE %s.%s SET %s = ST_GeomFromText('POINT(' || %s || ' ' || %s || ')',4326);" % 
             (schema, tbl, geom_field_name, x_col, y_col))
Ejemplo n.º 13
0
    'staging.parcels':
    'space/parcel.shp',

    'staging.buildings':
    'space/building.shp',

    'staging.blocks':
    'space/tl_2010_06073_tabblock10.shp',

    'staging.sitespec':
    'scheduled/site_spec.shp',

}

# Install PostGIS and create staging schema.
loader = TableLoader()
with loader.database.cursor() as cur:
    cur.execute("""
        CREATE EXTENSION IF NOT EXISTS postgis;
        CREATE SCHEMA IF NOT EXISTS staging;
    """)
loader.database.refresh()

t = loader.tables
staging = loader.tables.staging


# Load shapefiles specified above to the project database.
loader.load_shp_map(shapefiles)

Ejemplo n.º 14
0
import pandas as pd
from spandex import TableLoader
import pandas.io.sql as sql

loader = TableLoader()

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

## Export to HDF5-  get path to output file
h5_path = loader.get_path('out/regeneration/summaries/bayarea_v3.h5')  ## Path to the output file

#Buildings
buildings = db_to_df('select * from building').set_index('building_id')
if 'id' in buildings.columns:
    del buildings['id']
buildings['building_type_id'] = 0
buildings.building_type_id[buildings.development_type_id == 1] = 1
buildings.building_type_id[buildings.development_type_id == 2] = 3
buildings.building_type_id[buildings.development_type_id == 5] = 12
buildings.building_type_id[buildings.development_type_id == 7] = 10
buildings.building_type_id[buildings.development_type_id == 9] = 5
buildings.building_type_id[buildings.development_type_id == 10] = 4
buildings.building_type_id[buildings.development_type_id == 13] = 8
buildings.building_type_id[buildings.development_type_id == 14] = 7
buildings.building_type_id[buildings.development_type_id == 15] = 9
buildings.building_type_id[buildings.development_type_id == 13] = 8
buildings.building_type_id[buildings.development_type_id == 17] = 6
buildings.building_type_id[buildings.development_type_id == 24] = 16
Ejemplo n.º 15
0
import numpy as np
import pandas as pd
import cStringIO
import psycopg2
import pandas.io.sql as sql
from spandex import TableLoader
loader = TableLoader()

conn_string = "host='urbancanvas.cp2xwchuariu.us-west-2.rds.amazonaws.com' dbname='sandag_testing' user='******' password='******' port=5432"
conn=psycopg2.connect(conn_string)
cur = conn.cursor()

def uc_db_to_df(query):
    return sql.read_frame(query, conn)
    
parcels = uc_db_to_df("select parcel_id, zoning_id, devtype_id as development_type_id from parcel "
                      "where projects = '{1}' and valid_from = '{-infinity}';").set_index('parcel_id')
buildings = uc_db_to_df("SELECT building_id, parcel_id, building_type_id as development_type_id, improvement_value, "
                        "residential_units, non_residential_sqft, stories, year_built, residential_sqft, "
                        "note FROM building where projects = '{1}' and valid_from = '{-infinity}';").set_index('building_id')
                        
# Put tables in HDF5
h5_path = loader.get_path('out/sandag.h5')
store = pd.HDFStore(h5_path)

del store['buildings']
store['buildings'] = buildings

p_prev = store.parcels.copy()
p_prev['zoning_id'] = parcels.zoning_id
p_prev['development_type_id'] = parcels.development_type_id
Ejemplo n.º 16
0
import os
import pandas as pd
from spandex import TableLoader, TableFrame
from spandex.utils import load_config
from spandex.io import exec_sql, df_to_db
import pandas.io.sql as sql

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

# Build parcels TableFrame.
loader = TableLoader()
table = loader.database.tables.public.parcels
tf = TableFrame(table, index_col='gid')

# Load TAZ residential unit control totals.
taz_controls_csv = loader.get_path('hh/taz2010_imputation.csv')
targetunits = pd.read_csv(taz_controls_csv, index_col='taz1454')['targetunits']

# Get CSV output file directory.
output_dir = loader.get_path('out/regeneration/summaries')

# Generate summary CSV by county and TAZ.
for grouper in ['county_id', 'taz']:
    df = tf[[grouper, 'non_residential_sqft', 'residential_units']]
    df.dropna(subset=[grouper], inplace=True)

    if grouper == 'taz':
        df[grouper] = df[grouper].astype(int)
Ejemplo n.º 17
0
#Imports
import pandas as pd, numpy as np
import pandas.io.sql as sql
from spandex import TableLoader
from spandex.io import exec_sql,  df_to_db
from spandex.targets import scaling as scl
import hashlib

#Connect to the database
loader = TableLoader()

def db_to_df(query):
    """Executes SQL query and returns DataFrame."""
    conn = loader.database._connection
    return sql.read_frame(query, conn)

##Load parcels as dataframes for the imputation
parcels = db_to_df('select * from parcels;')
parcels = parcels.set_index('gid')

#Standardize the res_type field
parcels.res_type[parcels.res_type.isnull()] = 'other'
parcels.res_type[parcels.res_type==''] = 'other'
parcels.res_type[np.in1d(parcels.res_type, ['FLATS', 'APTS', 'CONDO', 'SRO', 'LIVEWORK', 'mixed'])] = 'multi'
parcels.res_type[parcels.res_type=='SINGLE'] = 'single'

# Load TAZ residential unit control totals and other zonal targets.
taz_controls_csv = loader.get_path('hh/taz2010_imputation.csv')
targetunits = pd.read_csv(taz_controls_csv, index_col='taz1454')

taz_controls_csv2 = loader.get_path('hh/tazsumm_redfin.csv')
Ejemplo n.º 18
0
import os
import string
import subprocess

import numpy as np
import pandas as pd
from spandex import TableLoader, TableFrame
from spandex.io import df_to_db
import urbansim.sim.simulation as sim

import utils

loader = TableLoader()
staging = loader.tables.staging

## Assumptions.

# Use codes were classified manually because the assessor classifications
# are meant for property tax purposes. These classifications should be
# reviewed and revised.
res_codes = {
    'single': ['01', '51', '52', '53'],
    'multi':
    [string.zfill(i, 2) for i in range(2, 6) + range(7, 10) + range(89, 99)],
    'mixed': []
}
exempt_codes = []

## Register input tables.

tf = TableFrame(staging.parcels_smt, index_col='apn')
Ejemplo n.º 19
0
import os
import re
import zipfile
import urllib, urllib2
import pandas as pd, numpy as np
from bs4 import BeautifulSoup
from spandex import TableLoader

loader = TableLoader()

soup = BeautifulSoup(
    urllib2.urlopen("http://www2.census.gov/acs2013_5yr/pums/"))

tags = soup.find_all(href=re.compile("csv_h..\.zip"))
hpums_links = []
for t in tags:
    hpums_links.append(t['href'])

tags = soup.find_all(href=re.compile("csv_p..\.zip"))
ppums_links = []
for t in tags:
    ppums_links.append(t['href'])

pums_links = hpums_links + ppums_links
for pums_file in pums_links:
    print pums_file
    pums_file_dl = urllib.URLopener()
    pums_file_dl.retrieve(
        "http://www2.census.gov/acs2013_5yr/pums/%s" % pums_file,
        os.path.join(loader.get_path('pums'), pums_file))