def get_sidewalk_data(**kwargs):
"""Get sidewalk condition data from DB."""
sw_query = general.file_to_string('./sql/sidewalk_insp.sql', __file__)
sw_conn = MsSqlHook(mssql_conn_id='streets_cg_sql')
df = sw_conn.get_pandas_df(sw_query)
# Rename columns we're keeping
df = df.rename(columns={
'sap_id': 'seg_id',
'legacy_id': 'geojoin_id',
'inspectiondate': 'oci_date',
'rating': 'oci_desc',
'condition': 'oci'
})
df = df.drop(['cgLastModified',
'MaxInspect',
'MaxMod'],axis=1)
# Write csv
logging.info('Writing ' + str(df.shape[0]))
general.pos_write_csv(
df, cond_file, date_format=conf['date_format_ymd'])
return "Successfully wrote prod file"
def get_sidewalk_data(**kwargs):
"""Get sidewalk condition data from DB."""
sw_query = general.file_to_string('./sql/sidewalk_insp.sql', __file__)
sw_conn = MsSqlHook(mssql_conn_id='streets_cg_sql')
df = sw_conn.get_pandas_df(sw_query)
# Rename columns we're keeping
df = df.rename(
columns={
'sap_id': 'seg_id',
'legacy_id': 'geojoin_id',
'inspectiondate': 'oci_date',
'rating': 'oci_desc',
'condition': 'oci'
})
df = df.drop(['cgLastModified', 'MaxInspect', 'MaxMod'], axis=1)
# Write csv
logging.info('Writing ' + str(df.shape[0]))
general.pos_write_csv(df, cond_file, date_format=conf['date_format_ymd'])
return "Successfully wrote prod file"
def get_pts_violations():
"""Get violations from pts, creates temp file."""
logging.info('Retrieving PTS violations.')
db = cx_Oracle.connect(credentials)
sql = general.file_to_string('./sql/pts_sw.sql', __file__)
df = pd.read_sql_query(sql, db)
general.pos_write_csv(df, temp_file_pts, date_format='%Y-%m-%dT%H:%M:%S%z')
return "Successfully wrote {} records for dsd_pts violations file".format(
df.shape[0])
def get_active_businesses():
"""Query DB for 'Active Businesses' and save data to temp."""
logging.info('Retrieving business tax license data')
db = cx_Oracle.connect(credentials)
sql = general.file_to_string('./sql/ttcs_biz.sql', __file__)
df = pd.read_sql_query(sql, db)
df_rows = df.shape[0]
logging.info('Query returned {} results'.format(df_rows))
general.pos_write_csv(df,
temp_all,
date_format=conf['date_format_ymd_hms'])
return 'Successfully retrieved active businesses data.'
def get_special_events():
"""Get special events from DB."""
se_query = general.file_to_string('./sql/special_events.sql', __file__)
se_conn = MsSqlHook(mssql_conn_id='special_events_sql')
df = se_conn.get_pandas_df(se_query)
df['event_id'] = pd.to_numeric(df['event_id'],
errors='coerce',
downcast='integer')
general.pos_write_csv(df,
temp_file,
date_format=conf['date_format_ymd_hms'])
return "Retrieved special events to temp file."
def get_active_businesses():
"""Query DB for 'Active Businesses' and save data to temp."""
logging.info('Retrieving business tax license data')
db = cx_Oracle.connect(credentials)
sql = general.file_to_string('./sql/ttcs_biz.sql', __file__)
df = pd.read_sql_query(sql, db)
df_rows = df.shape[0]
logging.info('Query returned {} results'.format(df_rows))
general.pos_write_csv(
df,
temp_all,
date_format=conf['date_format_ymd_hms'])
return 'Successfully retrieved active businesses data.'
def get_permits_files():
"""Query DB for 'permits' and save data to temp directory."""
logging.info('Retrieving permits data.')
db = cx_Oracle.connect(credentials)
sql = general.file_to_string('./sql/pts.sql', __file__)
sql += "WHERE a.issue_dt >= TO_DATE('"+str(year)+"-JAN-01', 'YYYY-MON-DD') AND a.issue_dt < TO_DATE('"+str(year+1)+"-JAN-01', 'YYYY-MON-DD')"
df = pd.read_sql_query(sql, db)
logging.info('Query returned {} results for {}'.format(df.shape[0],year))
general.pos_write_csv(
df,
temp_permits,
date_format=conf['date_format_ymd_hms'])
return 'Successfully retrieved permits data.'
def get_streets_paving_data():
"""Get streets paving data from DB."""
pv_query = general.file_to_string('./sql/pavement_ex.sql', __file__)
pv_conn = MsSqlHook(mssql_conn_id='streets_cg_sql')
df = pv_conn.get_pandas_df(pv_query)
results = df.shape[0]
general.pos_write_csv(
df, temp_file)
return f"Successfully wrote temp file with {results} records"
def get_claims_data():
"""Query an oracle database"""
logging.info('Retrieving data from Oracle database')
# This requires that otherwise optional credentials variable
db = cx_Oracle.connect(credentials)
# Create a sql file containing query for the database
# Save this file in a sql folder at the same level as the jobs file
sql = general.file_to_string('./sql/claimstat_tsw.sql', __file__)
df = pd.read_sql_query(sql, db)
logging.info(f'Query returned {df.shape[0]} results')
general.pos_write_csv(df, "{}/claimstat_raw.csv".format(tmp))
return 'Successfully retrieved Oracle data.'
def get_fd_data(**kwargs):
"""Get fire department data from Data Base."""
logging.info("Get fire department data from CAD archive")
fd_query = general.file_to_string('./sql/fd.sql', __file__)
fd_conn = MsSqlHook(mssql_conn_id='fire_department')
logging.info("Read data to panda DataFrame")
df = fd_conn.get_pandas_df(fd_query)
# Write csv
logging.info('Writing ' + prod_file)
general.pos_write_csv(df,
prod_file,
date_format=conf['date_format_ymd_hms'])
return "Successfully wrote prod file at " + prod_file
def get_fd_data( **kwargs):
"""Get fire department data from Data Base."""
logging.info("Get fire department data from CAD archive")
fd_query = general.file_to_string('./sql/fd.sql', __file__)
fd_conn = MsSqlHook(mssql_conn_id='fire_department')
logging.info("Read data to panda DataFrame")
df = fd_conn.get_pandas_df(fd_query)
# Write csv
logging.info('Writing ' + prod_file)
general.pos_write_csv(
df, prod_file, date_format=conf['date_format_ymd_hms'])
return "Successfully wrote prod file at " + prod_file
def get_cip_data(**kwargs):
"""Get CIP data from Data Base."""
logging.info("Get CIP data from Oracle DataBase")
cip_query = general.file_to_string('./sql/cip.sql', __file__)
cip_conn = cx_Oracle.connect(credentials)
logging.info("Read data to Panda DataFrame")
df = pd.read_sql_query(cip_query, cip_conn)
rows = df.shape[0]
# Write csv
logging.info('Writing {} rows to prod'.format(rows))
general.pos_write_csv(df,
prod_file,
date_format=conf['date_format_ymd_hms'])
return "Successfully wrote prod file"
def get_sidewalk_gis(**kwargs):
""" Get sidewalk geodatabase from shared drive"""
sde_query = general.file_to_string('./sql/sidewalk_sde.sql', __file__)
sde_conn = MsSqlHook(mssql_conn_id='atlas_sde')
#sde_server = conf['sde_server']
#sde_user = conf['sde_user']
#sde_pw = conf['sde_pw']
#sde_conn = pymssql.connect(sde_server, sde_user, sde_pw, 'sdw')
#query = "SELECT *, [Shape].STAsText() as geom FROM SDW.IAMSD.SIDEWALK"
df = sde_conn.get_pandas_df(sde_query)
df.columns = [x.lower() for x in df.columns]
df = df.drop('shape', 1)
df = df.rename(
columns={
'sapid': 'seg_id',
'cdcode': 'council',
'cpcode': 'comm_plan',
'legacy_id': 'geojoin_id',
'iamfloc': 'fun_loc_id',
'loc_descr': 'loc_desc'
})
df = df.fillna('')
logging.info('processed {} rows'.format(rows))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return "Converted table to shapefile"
def get_cip_data(**kwargs):
"""Get CIP data from Data Base."""
logging.info("Get CIP data from Oracle DataBase")
cip_query = general.file_to_string('./sql/cip.sql', __file__)
cip_conn= cx_Oracle.connect(credentials)
logging.info("Read data to Panda DataFrame")
df = pd.read_sql_query(cip_query, cip_conn)
rows = df.shape[0]
# Write csv
logging.info('Writing {} rows to prod'.format(rows))
general.pos_write_csv(
df, prod_file, date_format=conf['date_format_ymd_hms'])
return "Successfully wrote prod file"
def get_fd_data(**kwargs):
"""Get fire department data from Data Base."""
logging.info("Get fire department data from Data Base")
fd_query = general.file_to_string('./sql/fd_pn.sql', __file__)
fd_conn = MsSqlHook(mssql_conn_id='fire_department')
logging.info("Read data to panda DataFrame")
df = fd_conn.get_pandas_df(fd_query)
df = df.rename(
columns={
'city': 'address_city',
'response_month': 'month_response',
'response_year': 'year_response'
})
logging.info("Writing {} rows to prod".format(df.shape[0]))
# Write csv
general.pos_write_csv(df, prod_file)
return "Successfully wrote prod file"
def get_onbase_test():
"""Get tables from OnBase."""
logging.info('Getting files from onbase')
for root, dirs, files in os.walk('./poseidon/dags/city_docs/sql/onbase'):
for name in files:
logging.info('Querying for '+name)
path = './sql/onbase/{}'.format(name)
query_string = general.file_to_string(path, __file__)
logging.info('Connecting to MS Database')
onbase_conn = MsSqlHook(mssql_conn_id='onbase_test_sql')
logging.info('Reading data to Pandas DataFrame')
df = onbase_conn.get_pandas_df(query_string)
table_type = name[0:-4]
logging.info('Correcting title column')
df['TITLE'] = fix_title(df[['TITLE','OBJECT_NAME']])
save_path = '{0}/onbase_test_{1}.csv'.format(conf['prod_data_dir'],table_type)
logging.info('Writting Production file')
general.pos_write_csv(df, save_path)
return "Successfully retrieved OnBase tables"
def get_onbase():
"""Get tables from OnBase."""
logging.info('Getting files from onbase')
for root, dirs, files in os.walk('./poseidon/dags/city_docs/sql/onbase'):
for name in files:
logging.info('Querying for '+name)
path = './sql/onbase/{}'.format(name)
query_string = general.file_to_string(path, __file__)
logging.info('Connecting to MS Database')
onbase_conn = MsSqlHook(mssql_conn_id='onbase_sql')
logging.info('Reading data to Pandas DataFrame')
df = onbase_conn.get_pandas_df(query_string)
table_type = name[0:-4]
logging.info('Correcting title column')
df['TITLE'] = fix_title(df[['TITLE','OBJECT_NAME']])
save_path = '{0}/onbase_{1}.csv'.format(conf['prod_data_dir'],table_type)
logging.info('Writting Production file')
general.pos_write_csv(df, save_path)
return "Successfully retrieved OnBase tables"
def get_indicator_bacteria_tests(date_start='01-JAN-2014', date_end='15-JUN-2017', **kwargs):
# For test mode, because F* waiting so long.
if kwargs['test_mode'] == True:
logging.warning("RUNNING IN TEST MODE, PULLING LAST YEAR ONLY!!!!")
date_start = (kwargs['execution_date'] - timedelta(days=365)).strftime('%d-%b-%Y')
db = cx_Oracle.connect(conf['oracle_wpl'])
logging.info("Starting Indicator Bac Tests: " + date_start + " to " + date_end)
jzn_1_q = string.Template(general.file_to_string('./sql/jzn1.sql', __file__))\
.substitute(ds=date_start, de=date_end)
jzn_2_q = string.Template(general.file_to_string('./sql/jzn2.sql', __file__))\
.substitute(ds=date_start, de=date_end)
jzn_3_q = string.Template(general.file_to_string('./sql/jzn3.sql', __file__))\
.substitute(ds=date_start, de=date_end)
jzn_4_q = string.Template(general.file_to_string('./sql/jzn4.sql', __file__))\
.substitute(ds=date_start, de=date_end)
logging.info("Reading JZN1")
jzn_1 = pd.read_sql_query(jzn_1_q, db, coerce_float=True, index_col='F_FIELD_RECORD')
jzn_1.F_VALUE = pd.to_numeric(jzn_1.F_VALUE, errors='coerce')
jzn_1 = jzn_1[jzn_1.F_VALUE.notnull()]
logging.info("Reading JZN2")
jzn_2 = pd.read_sql_query(jzn_2_q, db, coerce_float=True, index_col='F_FIELD_RECORD')
jzn_2.F_VALUE = pd.to_numeric(jzn_2.F_VALUE, errors='coerce')
jzn_2 = jzn_2[jzn_2.F_VALUE.notnull()]
logging.info("Reading JZN3")
jzn_3 = pd.read_sql_query(jzn_3_q, db, coerce_float=True, index_col='F_FIELD_RECORD')
jzn_3.F_VALUE = pd.to_numeric(jzn_3.F_VALUE, errors='coerce')
jzn_3 = jzn_3[jzn_3.F_VALUE.notnull()]
logging.info("Reading JZN4")
jzn_4 = pd.read_sql_query(jzn_4_q, db, coerce_float=True, index_col='F_FIELD_RECORD')
jzn_4.F_VALUE = pd.to_numeric(jzn_4.F_VALUE, errors='coerce')
jzn_4 = jzn_4[jzn_4.F_VALUE.notnull()]
jn_1 = jzn_1.rename(columns={
'SOURCE':'V5_SOURCE',
'SAMPLE_DATE':'V5_SAMPLE_DATE',
'SAMPLE_ID':'V5_SAMPLE_ID',
'F_VALUE':'V5_CL2_TOTAL',
'L_VALUE':'V5_T_COLIFORM'
}).filter(like='V5',axis=1)
jn_2 = jzn_2.rename(columns={
'L_VALUE':'V5_E_COLI'
}).filter(like='V5',axis=1)
jn_3 = jzn_3.rename(columns={
'F_QUAL':'V5_TEMP_PART1',
'F_VALUE':'V5_TEMP_PART2'
}).filter(like='V5',axis=1)
jn_4 = jzn_4.rename(columns={
'F_QUAL':'V5_PH_PART1',
'F_VALUE':'V5_PH_PART2'
}).filter(like='V5',axis=1)
df = jn_1.join([jn_2, jn_3, jn_4], how='inner')
df = df.rename(columns={
'V5_PH_PART2':'V5_PH',
'V5_TEMP_PART2':'V5_TEMPERATURE',
})
del df['V5_PH_PART1']
del df['V5_TEMP_PART1']
df = df.rename(columns=lambda x: re.sub('V5\_','',x))
df.index.rename(name='FR_NUM', inplace=True)
new_file_path = conf['prod_data_dir'] + '/indicator_bacteria_tests_datasd.csv'
logging.info("Writing to " + new_file_path)
df.to_csv(new_file_path,
index=True,
encoding='utf-8',
doublequote=True,
date_format=conf['date_format_ymd'])
return "Indicator bacteria tests written to " + new_file_path
def get_streets_paving_data(mode='sdif', **kwargs):
"""Get streets paving data from DB."""
pv_query = general.file_to_string('./sql/pavement_ex.sql', __file__)
pv_conn = MsSqlHook(mssql_conn_id='streets_cg_sql')
moratorium_string = "Post Construction"
phone_UTLY = "858-627-3200"
phone_OTHER = "619-527-7500"
TSW_PM = "*****@*****.**"
UTLY_PM = "*****@*****.**"
ACT_OVERLAY_CONCRETE_PM = "*****@*****.**"
ACT_SLURRY_SERIES_PM = "*****@*****.**"
# Different String for imcat mode.
if mode == 'imcat':
moratorium_string = "Post-Construction"
df = pv_conn.get_pandas_df(pv_query)
for i in [
'seg_id', 'rd_seg_id', 'wo_id', 'wo_name', 'wo_status',
'wo_proj_type', 'job_activity', 'seg_func_class'
]:
df[i] = df[i].astype(str)
df['job_completed_cbox'] = df['job_completed_cbox'].astype(bool)
# Backfill - set all fields to mora
df.loc[df.wo_status.str.contains(
'post construction|moratorium|post-construction',
regex=True,
case=False), "wo_status"] = moratorium_string
# Remove Records w/o A Completed Date ONLY in the UTLY and TSW work order
# IMCAT ONLY
if mode == 'imcat':
df = df.query('not '\
+ '((wo_id == "UTLY" & job_end_dt.isnull()) '\
+ 'or (wo_id == "TSW" & job_end_dt.isnull()))')
# Remove empty activities (IMCAT ONLY)
df = df.query('not '\
+ '(job_activity.isnull() '\
+ '| job_activity == "" '\
+ '| job_activity == "None")')
# Remove Data Entry
# Remove mill / pave
# Remove Structure Widening
# Remove Patching
if mode == 'imcat':
remove_search = 'data entry|mill|structure wid|patching'
else:
remove_search = 'data entry|structure wid|patching'
df = df[~(df.job_activity.str.contains(
remove_search, regex=True, case=False))]
# Search Strings
concrete_search = "panel rep|pcc - reconstruc"
slurry_search = "surface treatment|scrub seal|cape seal|central mix"
overlay_search = "resurfacing|overlay|ac overlay|mill|ac - overlay|ac - ac overlay|ac - reconstruct|ac inlay"
# Set Proj Type to NA
df['wo_proj_type'] = None
# Concrete
df.loc[df.job_activity.str.contains(
concrete_search, regex=True, case=False), 'wo_proj_type'] = 'Concrete'
# Slurry
df.loc[df.job_activity.str.contains(
slurry_search, regex=True, case=False), 'wo_proj_type'] = 'Slurry'
# Overlay
df.loc[df.job_activity.str.contains(
overlay_search, regex=True, case=False), 'wo_proj_type'] = 'Overlay'
# Remove All Records over 5 Years Old;
#pv <- pv[(as.Date(pv$job_end_dt) > (today() - years(5))) | is.na(pv$job_end_dt),]
# Create ref dates
#today = kwargs['execution_date']
today = general.today()
five_yrs_ago = today.replace(year=(today.year - 5))
three_yrs_ago = today.replace(year=(today.year - 3))
# Remove records
df = df[(df.job_end_dt > five_yrs_ago) | (df.job_end_dt.isnull())]
# Remove Slurry Records > 3 Years Old
# IMCAT ONLY
if mode == 'imcat':
mask = ~((df.wo_proj_type == 'Slurry') &
(df.job_end_dt < three_yrs_ago))
df = df[mask]
# Create a feature for completed jobs
df['final_job_completion_state'] = False
#pv[(!is.na(pv$job_end_dt) & pv$job_completed_cbox == 1), "final_job_completion_state"] <- 1
df.loc[df.job_end_dt.notnull(), "final_job_completion_state"] = True
# Set all completed jobs to Moratorium status
df.loc[df.final_job_completion_state == True,
"wo_status"] = moratorium_string
# Set Dates in The future for TSW work orders as Construction.
mask = (df.wo_id == 'TSW') & \
(df.job_end_dt.notnull()) & \
(df.job_end_dt > today)
df.loc[mask, "wo_status"] = "Construction"
# Set Phone # For UTLY
df.loc[df.wo_id == 'UTLY', 'wo_pm_phone'] = phone_UTLY
# Set Phone # for Everything else
df.loc[df.wo_id != 'UTLY', 'wo_pm_phone'] = phone_OTHER
# Set PM for UTLY
df.loc[df.wo_id == 'UTLY', 'wo_pm'] = UTLY_PM
# Set PM for TSW
df.loc[df.wo_id == 'TSW', 'wo_pm'] = TSW_PM
# Set PM for Overlay / Concrete
#mask = (df.wo_proj_type == 'Overlay') | (df.wo_proj_type == 'Concrete') & (df.wo_pm.isnull())
mask = (df.wo_pm.isnull()) & ((df.wo_proj_type == 'Overlay') |
(df.wo_proj_type == 'Concrete'))
df.loc[mask, 'wo_pm'] = ACT_OVERLAY_CONCRETE_PM
# Set PM for Slurry / Series
mask = (df.wo_pm.isnull()) & ((df.wo_proj_type == 'Slurry') |
(df.wo_proj_type == 'Series Circuit'))
df.loc[mask, 'wo_pm'] = ACT_SLURRY_SERIES_PM
# Spot Unknown
mask = (df.job_activity.isnull()) | (df.job_activity == None) | (df.job_activity == 'None') | (df.job_activity == '')\
|(df.wo_proj_type.isnull()) | (df.wo_proj_type == None) | (df.wo_proj_type == 'None') | (df.wo_proj_type == '')\
|(df.wo_status.isnull()) | (df.wo_status == None) | (df.wo_status == 'None') | (df.wo_status == '')
spot_unknown = df[mask]
logging.info('Found {} records with no activity, type or status'.format(
spot_unknown.shape[0]))
# Remove unknown
df = df[~mask]
# Sort by job end date time
df = df.sort_values(by='job_end_dt', na_position='last', ascending=False)
# Remove duplicates, although it doesn't make sense
# This is wrong.
df = df.drop_duplicates('seg_id', keep='first')
# Rename columns we're keeping
df = df.rename(columns={
'pve_id': 'PVE_ID',
'seg_id': 'SEG_ID',
'wo_id': 'PROJECTID',
'wo_name': 'TITLE',
'wo_pm': 'PM',
'wo_pm_phone': 'PM_PHONE',
'wo_design_start_dt': 'START',
'wo_design_end_dt': 'END',
'wo_resident_engineer': 'RESIDENT_ENGINEER',
'job_end_dt': 'MORATORIUM',
'wo_status': 'STATUS',
'wo_proj_type': 'TYPE',
'seg_length_ft': 'LENGTH',
'seg_width_ft': 'WIDTH',
})
# Regex for the keeps:
df = df.filter(regex="^[A-Z0-9]")
# Remove START and END for projects in moratorium:
df.loc[df.STATUS == moratorium_string, ['START', 'END']] = None
# For IMCAT uppercase status
if mode == 'imcat':
df['STATUS'] = df['STATUS'].str.upper()
# Write csv
logging.info('Writing ' + str(df.shape[0]) + ' rows in mode ' + mode)
general.pos_write_csv(
df, prod_file[mode], date_format=conf['date_format_ymd_hms'])
return "Successfully wrote prod file at " + prod_file[mode]