import pandas as pd
from caeser import utils
import geopandas as gpd
from config import cnx_params
engine = utils.connect(**cnx_params.blight)
req = pd.read_sql("""select geoid, c.wkb_geometry, sum(numreqs) totreqs
from geography.cen_bg_2016 c
join (select parcelid, numreqs, wkb_geometry
from sca_parcels join combined_table on parcelid = parid
where reported_date >= '2012-01-01' and
reported_date < '2015-01-01') p
on st_within(st_centroid(p.wkb_geometry),
c.wkb_geometry)
group by geoid, c.wkb_geometry""", engine)
par = pd.read_sql("""select parid, reported_date, startyr, geoid
from combined_table
join sca_parcels p on parcelid = parid
join geography.cen_bg_2016 c on
st_within(st_centroid(p.wkb_geometry),
c.wkb_geometry)""", engine)
bg =gpd.read_postgis("""select * from geography.cen_bg_2016""", engine,
'wkb_geometry', 2274)
os.chdir('/home/nate/sharedworkspace/Data/Assessor')
years = ['2012','2013', '2014', '2015']
sales = pd.read_csv('2016/SALES.txt')
'''
Created on Feb 25, 2014
@author: nfergusn
'''
import psycopg2 as psql
import os
sys.path.append('$HOME/source')
from caeser import utils
params = utils.connection_info('localhost', db='db')
engine = utils.connect(**params)
db = psql.connect(params)
cursor = db.cursor()
select = """select table_name from information_schema.tables where table_schema = 'tiger'"""
index = """create index idx_{0}_geom on tiger.{0} using gist(geom);"""
cursor.execute(select)
tables = cursor.fetchall()
def create_index():
index = """create index idx_{0}_geom on tiger.{0} using gist(geom);"""
for t in tables:
print t[0]
"""
"""
import pandas as pd
import numpy as np
import os
from caeser import utils
from config import cnx_params
import matplotlib.pyplot as plt
from sklearn import linear_model
from sklearn.model_selection import cross_val_predict
from sklearn.metrics import mean_squared_error, r2_score
engine_blight = utils.connect(**cnx_params.blight)
engine_wwl = utils.connect(**cnx_params.wwl_2017)
os.chdir("/home/nate/dropbox-caeser/Data/MIDT/downtown_businesses")
q_bus_info = (
"select bus_name, address, b.name district, "
"regexp_replace(msg, '- ', '') land_use, livunit "
"from "
"(select bus_name, match_addr address, msg, livunit, l.wkb_geometry "
"from sca_parcels p, sca_pardat, sca_aedit, geography.bp_business_licenses l "
"where parcelid = parid "
"and (tble = 'PARDAT' and fld = 'LUC') "
"and val = luc "
"and st_within(l.wkb_geometry, p.wkb_geometry)) l, "
"(select * from geography.boundaries b "
"where name in ('CBID', 'The Core', 'Parkways', 'Main Street Mall')) b "
"where st_within(l.wkb_geometry, b.wkb_geometry) ")
bus_info = pd.read_sql(q_bus_info, engine_blight)