Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
FIT5137 S2 2024 Assignment 3: PTV Assignment Scenario
(Weight = 35%)
Due date: Friday, 25 October 2024, 4:30 PM
General Information and Submission
● This is an individual assignment.
● Submission method: Submission is online through Moodle.
● Penalty for late submission: 5% deduction for each day.
● Assignment FAQ: There is an Assignment 3 FAQ page set up on the EdStem forum.
Assignment Background
You have been hired as a data analyst at Public Transport Victoria (PTV), the Victoria Government authority responsible for public transport in the state. Some of your duties are data extraction, integration and analysis to provide good understanding regarding the public transportation condition in Victoria to the stakeholders.
After the COVID-19 restrictions were lifted, most companies are switching the workstyle from work-from-home to face-to-face. Therefore, transportation infrastructure and network is one of the most important aspects. While some people prefer to drive to work, some other people prefer to use the public transportation network as their main transportation mode. PTV as the sole provider for the public transportation network reduced their services during the lockdown period. Now, PTV has restored the services to cover as many areas as possible in the whole region. However, some questions remained mysteries. How good is the current PTV coverage? Are there any uncovered spots? Which area has the best public transportation options?
Therefore, as a data analyst, your task is to evaluate the data and provide the spatial data analysis to the stakeholders of PTV. The data should be presented in an area level, such as municipality, suburbs or postcode. For example, you may present “The number of bus services in Bundoora” or "The number of Trains or Trams network in Bundoora”.
Data
There are two datasets that you have to obtain in this assignment, which are the PTV/GTFS dataset and Australian Boundary data.
The General Transit Feed Specification (GTFS) is a data specification that allows public transit agencies to publish their transit data in a format that can be consumed by a wide variety of software applications. Today, the GTFS data format is used by thousands of public transport providers.
GTFS is split into a schedule component that contains schedule, fare, and geographic transit information and a real-time component that contains arrival predictions, vehicle positions and service advisories. A GTFS feed is composed of a series of text files collected in a ZIP file. Each file models a particular aspect of transit information: stops, routes, trips, and other schedule data.
For more detailed information about GTFS, you can refer to the official documentation provided by Google at https://developers.google.com/transit/gtfs. Additionally, You can read further explanation about the PTV-GTFS data from https://transitfeeds.com/p/ptv/497. For this assignment, we will be using the 17th March 2023 version of the dataset.
The GTFS data structure is shown below:
The Australian digital boundary is defined by the Australian Bureau of Statistics using the Australian Statistical Geography Standard (ASGS). The ASGS is a classification of Australia into a hierarchy of statistical areas. It is a social geography, developed to reflect the location of people and communities. It is used for the publication and analysis of official statistics and other data. The ASGS is updated every 5 years to account for growth and change in Australia’s population, economy and infrastructure. For the 2021 release, the ASGS will be re-named to the Australian Statistical Geography Standard (ASGS) Edition 3.
The ASGS is split into two parts, the ABS and Non ABS Structures. The ABS Structures are geographies that the ABS designs specifically for the release and analysis of statistics. This means that the statistical areas are designed to meet the requirements of statistical collections as well as geographic concepts relevant to those statistics. This helps to ensure the confidentiality, accuracy and relevance of ABS data.The Non ABS Structures generally represent administrative regions which are not defined or maintained by the ABS, but for which the ABS is committed to directly providing a range of statistics.
The Main Structure is developed by the ABS and is used to release and analyse a broad range of social, demographic and economic statistics. It is a nested hierarchy of geographies, and each level directly aggregates to the next level. Mesh Blocks (MBs) are the smallest geographic areas defined by the ABS and form the building blocks for the larger regions of the ASGS. Most Mesh Blocks contain 30 to 60 dwellings.
Below is the simplified ABS and Non ABS Structure. You can read further explanation about the structure here https://www.abs.gov.au/statistics/standards/australian-statistical-geography-standard-asgs-edition-3/jul2021-jun2026#overview
The Digital boundary files that you have to get is the Mesh Blocks dataset. The Mesh Blocks dataset is available as Shape file. You can read further explanation about the Mesh Blocks dataset here
Allocation files are non-spatial representations of how each geography is aggregated from their building block geography. You can also read further explanation about the Allocation files dataset here
Assignment Task list
Your assignment consists of several parts. Always read the instruction one by one. Do not move to the step without completing the previous step:
● Task 1: Data Restoration - Restore the data to the database. Monitor the success indicator to ensure successful restoration of the data.
● Task 2: Data Preprocessing - Perform necessary structure maintenance and create result tables for further processing.
● Task 3: Data Analytics and Visualization - Develop SQL queries to analyze the data and evaluate performance & Create visualizations to present the results of the data analytics.
● No data cleaning required for this assignment.
● For more information, see the FAQ for Assignment 3.
For simplicity, all the data required for this assignment is readily available in the PostGIS Docker container. You can access these datasets within the container by navigating to the /data/adata folder. If you don’t know how to do it, refer to the labs 10 activities.
Verify your data before the restoration process.
As a data analyst, it is your responsibility to understand and explore these publicly available data.
Assignment Task
Task 1: Data Restoration
Before you can start the data analytic processes, the first thing you have to do is to restore the external data to your database. Make sure you prepare a destination schema to restore your data. The destination schema for your assignment is “ptv”.
Note:
● Before initiating the data restoration process, it is essential to thoroughly explore the dataset. This exploration involves identifying appropriate data types, determining field lengths, and making other relevant considerations that will inform the creation of the table structure.
● Ensure that you restore the data into the PTV schema using regular (local) tables. Do not utilise foreign tables, as the data must be stored directly within the PostgreSQL database.
● Ensure that all tables are successfully restored, including 8 tables from GTFS and 3 tables for MB_2021, LGA_2021 and SAL_2021 respectively.
The outputs of this task for Report are:
a) Attach a screenshot of the results to include all the tables you restored in Task 1, including the number of rows for each table you restored by using following code:
|
with tbl as (select table_schema, TABLE_NAME from information_schema.tables where table_schema in ('ptv')) select table_schema, TABLE_NAME, (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n from tbl order by table_name; |
Task 2: Data Preprocessing for Melbourne Metropolitan area
The purpose of this section is to manipulate the data into a suitable format for the following task analysis. This task has two parts: Mandatory requirement and Optional requirements.
Mandatory requirement
[You must meet the mandatory requirements described in this section.]
In this assignment, we aim to explore the transportation accessibility [Topic of report] of the Melbourne Metropolitan area exclusively [Scope of report]. The mb_2021 table contains mesh blocks for the entire country of Australia. To minimise query costs, ensure that you only use the mesh blocks within the Melbourne Metropolitan area for this assignment. The Melbourne Metropolitan’s mesh blocks can be identified from the gcc_name21. If the column contains “Greater Melbourne”, this mesh block is located in Melbourne Metropolitan. As a result, you need to create a table called "mb2021_mel" that contains ONLY the mesh blocks in Melbourne Metropolitan.
Optional requirements:
[You are free to explore and manipulate the data creatively within the mandatory requirements, which are limited to Melbourne Metropolitan for the topic of transport accessibility.]
For optional requirements can be selected based on your specific data analysis needs. Make sure to include a detailed explanation of your rationale in the report for optional requirements you choose.
|
Question: Do I have to answer at least one of the optional requirements? Answer: No, you are free to explore and manipulate the data creatively as long as the data is analysed in Melbourne Metropolitan for the topic of transport accessibility. |
The following suggestion may useful for data exploration and analysis transportation accessibility of Melbourne Metropolitan area:
1.Since the working area will be Melbourne Metropolitan, it is important to have a polygon for the boundary of our working area. Hint: aggregate all mesh blocks polygon to create one large polygon for Melbourne Metropolitan boundary.
2.Stops table does not have any geometry column. It might be useful to add a geometry column, using the latitude and longitude values available in the table. Make sure you use GDA2020 (SRID:7844) for this column.
3.The Stops table does not show direct information regarding the vehicle types, routes_short_name and routes_long_name. These informations are stored in the routes table.
4.If you want to explore the transportation situation for different vehicle types, such as tram, train, or bus, the vehicle type is determined by the corresponding route type in the routes table, where:
● 0 corresponds to tram
● 2 corresponds to train
● 3 corresponds to bus
● Any other route type is labelled as 'Unknown'.
The outputs of this task for Report are:
b) Attach a screenshot of SQL script for creating a table named “mb2021_mel” that contains ONLY the mesh blocks in Melbourne Metropolitan.
c) Provide a detailed explanation of the remaining data processing steps you have conducted, including screenshots of the SQL scripts and the rationale behind your choices in the report.
Task 3: Data Analytics and Visualisation
In this section you will need to perform data analysis on the tables you have restored, focusing on transport accessibility in metropolitan Melbourne. Use the techniques you have learned in the spatial database part to carry out your analysis. You are free to choose any specific perspectives or aspects of data analysis relevant to your dataset, but ensure that your analysis relates to the main topic: transport accessibility in metropolitan Melbourne.
This could include exploring different statistical measures or carrying out other relevant analyses. Present your findings clearly and concisely, demonstrating your understanding of the dataset and highlighting any notable observations or patterns.
As part of this data visualisation, you will also need to create at least one map-based headmap using QGIS to present your findings related to the main topics. These visualisations will be used in the next section of the assignment, the summary report. To support your analysis, you can include screenshots of the visualisations directly in the report.
Be sure to include the script or code used for data analysis and data visualisation in the appendix of your report. The script should provide clear instructions on how the analysis was performed and any necessary calculations or transformations applied to the data. This will ensure that your analysis can be reproduced and verified. Remember to include appropriate labels, titles, and legends in your visualisations to make them easy to understand. The visualisations should be of sufficient quality and clarity to effectively convey your analysis findings.
Note:
● Use SQL queries to investigate the restored tables.
● Conduct a thorough descriptive analysis to uncover insights within the data.
● Summarise and Visualise your findings clearly and concisely.
● Highlight key observations and patterns discovered during the analysis.
● Ensure your findings reflect a deep understanding of the data.
The outputs of this task for Report are:
d) Data analysis and visualisation, including the screenshot of SQL script and visualisation. For the visualisation, it must contain at least one map base figure.
Submission Checklist
Summary Report for Task 1 to 3
As a professional data analyst, your task is to consolidate all the previous tasks, including data restoration, processing, analysis, and visualisations, into a comprehensive written report. The report should adhere to a word limit of 2000 words and follow a structured format, consisting of an introduction, methodology, results, conclusion, and appendix. Please note that a question-and-answer format is not acceptable for this assignment, and marks will be deducted for using such a format.
Please ensure that the report adheres to the given word limit and is well-organised, concise, and coherent.The sample report should be formatted as follows:
|
Title: Write your title here on a separate page, (Note: Abstract is not required) 1. Introduction, such as ● Briefly explain the purpose of the report and what you aim to achieve with your analysis. ● Highlight the key questions you want to investigate through your analysis. 2. Methodology This section should provide a clear explanation of the different stages of your work. ● Dataset Overview, such as Provide an overview of the data and its source. ● Data Restoration and Preprocessing, such as Explain how you imported and initially explored the data. Include the software and libraries used. Provide a detailed explanation of the remaining data processing steps you have conducted, and the rationale behind your choices in the report. ● Data Analysis and Visualization Describe which area of transport accessibility in metropolitan Melbourne you are primarily exploring. Describe the analysis you conducted and the types of visualisations you chose to use, and why you felt they would effectively represent your data and findings. What software or libraries were used to create these visualisations? 3. Results: Present the results of your in-depth investigations. Explain what these results mean and how they answer your initial questions. For the visualisation, it must contain at least one map base figure. 4. Discussion Discuss your findings and their implications. ● Restate the main findings of your descriptive and advanced analyses. ● Discuss how these findings answer your initial questions or hypotheses. ● Reflect on the process and any limitations or challenges you faced during your analysis. 5. References [Excluded from the 2000-word limit] If you have used external resources, don't forget to cite them properly according to the chosen style guide (APA 7th edition). 6. Appendix [Excluded from the 2000-word limit] The screenshots of the following tasks: ● Attach a screenshot of the results to include all the tables you restored in Task 1, including the number of rows for each table you restored. ● Attach screenshots of the SQL scripts used in Task 2, including the SQL scripts for creating a table called "mb2021_mel" and screenshots of the SQL scripts you used for the remaining data processing steps. ● The SQL script for Data analysis and visualisation |
Video presentation
A five minute video presentation in mp4 format save as: YourstudentID_A3_video.mp4
Based on the report you have created, present your design and findings in a five-minute video presentation. Ensure you thoroughly understand both the dataset and the report to effectively extract and communicate the key points.
Assignment Submission
1. A combined pdf file save as: YourstudentID_A3_report.pdf, containing all of the above tasks 1 to 3.
2. A five minute video presentation in mp4 format save as: YourstudentID_A3_video.mp4
Zip all above files from step 1 to 3, and name the ZIP folder as A3_YourstudentID.zip.
● The submission of this assignment must be in the form of a single ZIP file. Only PDF and .mp4 files will be accepted within the zip file. No other formats will be accepted.
● You must ensure that you have all the files listed in this checklist before submitting your assignment to Moodle. Failure to submit a complete list of files will lead to mark penalties.
● It's important to note that our support hours are limited, and we don't have the capacity to address submission issues outside of working hours. You must ensure that you have all the files listed in this checklist before submitting your assignment to Moodle. Failure to submit a complete list of files will result in a mark penalty.
● Penalty for late submission: 5% deduction for each day, including weekends
● Submission cut-off time: Friday, 1 November 2024, 4:30PM. Submissions will not be accepted after this time unless there are special considerations.