Python Script to Create Database for Zone Model System

A Python script that contains all the definitions of tables, and can create a new database and all the tables, is provided below. The class definitions correspond to individual tables, and define the fields, their types, and primary and foreign key relationships. Note that you can change the database server to any of the platforms supported by SQLAlchemy. This script depends on packages that are provided in the current development version of UrbanSim.

Copy and paste the script below into a programming editor like Scite, and save it with a .py extension, like 'create_parcel_database.py'. Edit the lines in the comment section to identify the database platform you prefer, and make sure only one line is uncommented. You can then run it from the command line, with 'python create_parcel_database.py'. If you are new to database management or want to try things out quickly, use the sqlite option. We have not tested its performance on large databases, so cannot recommend it for large-scale applications. MySQL and Postgres are likely to be more robust options for large applications, and Postgres also has the advantage of a mature GIS extension called PostGIS.

from elixir import *

# Select the database connection by (un)commenting and adapting the following options
######################
metadata.bind = "sqlite:////Users/pwaddell/sqlite/sample_zone.db"
#metadata.bind = "postgres://account:password@localhost/sample_zone"
#metadata.bind = "mysql://account:password@localhost/sample_zone"
######################

metadata.bind.echo = True

class AnnualEmploymentControlTotal(Entity):
    using_options(tablename='annual_employment_control_totals')
    year = Field(Integer)
    sector = ManyToOne('EmploymentSector', colname='sector_id')
    home_based_status = Field(Integer)
    number_of_jobs = Field(Integer)

class AnnualHouseholdControlTotal(Entity):
    using_options(tablename='annual_household_control_totals')
    year = Field(Integer)
    total_number_of_households = Field(Integer)

class AnnualRelocationRatesForHousehold(Entity):
    using_options(tablename='annual_relocation_rates_for_households')
    age_of_head_min = Field(Integer)
    age_of_head_max = Field(Integer)
    income_min = Field(Integer)
    income_max = Field(Integer)
    probability_of_relocating = Field(Float)

class AnnualJobRelocationRates(Entity):
    using_options(tablename='annual_job_relocation_rates')
    sector = ManyToOne('EmploymentSector', colname='sector_id')
    job_relocation_probability = Field(Float)

class Building(Entity):
    using_options(tablename='buildings')
    building_id = Field(Integer, primary_key=True)
    land_area = Field(Integer)
    non_residential_sqft = Field(Integer)
    non_residential_sqft_capacity = Field(Integer)
    residential_units = Field(Integer)
    residential_units_capacity = Field(Integer)
    sqft_per_unit = Field(Integer)
    year_built = Field(Integer)
    average_value_per_unit = Field(Integer)
    zone = ManyToOne('Zone', colname='zone_id')
    building_type = ManyToOne('BuildingType', colname='building_type_id')

class BuildingSqftPerJob(Entity):
    using_options(tablename='building_sqft_per_job')
    building_type_id = Field(Integer, primary_key=True)
    zone_id = Field(Integer, primary_key=True)
    building_sqft_per_job = Field(Integer)
    
class BuildingType(Entity):
    using_options(tablename='building_types')
    building_type_id = Field(Integer, primary_key=True)
    is_residential = Field(Integer)
    building_type_name = Field(String(20))

class City(Entity):
    using_options(tablename='cities')
    city_id = Field(Integer, primary_key=True)
    city_name = Field(String(25))

class County(Entity):
    using_options(tablename='counties')
    county_id = Field(Integer, primary_key=True)
    county_name = Field(String(20))
    county_fips = Field(String(10))

class DevelopmentConstraint(Entity):
    using_options(tablename='development_constraints')
    development_constraint_id = Field(Integer, primary_key=True)
    zone_id = Field(Integer)
    building_type_id = Field(Integer)
    residential_units_capacity = Field(Integer)
    non_residential_sqft_capacity = Field(Integer)

class DevelopmentEventHistory(Entity):
    using_options(tablename='development_event_history')
    zone = ManyToOne('Zone', colname='zone_id')
    building_type_id = Field(Integer, primary_key=True)
    scheduled_year = Field(Integer)
    residential_units = Field(Integer)
    non_residential_sqft = Field(Integer)
    change_type = Field(String(1))

class EmploymentAdHocSectorGroupDefinition(Entity):
    using_options(tablename='employment_adhoc_sector_group_definitions')
    sector_id = Field(Integer, primary_key=True)
    group = ManyToOne('EmploymentAdHocSectorGroup', colname='group_id')

class EmploymentAdHocSectorGroup(Entity):
    using_options(tablename='employment_adhoc_sector_groups')
    group_id = Field(Integer, primary_key=True)
    name = Field(String(20))

class EmploymentSector(Entity):
    using_options(tablename='employment_sectors')
    sector_id = Field(Integer, primary_key=True)
    name = Field(String(20))

class Faz(Entity):
    using_options(tablename='fazes')
    faz_id = Field(Integer, primary_key=True)
    large_area = ManyToOne('LargeArea', colname='large_area_id')

class HomeBasedStatus(Entity):
    using_options(tablename='home_based_status')
    home_based_status = Field(Integer, primary_key=True)
    name = Field(String(20))

class Household(Entity):
    using_options(tablename='households')
    household_id = Field(Integer, primary_key=True)
    building = ManyToOne('Building', colname='building_id')
    persons = Field(Integer)
    income = Field(Integer)
    age_of_head = Field(Integer)
    race = ManyToOne('RaceName', colname='race_id')
    workers = Field(Integer)
    children = Field(Integer)
    cars = Field(Integer)

class HouseholdCharacteristicsForHT(Entity):
    using_options(tablename='household_characteristics_for_ht')
    characteristic = Field(String(20))
    min = Field(Integer)
    max = Field(Integer)

class HouseholdsForEstimation(Entity):
    using_options(tablename='households_for_estimation')
    household_id = Field(Integer, primary_key=True)
    building = ManyToOne('Building', colname='building_id')
    persons = Field(Integer)
    income = Field(Integer)
    age_of_head = Field(Integer)
    race = ManyToOne('RaceName', colname='race_id')
    workers = Field(Integer)
    children = Field(Integer)
    cars = Field(Integer)

class Job(Entity):
    using_options(tablename='jobs')
    job_id = Field(Integer, primary_key = True)
    building = ManyToOne('Building', colname='building_id')
    home_based_status = Field(Integer)
    sector = ManyToOne('EmploymentSector', colname='sector_id')

class JobsForEstimation(Entity):
    using_options(tablename='jobs_for_estimatiion')
    job_id = Field(Integer, primary_key = True)
    building = ManyToOne('Building', colname='building_id')
    home_based_status = Field(Integer)
    sector = ManyToOne('EmploymentSector', colname='sector_id')

class LargeArea(Entity):
    using_options(tablename='large_areas')
    large_area_id = Field(Integer, primary_key=True)
    large_area_name = Field(String(50))
    county = ManyToOne('County', colname='county_id')

class PlanType(Entity): #Needed for zones?
    using_options(tablename='plan_types')
    plan_type_id = Field(Integer, primary_key=True)
    plan_type_name = Field(String(50))

class RaceName(Entity):
    using_options(tablename='race_names')
    race_id = Field(Integer, primary_key=True)
    minority = Field(Integer)
    name = Field(String(20))

class Refinement(Entity):
    refinement_id = Field(Integer, primary_key=True)
    using_options(tablename='refinements')
    transaction_id = Field(Integer, primary_key=True)
    agent_dataset = Field(String(25))
    agent_expression = Field(String(200))
    amount = Field(Integer)
    location_capacity_attribute= (50)
    location_expression = Field(String(200))
    transaction_id = Field(Integer)
    year = Field(Integer)

class ScheduledDevelopmentEvents(Entity):
    using_options(tablename='scheduled_development_events')
    id = Field(Integer, primary_key=True)
    year = Field(Integer)
    action = Field(String(20))
    attribute = Field(String(25))  #optional, required if action is 'set_value', 'add_value', 'subtract_value', or multiply_value
    amount = Field(Integer)
    #fields to identify buildings(location and building type) for events
    building = ManyToOne('Building', colname='building_id')
    ## alternatively, use primary or computed attributes of buildings
    #zone = ManyToOne('Zone', colname='zone_id')
    #building_type = ManyToOne('Building_type', colname='building_type_id')

class ScheduledEmploymentEvents(Entity):
    using_options(tablename='scheduled_employment_events')
    id = Field(Integer, primary_key=True)
    year = Field(Integer)
    action = Field(String)
    attribute = Field(String)   #optional, required if action is 'set_value', 'add_value', 'subtract_value', or multiply_value
    amount = Field(Integer)
    #fields to identify jobs for events 
    sector = ManyToOne('EmploymentSector', colname='sector_id')
    ##alternatively, use primary or computed attributes of jobs
    # building = ManyToOne('Building', colname='building_id')

class TargetVacancy(Entity):
    using_options(tablename='target_vacancies')
    year = Field(Integer, primary_key=True)
    building_type = ManyToOne('BuildingType', colname='building_type_id')
    target_vacancy_rate = Field(Float)

class TravelData(Entity):
    using_options(tablename='travel_data')
    from_zone_id = Field(Integer, primary_key=True)
    to_zone_id = Field(Integer, primary_key=True)
    am_single_vehicle_to_work_travel_time = Field(Integer)
    #Enter any additional columns needed from travel model skims

class Zone(Entity):
    using_options(tablename='zones')
    zone_id = Field(Integer, primary_key=True)
    city = ManyToOne('City', colname='city_id')
    county = ManyToOne('County', colname='county_id')
    faz = ManyToOne('Faz', colname='faz_id')


setup_all()
create_all()

-- PaulWaddell - 20 May 2010