ioavstats package

Subpackages

Submodules

ioavstats.Menu module

IO-Aero Menu.

ioavstats.avstats module

IO-AVSTATS interface.

ioavstats.avstats.check_arg_msaccess(args)

Check the command line argument: -m / –msaccess.

Parameters:

args (argparse.Namespace) – Command line arguments.

Return type:

None

The MS Access database name must be in the format ‘upDDmmm’ where:

  • ‘up’ is a fixed prefix

  • ‘DD’ is the day of the month as a two digit number (01-31)

  • ‘mmm’ is the month as a three letter abbreviation (jan-dec)

If the task is ‘d_n_a’ or ‘l_n_a’, the argument ‘-m’ or ‘–msaccess’ is required. Otherwise, it is not allowed.

ioavstats.avstats.check_arg_msexcel(args)

Check the command line argument: -e / –msexcel.

This function checks whether the command line argument -e / –msexcel is valid. If the argument is required but not provided, or if the file does not exist or is not a valid Microsoft Excel file, a fatal error is raised.

Parameters:

args (argparse.Namespace) – Command line arguments.

Return type:

None

ioavstats.avstats.check_arg_task(args)

Check the command line argument: -t / –task.

Check if the specified task is a valid task name. If not, terminate the program with a fatal error message.

Parameters:

args (argparse.Namespace) – Command line arguments.

Return type:

None

ioavstats.avstats.cleansing_postgres_data()

c_p_d: Cleansing PostgreSQL data.

This function cleanses the PostgreSQL database.

Return type:

None

ioavstats.avstats.correct_dec_lat_lng()

c_l_l: Correct US decimal latitudes and longitudes.

This function corrects the decimal latitude and longitude values in the PostgreSQL database.

Return type:

None

ioavstats.avstats.create_db_schema()

c_d_s: Create the PostgreSQL database schema.

This function creates the PostgreSQL database schema.

Return type:

None

ioavstats.avstats.download_ntsb_msaccess_file(msaccess)

d_n_a: Download a NTSB MS Access database file.

This function downloads a NTSB MS Access database file.

Parameters:

msaccess (str) – The NTSB MS Access database file without file extension.

Return type:

None

ioavstats.avstats.find_nearest_airports()

f_n_a: Find the nearest airports.

This function determines the nearest airports to the event sites.

Return type:

None

ioavstats.avstats.generate_sql()

Generate SQL statements: INSERT & UPDATE.

This function generates SQL statements: INSERT & UPDATE.

It calls the function code_generator.generate_sql() to generate the SQL statements.

Return type:

None

ioavstats.avstats.get_args()

Load the command line arguments into the memory.

This function loads the command line arguments using the argparse module and checks the arguments for validity.

The available command line arguments are:

  • -e, –msexcel: The MS Excel file.

  • -m, –msaccess: The Microsoft Access database file:

    • avall: Data from January 1, 2008 to today.

    • pre2008: Data from January 1, 1982 to December 31, 2007.

    • upDDMON: New additions and updates until DD day in the month MON.

  • -t, –task: The task to execute:

    • a_o_c: Load aviation occurrence categories into PostgreSQL.

    • c_d_s: Create the PostgreSQL database schema.

    • c_l_l: Correct decimal US latitudes and longitudes.

    • c_p_d: Cleansing PostgreSQL data.

    • d_n_a: Download a NTSB MS Access database file.

    • f_n_a: Find the nearest airports.

    • generate: Generate SQL statements.

    • l_a_p: Load airport data into PostgreSQL.

    • l_c_d: Load data from a correction file into PostgreSQL.

    • l_c_s: Load country and state data into PostgreSQL.

    • l_n_a: Load NTSB MS Access database data into PostgreSQL.

    • l_s_d: Load simplemaps data into PostgreSQL.

    • l_s_e: Load sequence of events data into PostgreSQL.

    • l_z_d: Load ZIP Code Database data into PostgreSQL.

    • r_d_s: Update the PostgreSQL database schema.

    • u_d_s: Refresh the PostgreSQL database schema.

    • version: Show the current version of IO-AVSTATS.

    • v_n_d: Verify selected NTSB data.

Return type:

None

ioavstats.avstats.load_airport_data()

l_a_p: Load airport data into PostgreSQL.

This function loads the airport data into the PostgreSQL database.

Return type:

None

ioavstats.avstats.load_aviation_occurrence_categories()

a_o_c: Load aviation occurrence categories into PostgreSQL.

This function loads the aviation occurrence categories into the PostgreSQL database.

Return type:

None

ioavstats.avstats.load_correction_data(filename)

l_c_d: Load data from a correction file into PostgreSQL.

Load data from a correction file into the PostgreSQL database.

Parameters:

filename (str) – The filename of the correction file.

Return type:

None

ioavstats.avstats.load_country_state_data()

l_c_s: Load country and state data into PostgreSQL.

This function loads the country and state data from a CSV file into the PostgreSQL database.

Notes

  • The country and state data is loaded from a CSV file.

  • The CSV file is assumed to be in the same directory as the script.

  • The CSV file is assumed to have the following columns:

    • country

    • country_name

    • state

    • state_name

  • The data is loaded into the PostgreSQL tables:

    • io_countries

    • io_states

Return type:

None

ioavstats.avstats.load_ntsb_msaccess_data(msaccess)

l_n_a: Load NTSB MS Access database data into PostgreSQL.

Load the NTSB MS Access database data into the PostgreSQL database.

Parameters:

msaccess (str) – The NTSB MS Access database file without file extension.

Return type:

None

Notes

  • The NTSB MS Access database file is assumed to be in the same directory as the script.

  • The NTSB MS Access database file is assumed to have the following tables:

    • aircraft

    • dt_aircraft

    • dt_events

    • dt_flight_crew

    • engines

    • events

    • events_sequence

    • findings

    • flight_crew

    • flight_time

    • injury

    • narratives

    • ntsb_admin

    • occurrences

    • seq_of_events

  • The data is loaded into the PostgreSQL tables:

    • aircraft

    • dt_aircraft

    • dt_events

    • dt_flight_crew

    • engines

    • events

    • events_sequence

    • findings

    • flight_crew

    • flight_time

    • injury

    • narratives

    • ntsb_admin

    • occurrences

    • seq_of_events

ioavstats.avstats.load_sequence_of_events()

l_s_e: Load sequence of events data into PostgreSQL.

Loads the sequence of events data from a CSV file into the PostgreSQL database.

Notes

  • The sequence of events data is loaded from a CSV file.

  • The CSV file is assumed to be in the same directory as the script.

  • The CSV file is assumed to have the following columns:

    • eventsoe_no

    • meaning

    • cictt_code

  • The data is loaded into the PostgreSQL table:

    • seq_of_events

Return type:

None

ioavstats.avstats.load_simplemaps_data()

l_s_d: Load simplemaps data into PostgreSQL.

Notes

  • The simplemaps data is loaded from two CSV files.

  • The CSV files are assumed to be in the same directory as the script.

  • The CSV files are assumed to have the following columns:
    • us_cities.csv:

      • city

      • lat

      • lng

      • state

      • state_id

      • zips

    • us_zips.csv:

      • zip

      • lat

      • lng

      • city

      • state

      • state_id

      • county

      • county_fips

  • The data is loaded into the PostgreSQL tables:

    • io_lat_lng

Return type:

None

ioavstats.avstats.load_zip_code_db_data()

l_z_d: Load ZIP Code Database data into PostgreSQL.

Notes

  • The ZIP Code Database data is loaded from an Excel file.

  • The Excel file is assumed to be in the same directory as the script.

  • The Excel file is assumed to have the following columns:

    • zip_code

    • lat

    • lng

    • city

    • state

    • state_id

    • county

    • county_fips

  • The data is loaded into the PostgreSQL table:

    • io_lat_lng

Return type:

None

ioavstats.avstats.progress_msg(msg)

Create a progress message.

Parameters:

msg (str) – Progress message to be displayed.

Return type:

None

Notes

  • The progress message is only displayed if the verbose mode is on.

ioavstats.avstats.progress_msg_time_elapsed(duration, event)

Create a time elapsed message.

The time elapsed is displayed in seconds and milliseconds.

Parameters:

  • duration (int) – Time elapsed in ns.

  • event (str) – Event description.

Return type:

None

Notes

  • The time elapsed message is only displayed if the verbose mode is on.

ioavstats.avstats.refresh_db_schema()

Refresh the PostgreSQL database schema.

Notes

  • This function uses the following functions:

    • db_ddl_base.refresh_db_schema()

Return type:

None

ioavstats.avstats.terminate_fatal(error_msg)

Terminate the application immediately.

Parameters:

error_msg (str) – Error message to be displayed.

Return type:

None

Notes

  • This function is used to terminate the application immediately with an error message.

ioavstats.avstats.update_db_schema()

Update the PostgreSQL database schema.

Notes

  • This function calls db_ddl_base.update_db_schema() to update the database schema.

Return type:

None

ioavstats.avstats.verify_ntsb_data()

v_n_d: Verify selected NTSB data.

This function verifies selected NTSB data.

Notes

  • This function is used to verify selected NTSB data.

  • This function is called by the main entry point of the application.

Return type:

None

ioavstats.avstats.version()

Return the version number of the IO-AVSTATS application.

Notes

  • This function returns the version number of the IO-AVSTATS application.

Returns:

The version number of the IO-AVSTATS application

Return type:

str

ioavstats.code_generator module

IO-AVSTATS interface.

ioavstats.code_generator.generate_sql()

Generate SQL statements: INSERT & UPDATE.

The underlying database structures originate from a DDL export of RazorSQL.

Return type:

None

ioavstats.db_ddl_base module

Managing the database schema of the PostgreSQL database.

ioavstats.db_ddl_base.create_db_schema()

Create the database schema.

This function is a part of the setup of the database. It is called after the database has been created and the tables have been created. It creates the database schema by calling the functions that create the tables and the views.

Return type:

None

ioavstats.db_ddl_base.refresh_db_schema()

Refresh the database schema.

This function is a part of the setup of the database. It is called after the database has been created and the tables have been created. It updates the database schema by refreshing the materialized views.

Return type:

None

ioavstats.db_ddl_base.update_db_schema()

Update the database schema.

This function is a part of the setup of the database. It is called after the database has been created and the tables have been created. It drops all existing views and creates new ones and also creates the indexes for the tables.

Return type:

None

ioavstats.db_dml_base module

Managing the database schema of the PostgreSQL database.

ioavstats.db_dml_base.download_us_cities_file()

Download the US cities file from the simplemaps website.

The function downloads the US cities file from the simplemaps website and stores it in the download work directory. The filename is specified in the settings.

The function checks the status code of the response and checks if the file has been downloaded successfully. If not, it terminates with an error message.

The function also checks if the file is a valid zip file and if it can be unpacked. If the file is not a valid zip file or if it cannot be unpacked, it terminates with an error message.

Return type:

None

ioavstats.db_dml_base.download_zip_code_db_file()

Download the ZIP Code Database file.

The function downloads the ZIP Code Database file from the Zip Codes.org website and stores it in the download work directory. The filename is specified in the settings.

The function checks the status code of the response and checks if the file has been downloaded successfully. If not, it terminates with an error message.

If the file has been downloaded successfully, it prints a message with the filename and the number of chunks that have been downloaded.

Return type:

None

ioavstats.db_dml_base.load_airport_data()

l_a_p: Load airport data into PostgreSQL.

This function loads the airport data into the PostgreSQL database. The airport data is read from a CSV file and stored into the tables ‘io_airports’ and ‘io_runways’.

Return type:

None

ioavstats.db_dml_base.load_aviation_occurrence_categories()

Load aviation occurrence categories from an Excel file.

The categories are part of the ICAO common taxonomy, which is used to categorize events. The categories are used to link events to their corresponding CICTT codes.

The function loads the categories from an Excel file into the PostgreSQL database. The Excel file is expected to be in the same directory as this module.

Return type:

None

ioavstats.db_dml_base.load_country_state_data()

Load country and state data from various sources and save it into the PostgreSQL database.

The function first loads the country data from a CSV file into a pandas DataFrame. Then, it loads the state data from an Excel file into another pandas DataFrame. The data is then saved into the ‘io_countries’ and ‘io_states’ tables in the PostgreSQL database.

Returns:

None

Return type:

None

ioavstats.db_dml_base.load_sequence_of_events()

Load sequence of events sequence data.

This function loads sequence of events sequence data from a CSV file into the PostgreSQL database. The data is loaded into the ‘seq_of_events’ table.

The function starts by deleting any existing data from the table and then loads the data from the CSV file.

The function then finalizes the processing by closing the database connection.

Return type:

None

ioavstats.db_dml_base.load_simplemaps_data()

Load simplemaps data.

This function loads data from simplemaps into the PostgreSQL database. The data is loaded into the ‘io_lat_lng’ table.

The data loaded is from two sources:

  1. A US city file

  2. A US zip code file

The data from the two sources is loaded into two separate tables and then averaged into a third table.

The function starts by deleting any existing data from the two sources and then loads the data from the two sources. The data is then averaged and inserted into the third table.

The function then finalizes the processing by closing the database connection.

Return type:

None

ioavstats.db_dml_base.load_zip_codes_org_data()

Load ZIP Code Database data.

This function loads data from the ZIP Code Database file into the PostgreSQL database. The data is loaded into the ‘io_lat_lng’ table.

Return type:

None

ioavstats.db_dml_corr module

Managing the database schema of the PostgreSQL database.

ioavstats.db_dml_corr.ROW: list[OrderedDict]
ioavstats.db_dml_corr.cleansing_postgres_data()

Cleansing PostgreSQL data.

Return type:

None

ioavstats.db_dml_corr.correct_dec_lat_lng()

Correct decimal latitude and longitude.

Return type:

None

ioavstats.db_dml_corr.find_nearest_airports()

Find the nearest airports.

Return type:

None

ioavstats.db_dml_corr.load_correction_data(filename)

Load data from a correction file into the PostgreSQL database.

Parameters:

filename (str) – The MS Excel file.

Return type:

None

ioavstats.db_dml_corr.verify_ntsb_data()

Verify selected NTSB data.

Return type:

None

ioavstats.db_dml_msaccess module

Managing the database schema of the PostgreSQL database.

ioavstats.db_dml_msaccess.download_ntsb_msaccess_file(msaccess)

Download an MS Access database file from the NTSB website.

Parameters:

msaccess (str) – The MS Access database file without file extension.

Return type:

None

Notes

  • The function attempts to download the file from the NTSB website.

  • The function checks the status code of the response and checks if the file has been downloaded successfully. If not, it terminates with an error message.

  • The function also checks if the file is a valid zip file and if it can be unpacked. If the file is not a valid zip file or if it cannot be unpacked, it terminates with an error message.

  • The function prints a message with the filename and the number of chunks that have been downloaded.

  • The function also prints a message with the filename and the number of chunks that have been downloaded.

ioavstats.db_dml_msaccess.load_ntsb_msaccess_data(msaccess)

Load data from MS Access to the PostgreSQL database.

Parameters:

msaccess (str) – The MS Access database file without file extension.

Return type:

None

ioavstats.glob_local module

Global constants and variables.

ioavstats.user_guide module

Creation of the user guide.

ioavstats.user_guide.get_ae1982_app()

Create the user guide for the whole application.

This user guide is displayed when the user navigates to the ‘Application’ page.

Return type:

None

ioavstats.user_guide.get_ae1982_bar_chart(chart_id, chart_title)

ae1982 - Creates the user guide for bar charts.

This function generates the user guide for bar charts. The user guide is divided into two parts: 1. A detailed description of the bar chart.

Parameters:

  • chart_id (str)

  • chart_title (str)

Return type:

None

ioavstats.user_guide.get_pd1982_app()

Create the user guide for the whole application.

This function generates the user guide for the whole application. The user guide is divided into two parts: 1. A detailed description of the application. 2. A link to the GitHub page where the user can find more information.

Return type:

None

ioavstats.user_guide.get_pd1982_data_profile()

Create the user guide for the ‘Show data profile’ task.

This task performs a data analysis of the selected table or view. This is carried out with the help of [Pandas Profiling](https://pandas-profiling.ydata.ai/docs/master/).

The task provides two versions of the data analysis: 1. An explorative version that requires more computational effort than the minimal version. 2. A minimal version that requires less computational effort than the explorative version.

Depending upon the size of the selected table and/or View it can come to delayed response times.

The result of the data analysis can also be downloaded as HTML file if desired.

Return type:

None

ioavstats.user_guide.get_pd1982_details()

Create the user guide for the ‘Show details’ task.

This function generates the user guide for the ‘Show details’ task. The user guide is divided into two parts: 1. A detailed description of the task. 2. A footer with links to the GitHub page and the IO-Aero website.

The task provides the data of the tables and views of the database IO-AVSTATS-DB in a table format for display and download as csv file. The series to be displayed can be limited to an interval of event years in the filter options. The order of data display is based on the respective primary key of the database table. The database columns of the selected series are always displayed in full.

Return type:

None

ioavstats.utils module

Application Utilities.

ioavstats.utils.get_args()

Load the command line arguments into the memory.

This function parses the command line arguments and determines the host mode and the execution mode.

The host mode can be either ‘Cloud’ or ‘Local’. The execution mode can be either ‘ltd’ (limited demo version) or ‘std’ (full version).

Returns:

The host mode: ‘Cloud’ or ‘Local’.

Return type:

str

ioavstats.utils.get_engine(settings)

Create a simple user PostgreSQL database engine.

This function creates a database engine which is used to connect to the PostgreSQL database.

Parameters:

settings (Dynaconf) – The settings object.

Returns:

The database engine.

Return type:

Engine

The engine is created using the following parameters:

  • postgres_user_guest: The PostgreSQL user for the database.

  • postgres_password_guest: The password for the PostgreSQL user.

  • postgres_host: The host name of the PostgreSQL server.

  • postgres_connection_port: The port number of the PostgreSQL server.

  • postgres_dbname: The name of the PostgreSQL database.

ioavstats.utils.get_postgres_connection()

Create a PostgreSQL connection.

The function creates a connection to the PostgreSQL database.

Returns:

The PostgreSQL connection object.

Return type:

connection

ioavstats.utils.prepare_latitude(latitude_string)

Prepare a latitude structure.

Parameters:

latitude_string (str) – Latitude string.

Returns:

Latitude structure.

Return type:

str

This function takes a latitude string and prepares it for display. The string is expected to be in the format “DDDMMSS” where DDD is the degrees, MM is the minutes and SS is the seconds. The string will be divided into its components and returned as a string with spaces between the components.

ioavstats.utils.prepare_longitude(longitude_string)

Prepare a longitude structure.

Parameters:

longitude_string (str) – longitude string.

Returns:

longitude structure.

Return type:

str

This function takes a longitude string and prepares it for display. The string is expected to be in the format “DDDMMSS” where DDD is the degrees, MM is the minutes and SS is the seconds. The function will split the string into its components and return a string in the format “DD MM SS.SSS”.

If the string is not in the expected format, the function will return the original string.

ioavstats.utils.present_about(pg_conn, app_id)

Present the ‘about’ information.

Parameters:

  • pg_conn (connection) – Database connection.

  • app_id (str) – Application name.

Return type:

None

This function presents the ‘about’ information, including the latest NTSB database file and the copyright information.

ioavstats.utils_msaccess module

Miscellaneous helper functions.

ioavstats.utils_msaccess.get_msaccess_cursor(filename)

Create an MS Access cursor.

Parameters:

filename (str) – MS Access filename.

Returns:

ODBC database connection and cursor.

Return type:

tuple[pyodbc.Connection,pyodbc.Cursor]

Module contents

IO-AVSTATS.