from IPython.display import Image
Image('diagrams/banner.png')

from IPython.display import Image
Image('diagrams/banner2.png')

%%bash
tree data

import logging
logging.basicConfig()
logger = logging.getLogger('spandex')
logger.setLevel(logging.INFO)

%%bash
cat spandex.cfg

from spandex import TableLoader


# Connect to database specified in configuration file.
loader = TableLoader(config_filename='spandex.cfg')

# Import the shapefiles.
# Automatically determines the projection and attribute encoding.
loader.load_shp_map({'bg':      'bg.shp',
                     'parcels': 'parcels.shp',
                     'water':   'water.shp'})

# Asssign shorter variable names to our tables for convenience.
bg = loader.tables.public.bg
parcels = loader.tables.public.parcels
water = loader.tables.public.water

from spandex.io import db_to_df
print(db_to_df.__doc__)

# Census Block Groups
db_to_df(bg).head()

# Parcels
parcels_df = db_to_df(parcels)
parcels_df.head()

# Multiple records have the same parcel ID.
parcels_df[parcels_df.parc_py_id.duplicated()].head()

# Bodies of Water
db_to_df(water.geom).head()

from spandex import spatialtoolz

# Reproject to a consistent projection.
print(spatialtoolz.conform_srids.__doc__)
print("Reprojecting to project SRID: {}".format(loader.srid))
spatialtoolz.conform_srids(loader.srid)

# Find overlapping geometries.
print(spatialtoolz.geom_overlapping.__doc__)
spatialtoolz.geom_overlapping(parcels, key_name='parc_py_id',
                              output_table_name='parcels_overlapping')

from IPython.display import Image
Image('spatialtoolz/overlapping.png')

# Find unfilled geometries (interior rings).
print(spatialtoolz.geom_unfilled.__doc__)
spatialtoolz.geom_unfilled(parcels, output_table_name='parcels_unfilled')

from IPython.display import Image
Image('spatialtoolz/unfilled.png')

# Tag parcels with block group FIPS code.
print(spatialtoolz.tag.__doc__)
spatialtoolz.tag(parcels, 'bg_id', bg, 'geoid')

from IPython.display import Image
Image('spatialtoolz/tag.png')

# Calculate proportion of parcel overlapped by water.
print(spatialtoolz.proportion_overlap.__doc__)
spatialtoolz.proportion_overlap(parcels, water, 'proportion_water')

from IPython.display import Image
Image('spatialtoolz/overlap.png')

# Trim away parcel regions that are overlapped by water. 
print(spatialtoolz.trim.__doc__)
spatialtoolz.trim(parcels.geom, water.geom)

from IPython.display import Image
Image('spatialtoolz/trim.png')

import numpy as np
import pandas as pd
import urbansim.sim.simulation as sim

from spandex import TableLoader, TableFrame
from spandex.io import df_to_db, db_to_df
from spandex.sim import column


# Connect to database specified in configuration file.
loader = TableLoader(config_filename='spandex.cfg')

# Asssign shorter variable names to our tables for convenience.
bg = loader.tables.public.bg
parcels = loader.tables.public.parcels
water = loader.tables.public.water

# Register read-only input parcel table with UrbanSim simulation framework.
parcels_in = TableFrame(parcels, index_name='gid')
sim.add_table('parcels_in', parcels_in, copy=False)

# Register intermediate parcel table.
@sim.table()
def parcels_in2(parcels_in):
    return pd.DataFrame(index=parcels_in.index)

# Register output parcel table.
@sim.table()
def parcels_out(parcels_in):
    return pd.DataFrame(index=parcels_in.parc_py_id)

# Specify default output table.
def out(*args, **kwargs):
    return column(table_name='parcels_out', groupby=parcels_in.parc_py_id,
                  *args, **kwargs)

@out(astype=float, agg='sum')
def building_sqft(res_sqft='parcels_in.tla',
                  nonres_sqft='parcels_in.bldg_sqft',
                  res='parcels_in2.is_residential'):
    return pd.concat([res_sqft[res], nonres_sqft[~res]])


@out(astype=float, agg='sum')
def improvement_value(value='parcels_in.imp_val'):
    return value


@out(astype=float, agg='sum')
def land_value(value='parcels_in.land_value'):
    return value


@out(fillna=-1, astype=int, agg='median')
def year_built(res_year='parcels_in.yr_hs_blt',
               nonres_year='parcels_in.yr_built',
               res='parcels_in2.is_residential'):
    year = pd.concat([res_year[res], nonres_year[~res]])
    return year


@column('parcels_in2', fillna=-1, astype=int)
def land_use_type_id(use_code='parcels_in.use_code'):
    return use_code


@out(fillna=-1, astype=int, agg='median')
def land_use_type_id(use_code='parcels_in.use_code'):
    return use_code


@out(astype=str, agg='first')
def apn(apn='parcels_in.apn'):
    return apn


@out()
def county_id():
    return 13
    

@out(astype=int)
def non_residential_sqft(sqft='parcels_out.building_sqft',
                         nonres='parcels_out.is_nonresidential'):
    return sqft[nonres].fillna(0)


@column('parcels_in2', astype=bool)
def is_residential(use_code='parcels_in2.land_use_type_id'):
    residential_codes = range(10, 30)
    return use_code.isin(residential_codes)


@out(astype=bool)
def is_residential(use_code='parcels_out.land_use_type_id'):
    residential_codes = range(10, 30)
    return use_code.isin(residential_codes)


@out(astype=bool)
def is_nonresidential(use_code='parcels_out.land_use_type_id'):
    nonresidential_codes = range(30, 100)
    return use_code.isin(nonresidential_codes)

@sim.model()
def export(parcels_out):
    df_to_db(parcels_out.to_frame(), 'parcels_out')

# Run the export back to the database.
sim.run(['export'])

db_to_df(loader.tables.public.parcels_out, index_name='parc_py_id').head()

from IPython.display import SVG
SVG('diagrams/attributes.svg')

from IPython.display import Image
Image('usui/usui.png')