USA Job Point Generator

import pandas as pd
import numpy as np
import psycopg2
import os, os.path
import itertools

#load the lodes dataset, setting id columns to strings
lodes = pd.read_csv('/path/all_lodes.csv', 
                    dtype = {'statefp10': str, 'countyfp10': str, 'tractce10': str, 'blockce': str, 'blockid10': str})

conn_string = "host='localhost' dbname='' user='' password='' port=5432"
conn=psycopg2.connect(conn_string)
cur = conn.cursor()

#select the state
state = '06'
#select the county
county = '041'
#name the table that will hold the job points
target_table = "marin_jobs"
#name the table that will hold the census blocks for the county
create_blocks = "marin_blocks"

#this is to run a block level generation of jobs for a marin county


lodes_test2 = lodes_test2[['blockid10','cns01','cns02','cns03','cns04','cns05','cns06','cns07','cns08','cns09','cns10','cns11','cns12','cns13','cns14','cns15','cns16','cns17','cns18','cns19','cns20']]
lodes_test2 = lodes_test2.reset_index(drop=True)

def jobs_table(x):
    'set the target table for the job point generation'
    
    print ('creating table {} to hold job points...'.format(target_table))
    
    cur.execute("drop table if exists {};". format(x))
    conn.commit()
    
    cur.execute("create table {} (blockid10 varchar(17), job_type varchar(10), the_geom geometry);" .format(x))
    conn.commit()
    
    print ('done')
    
jobs_table(target_table)


def blocks_table(x):
    'isolates the census blocks for the county you want to calculate job points'
    
    print ('creating table {} from the tiger geometries...'.format(create_blocks))
    
    cur.execute("drop table if exists {}".format(x))
    conn.commit()
    
    cur.execute("create table {} as (select * from tabblock where statefp10 = '{}' and countyfp10 = '{}');".format (x, state, county))
    conn.commit()
    
    cur.execute("alter table {} rename column geoid to geoid10".format(x))
    conn.commit()
    
    print ('done')

blocks_table(create_blocks)


def create_job_points():
    'iterate cell by cell, check if value > 0, then create points for appropriate census block with the right job type'
    
    print ('creating job points for state {}, county {}...'.format(state, county))
    
    columns = ['cns01','cns02','cns03','cns04','cns05','cns06','cns07','cns08',
               'cns09','cns10','cns11','cns12','cns13','cns14','cns15','cns16',
               'cns17','cns18','cns19','cns20']
    rows = range(0,len(lodes_test2))
    for r in rows:
        for i in columns:
            if lodes_test2[i][r] > 0:
                #print block
                block = str(lodes_test2['blockid10'][r])
                job = i
                job_x_block = (i + 'x' + block)
                num_job =  int(lodes_test2[i][r])
                
                cur.execute("drop table if exists {};" .format(job_x_block))
                conn.commit()
                
                cur.execute("""CREATE TABLE %s 
                (blockid10 varchar(17) DEFAULT '%s',
                job_type varchar(10) DEFAULT '%s',
                the_geom geometry);""" % (job_x_block, block, job))
                conn.commit()
                
                cur.execute("""INSERT INTO {}(the_geom)
                SELECT RandomPointsInPolygon(geom, {})
                FROM {} WHERE geoid10 = '{}';""" .format(job_x_block, num_job, create_blocks, block))
                conn.commit()
                
                cur.execute("insert into {} select * from {};" .format(target_table, job_x_block))
                conn.commit()
                
                cur.execute("drop table if exists {}" .format(job_x_block))
                conn.commit()
                
    print ('done')

create_job_points()

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s