def stations_import():
"""
Imports the traffic site and updates the site table
"""
def load_data_local():
ok = True
st.info('transferring data from staging to fact')
cmd = qry['station_staging_fact']
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info('data transferred')
else:
st.info(
'An error occurred transferring the data from staging to fact')
return ok
# Main
ok = True
if ok:
cmd = all_qry.qry['truncate_table'].format('station_source')
ok = db.execute_non_query(cmd, db.conn)
if ok:
df, ok = read_source_file(cn.SOURCE_STATION_FILE_NAME)
if ok:
fields = [
'ZST_NR', 'ZST_NAME', 'Geo Point', 'BREITENGR', 'LAENGENGR',
'GEMEINDE', 'ZST_KLASSE', 'KOMBINIERT', 'ART', 'ANZ_ARME',
'ANZ_FAHRST', 'ZWECK', 'TYP', 'STR_TYP', 'EIGENTUM', 'DAT_INBETR',
'GUELTIG'
]
ok = save_df2table('station_source', df, fields)
ok = False
if ok:
cmd = all_qry.qry['truncate_table'].format('station_staging')
ok = db.execute_non_query(cmd, db.conn)
if ok:
ok = transfer_source2staging('station_source_staging')
if ok:
pass # not yet required: ok = update_columns()
if ok:
ok = load_data_local()
ok = True
tools.log('miv_update start')
def load_data_local():
ok = True
st.info('transferring data from staging to fact')
cmd = qry['station_staging_fact']
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info('data transferred')
else:
st.info(
'An error occurred transferring the data from staging to fact')
return ok
def compact_db():
st.info('Resetting tables...')
cmd = all_qry.qry['truncate_table'].format('slow_traffic_source')
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = all_qry.qry['truncate_table'].format('miv_traffic_source')
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = all_qry.qry['truncate_table'].format('traffic_missing')
ok = db.execute_non_query(cmd, db.conn)
st.info('Done...')
st.info('Compacting database...')
size_start = os.path.getsize(cn.DATABASE_FILE)
if ok:
cmd = "VACUUM"
ok = db.execute_non_query(cmd, db.conn)
if ok:
size_end = os.path.getsize(cn.DATABASE_FILE)
st.info(
f'database has been compacted from {size_start} to {size_end} bytes. ({(size_start-size_end) / size_start * 100} %'
)
else:
st.error('I tried but it did not work')
def transfer_source2staging(key: str):
"""
Copies data from the source to the staging table and updates some fields, returns status
"""
ok = True
st.info('transferring data from source to staging')
cmd = qry[key]
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info('data transferred')
else:
st.info(
'An error occurred transferring the data from source to staging')
return ok
def transfer_staging2fact():
ok = True
tools.log('Copy miv traffic records from staging to miv_traffic table')
cmd = qry['traffic_staging_fact']
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = qry[update_info_query[traffic_type]]
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = qry['reset_station_flags']
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = qry['update_station_flags'].format('miv_flag', 1)
ok = db.execute_non_query(cmd, db.conn)
cmd = qry['update_station_flags'].format('velo_data_flag', 2)
ok = db.execute_non_query(cmd, db.conn)
cmd = qry['update_station_flags'].format('fuss_data_flag', 3)
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info('Statement executed.')
else:
st.error('Statement failed.')
return ok
def insert_missing_rows(year: int) -> bool:
"""
Inserts an empty value row for all missing intervals. This allows to show missing values as gaps in
time series if plotted.
"""
cmd = all_qry.qry['delete_rows_where'].format('traffic_fact',
"values_edited=-99")
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = all_qry.qry['truncate_table'].format('traffic_missing')
ok = db.execute_non_query(cmd, db.conn)
fields = [
"direction_id",
"station_id",
"values_edited",
"lane",
"traffic_type",
"date",
"date_time",
"hour_from",
"week",
"month",
"year",
"weekday",
"weekday_type",
]
cmd = qry['station_list_year'].format(year)
directions = db.execute_query(cmd, db.conn)
for row in directions.itertuples():
tools.log(f'{row.direction_id}, {row.lane}')
# make sure that empty rows are only inserted with the min/max values of existing data.
cmd = qry['min_max_dates'].format(year, row.station_id)
df = db.execute_query(cmd, db.conn)
start_date = df['min'][0]
end_date = df['max'][0]
idx_intervals = pd.date_range(start=f'{start_date}',
end=f'{end_date}',
freq='H',
tz='Europe/Zurich')
df = pd.DataFrame(data=idx_intervals, columns=['date_time'])
df['year'] = year
df['station_id'] = row.station_id
df['direction_id'] = row.direction_id
df['lane'] = row.lane
df['traffic_type'] = row.traffic_type
df['date'] = df['date_time'].dt.strftime('%Y-%m-%d')
df['month'] = np.int64(df['date_time'].dt.strftime('%m'))
df['week'] = np.int64(df['date_time'].dt.strftime('%W'))
df['weekday'] = np.int64(df['date_time'].dt.strftime('%u'))
df.loc[df['weekday'] < 6, 'weekday_type'] = 1
df.loc[df['weekday'] > 5, 'weekday_type'] = 2
df['hour_from'] = np.int64(df['date_time'].dt.strftime('%H'))
df['values_edited'] = -99
df['date_time'] = df['date_time'].dt.strftime('%Y-%m-%d %H:30')
if not ok:
break
else:
ok = save_df2table('traffic_missing', df, fields)
if ok:
cmd = qry['insert_missing_records']
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.write('Added records intervals with no measured values')
st.write('Finished')
return ok
def import_traffic(year: int, traffic_type: int):
"""
Replaces the table traffic_source with new data. Truncates first all data, then loads the all data from
data.bs.ch in a dataframe and filters for rows with year > currentyear -2. this is to reduce the amount of data
replaced in the target table miv.
"""
def transfer_staging2fact():
ok = True
tools.log('Copy miv traffic records from staging to miv_traffic table')
cmd = qry['traffic_staging_fact']
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = qry[update_info_query[traffic_type]]
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = qry['reset_station_flags']
ok = db.execute_non_query(cmd, db.conn)
if ok:
cmd = qry['update_station_flags'].format('miv_flag', 1)
ok = db.execute_non_query(cmd, db.conn)
cmd = qry['update_station_flags'].format('velo_data_flag', 2)
ok = db.execute_non_query(cmd, db.conn)
cmd = qry['update_station_flags'].format('fuss_data_flag', 3)
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info('Statement executed.')
else:
st.error('Statement failed.')
return ok
# cmd = qry['last_miv_observation']
# result = get_single_value(cmd, conn, 'max_dat')
# st.info('Most recent observation in miv_traffic: ' + result)
### Main
ok = True
update_info_query = {1: 'update_miv_info', 2: 'update_slow_info'}
source_table = {1: 'miv_traffic_source', 2: 'slow_traffic_source'}
source_file = {
1: cn.source_miv_file_name.format(year),
2: cn.source_slow_file_name.format(year)
}
source_fields = {
1: [
'SiteCode', 'SiteName', 'DirectionName', 'LaneCode', 'LaneName',
'Date', 'TimeFrom', 'TimeTo', 'ValuesApproved', 'ValuesEdited',
'TrafficType', 'Total', 'MR', 'PW', 'PW+', 'Lief', 'Lief+',
'Lief+Aufl.', 'LW', 'LW+', 'Sattelzug', 'Bus', 'andere', 'Year',
'Month', 'Weekday', 'HourFrom'
],
2: [
'SiteCode', 'SiteName', 'DirectionName', 'LaneCode', 'LaneName',
'Date', 'TimeFrom', 'TimeTo', 'ValuesApproved', 'ValuesEdited',
'TrafficType', 'Total', 'Year', 'Month', 'Weekday', 'HourFrom'
]
}
source_staging_transfer_query = {
1: 'miv_traffic_source_staging',
2: 'slow_traffic_source_staging'
}
traffic_type_criteria = {1: 'traffic_type = 1', 2: traffic_type > 1}
row_count_start = db.count_rows("select * from traffic_fact", db.conn)
# delete all records from the miv_traffic_source table
if ok:
cmd = all_qry.qry['truncate_table'].format(source_table[traffic_type])
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info(f'Table {source_table[traffic_type]} was initialized.')
else:
st.error(
f'Table {source_table[traffic_type]} could not be deleted.')
if ok:
df, ok = read_source_file(source_file[traffic_type])
if ok:
ok = save_db_table(source_table[traffic_type], df,
source_fields[traffic_type])
# delete all rows from the staging table
if ok:
cmd = all_qry.qry['truncate_table'].format('traffic_staging')
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info(f'Table {"traffic_staging"} was initialized.')
else:
st.error(f'Table {"traffic_staging"} could not be deleted.')
# copy the source data to the staging table, some fields are removed and counts are simplified, e.g. pw and pw with anhänger are summed
# there is a new count for pw and lieferwagen and for lastwagen, lastwagen with anhänger and sattelschlepper so light and heavy traffic can be easily
# distinguished.
if ok:
ok = transfer_source2staging(
source_staging_transfer_query[traffic_type])
# get the station_id from the station table
if ok:
cmd = qry['traffic_update_station_id']
ok = db.execute_non_query(cmd, db.conn)
# append new direction names to the lookup table
if ok:
cmd = qry['traffic_update_direction_codes']
ok = db.execute_non_query(cmd, db.conn)
# update direction id field in traffic_staging table
if ok:
cmd = qry['traffic_update_direction_id']
ok = db.execute_non_query(cmd, db.conn)
# update time fields
if ok:
cmd = qry['update_traffic_time_columns']
ok = db.execute_non_query(cmd, db.conn)
ok = True
if ok:
cmd = all_qry.qry['delete_rows_where'].format(
'traffic_fact',
f'{traffic_type_criteria[traffic_type]} and year = {year}')
st.write(cmd)
ok = db.execute_non_query(cmd, db.conn)
if ok:
st.info(
f'Table {"traffic_fact"} was initialized for year and traffic type.'
)
else:
st.error(
f'Table {"traffic_staging"} could not be initialized for year and traffic type.'
)
if ok:
ok = transfer_staging2fact()
if ok:
row_count_end = db.count_rows("select * from traffic_fact", db.conn)
st.info(
f'{row_count_end - row_count_start} rows where successfully imported'
)
df = db.execute_query(qry['import_result_summary'], db.conn)
st.write("Summary")
st.table(df)
else:
st.error(
"The import could not be completed, check log above for error messages"
)