data SQL

Java Python Project 1.2: Transitor
CS Year 1 Project 2
Department of Advanced Computing Sciences
Maastricht University
Table of contents
1 Transitor 2
2 Project Description 2
2.1 Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.2 Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.3 Routing Engine and Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
3 Phases of the Project 3
3.1 Phase 1: Distance and Time Calculators . . . . . . . . . . . . . . . . . . . . . . 3
3.1.1 Tasks: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
3.1.2 Requirements: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
3.1.3 To Pass: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1.4 Demo: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1.5 Criteria for a higher grade (include): . . . . . . . . . . . . . . . . . . . . 5
3.2 Phase 2: Public Transport Routing . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.2.1 Tasks: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.2.2 Requirements: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.2.3 To Pass: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.3 Phase 3: Public Transport Routing (with transfers) + Accessibility calculation
per postal code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.3.1 Tasks: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3.2 Requirements: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3.3 To Pass: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3.4 Criteria for a higher grade include: . . . . . . . . . . . . . . . . . . . . . 7
References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1
Appendix 8
General Transit Feed Specification (GTFS) . . . . . . . . . . . . . . . . . . . . . . . 8
Understanding GTFS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
GeoJSON files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Calculating transit times when bus transfers are included . . . . . . . . . . . . 9
1 Transitor
In this project, “Transitor,” you are tasked with developing a Java-based routing engine for
Maastricht’s public transport. This tool aims to analyze the socio-economic accessibility of
various areas within the city. By integrating geographical data, public transport information,
and socio-economic metrics, you will create a system that not only navigates through the
city’s complex transportation network but also sheds light on the accessibility disparities across
different neighborhoods.
2 Project Description
This project primarily involves creating a Java-based navigation system for Maastricht, aimed
at evaluating the socio-economic accessibility across various regions. The objective is to use this
system to understand how different areas connect and relate in terms of available socio-economic
opportunities.
2.1 Background
Socio-economic opportunities, including healthcare, education, and mobility, are critical for
community well-being but are not uniformly accessible across urban landscapes. The distribution
and accessibility of these opportunities may affect residents’ quality of life and their ability to
access essential services (Litman 2017). The case of Bijlmer, an area in Amsterdam, serves
as an example where urban design failed to adequately provide socio-economic opportunities,
leading to isolation and reduced quality of life for its residents (Olsson and Loerakker 2013).
In global contexts, studies in cities like London and New York have provided valuable insights
into how transport systems affect socio-economic accessibility. For instance, London’s Crossrail
project was studied for its impact on regional accessibility, revealing how new transport links
can transform access to jobs and services (TFL 2019). Similarly, New York’s subway system
analyses have demonstrated the relationship between transport accessibility and socio-economic
mobility (Kaufman et al. 2014).
For Maastricht, understanding the link between transport infrastructure and socio-economic
opportunity distribution is just as important. By examining this relationship, we can pinpoint
2
areas needing improvement for more equitable urban planning and policy-making, ultimately
enhancing the city’s socio-economic accessibility.
2.2 Objectives
1. Given two postal codes (zip codes), identify all feasible routes between them, including
public transport services such as buses, and walking or cycling.
2. Develop a tool to quantify accessibility for all postal codes in Maastricht.
2.3 Routing Engine and Data
For this project, you will need:
• Detailed geographical information of Maastricht’s Wijk (districts), areas (sub-districts),
and postal codes (specific neighborhoods).
• Public transport information such as bus stops, routes, and travel time.
• Socio-economic opportunities within Maastricht. These encompass services including
but not limited to cafes, restaurants, education, transportation, financial, healthcare,
entertainment, arts & culture, public services, facilities, and waste management 1
.
These will enable you to build a routing engine capable of computing different transportation
options from one address to another in Maastricht. Use this engine to identify areas more
isolated from specific opportunities.
3 Phases of the Project
• P1. Develop a distance and time calculator for specific locations.
• P2. Public Transport Routing (no transfers).
• P3. Public Transport Routing (with transfers) + accessibility calculations per postal
code.
3.1 Phase 1: Distance and Time Calculators
In this phase, you will design a simple distance calculator between two locations.
1Note that this list is non-exhaustive; you may find more opportunities to include in your project.
3
3.1.1 Tasks:
1. Develop well-structured code to calculate the distance ‘as the crow flies’ between two
postal codes within Maastricht, ignoring road structure and calculating aerial distance.
Use the MassLatLon.xlsx file. 2 All locations are in latitude and longitude which can
be used for distance/time calculations.
2. It is possible that some postal codes are not present in the file. For such cases, we have
provided a web service that will allow you to look up latitude and longitude given a
postal code. Select and apply a suitable design pattern for consuming a RESTful API in
order to access this service.
Use the API only if the postal code is not present in MassLatLon.xlsx. More information
on the API is present on computerscience.dacs.unimaas.nl.
3
3. Enforce a rate limit on API calls:
Period Limit
5 Seconds 1 request per IP
1 Minute 5 requests per IP
1 Hour 40 requests per IP
1 Day 100 requests per IP
4. Design a simple visualization to represent distances.
A simple plot/graph/map is sufficient at this point.
3.1.2 Requirements:
1. The distance and time must utilize latitude and longitude coordinates for its coordinate
system.
2. You must calculate time for both walking and cycling.
3. You must use kilometers and minutes as standard units of measurement.
4. Provide justification for your design choices for the calculator and API interactions.
2You can find all the project related files on Canvas and on https://computerscience.dacs.unimaas.nl/project12.
Please note you will need to use the UM VPN to access the project web page.
3You need to use the UM VPN to access this webpage.
4
3.1.3 To Pass:
• Have a working distance and time calculator that correctly determines the time and
distance for both walking and cycling. You must write this calculator yourself and it
must execute locally; you cannot use a cloud-based API or the internet for this
• Provide time and distance from DACS to Vrijthof (this should be in your slides).
• Create an elementary visualization, similar to flat public transport city maps. Include
minimal components (city boundaries, Wijk), scaling for distance, and time visualization.
3.1.4 Demo:
During your first presentation, we will give you two postal codes to find the distance, calculate
time, and present visualization.
3.1.5 Criteria for a higher grade (include):
1. Calculate the actual (not aerial) distance using the supplied .osm file and external libraries
(e.g., GraphHopper routing engine (GmbH 2023)). Note: You must still implement your
routing engine (as instructed above); using an external routing engine does not void that
requirement. The external library you choose should perform routing locally; you cannot
use a cloud-based API or the internet for this.
2. Develop an advanced, user-friendly graphical interface.
3. Ensure codai 写data、SQL de is clean and free from common coding flaws such as bad code smells.
3.2 Phase 2: Public Transport Routing
For this phase, you have been provided with a GTFS (General Transit Feed Specification4
)
dataset for The Netherlands. Calculate the distance and time between two postal codes, now
considering public transport options.
Assume that the only mode of public transport in Maastricht is by bus (ignore the Arriva train
connections between Randwyck, Centraal, and Nord stations).
3.2.1 Tasks:
1. Develop an efficient algorithm to find the closest bus stops, bus routes, and times for
trips for a given postal code.
4Please see Appendix for more information on GTFS.
5
2. Store the GTFS data5
in a well-structured relational database and write efficient SQL
queries.
Hint: Use indexes where appropriate.
3. Enhance your visualization to accurately depict bus routes.
You may use the city bus map as inspiration.
4. Write JUnit test cases and report code coverage statistics (aim for very high coverage).
3.2.2 Requirements:
1. Store GTFS data in a relational database. Optimize all SQL queries for performance
(minimal query wait time).
2. Calculate time by bus. Only direct connections count. If no direct connection exists
between two postal codes the GUI must indicate this.
3.2.3 To Pass:
1. Have a correctly implemented routing algorithm for finding the shortest direct route.
2. Include a GUI with all bus routes. Upon user input the GUI must indicate in some
manner which bus route the user should take.
3.3 Phase 3: Public Transport Routing (with transfers) + Accessibility calculation
per postal code
In this phase, focus on improving public transport routing by allowing transfers between buses,
enabling riders to reach more destinations as part of their journey.
Define a measure of socio-economic accessibility6
. Calculate the socio-economic accessibility of
every postal code to identify the most and least well-connected areas of Maastricht.
5You can download the GTFS file from Canvas and https://computerscience.dacs.unimaas.nl/project12.
6This is a term in use in the field of sociology. It has several related definitions and needs to be "filled out"
when considering a specific location.
6
3.3.1 Tasks:
1. Write an efficient routing engine that considers bus transfers.
2. Serialize and process the GeoJSON files7
for various amenities in Maastricht.
3. Compute a detailed accessibility metric for each postal code.
4. Refactor the code, if needed, to ensure good design practices are incorporated. Provide
an informal software architecture.
3.3.2 Requirements:
1. All previous requirements apply.
2. Handle the case where a postal code does not have access to a bus route, if such a case
exists.
3. If the user must wait in between buses, this time must be counted as part of the travel
time. See the Appendix for more information on how transit time must be calculated.
3.3.3 To Pass:
• Correct any errors from the previous phases.
• Have a functional routing engine with bus transfers. It must be integrated with the GUI.
• Efficiently calculate the accessibility measure for all postal codes in Maastricht (execution
time should be as minimal as possible).
• Defend your measure of socio-economic accessibility using the provided data.
3.3.4 Criteria for a higher grade include:
• Well-structured code (no bad code smells).
• Well-designed accessibility measure, backed by scientific literature.
• Visualization of accessibility (could be separate from routing visualization).
References
GmbH, GraphHopper. 2023. “GraphHopper Routing Engine.” https://github.com/graphhopp
er/graphhopper.
Kaufman, Sarah, Mitchell L Moss, Justin Tyndall, and Jorge Hernandez. 2014. “Mobility,
Economic Opportunity and New York City Neighborhoods.” NYU Wagner Research Paper,
no. 2598566.
Litman, Todd. 2017. Evaluating Accessibility for Transport Planning. Victoria Transport
Policy Institute Victoria, BC, Canada.
7See Appendix for more information on GeoJSON files.
7
Olsson, Lea, and Jan Loerakker. 2013. “Revisioning Amsterdam Bijlmermeer.” Failed
Architecture. https://failedarchitecture.com/the-story-behind-the-failure-revisioningamsterdam-bijlmermeer/.
TFL. 2019. “Crossrail Project 2019 to 2023: Completing the Elizabeth Line.” Association for
Project Management. https://www.apm.org.uk/v2/media/10vnyi1q/crossrail-project-2019-
to-2023-completing-the-elizabeth-line-apm-journal.pdf.
Appendix
General Transit Feed Specification (GTFS)
In phase 2 the primary data format you’ll work with is the General Transit Feed Specification (GTFS). GTFS is a common format for public transportation schedules and associated
geographic information. It’s used worldwide to power trip planners and transit applications,
making public transport data universally accessible and useful. GTFS helps various stakeholders
from transit agencies to developers and passengers in planning, analyzing, and using public
transport more effectively.
Understanding GTFS
GTFS consists of a series of text files (.txt) zipped into a single archive. Each file models a
particular aspect of the transit system:
• agency.txt: Details about the transit agency, including name, URL, and time zone.
• stops.txt: Information about individual stops, stations, or areas where vehicles pick up
or drop off passengers.
• routes.txt: Descriptions of routes in terms of long and short names, route type (bus,
tram, etc.), and route ID linking to other files.
• trips.txt: Trips for each route, including trip IDs and headsigns, linked to schedules and
sequences of stops.
• stop_times.txt: Times that a vehicle arrives at and departs from individual stops for
each trip.
• calendar.txt or calendar_dates.txt: Service dates, showing when services are available
or exceptions occur.
• ... and potentially more, depending on the agency’s data.
8
GTFS data allows you to map out routes, calculate distances and travel times, and understand
service patterns. In this phase, you’ll develop algorithms to parse GTFS data, find the closest
bus stops, and calculate the fastest routes.
GeoJSON files
GeoJSON is a standard format for encoding geographic data structures, making it an essential
asset for mapping and spatial analysis. You’ll use this data to identify amenities, shops, tourism
spots, and other points of interest, understanding how they contribute to the socio-economic
landscape of Maastricht.
• GeoJSON Data.zip:
– Tourism (tourism.geojson): Features 259 points of interest related to tourism
like museums and historical sites.
– Shops (shop.geojson): Details about 697 shopping locations and stores, providing
insights into commercial accessibility.
– Amenities (amenity.geojson): Contains 1173 features representing various public
amenities and facilities.
Consider how access to different amenities, shops, and tourist attractions might influence the
quality of life and opportunities available to residents and visitors alike.
Calculating transit times when bus transfers are included
In our example, at some time "start", a user is attempting to travel from alpha to beta by
taking bus b1 to transfer point T, where they exit b1 and board bus b2 for the rest of the trip
to beta.
Informally, the below paragraphs state: You can assume the user will take the buses and
transfers that minimize the time required to get from alpha to beta.
More formally, let us assume we have a function spot(b, pc), which returns the time at which
bus b is at location pc and is undefined if b is never at pc. If we stick to just those (b, pc)
where spot is defined, we can now define:
• wait = spot(b1, alpha) - start where wait >= 0
• t1 = spot(b1, T) - spot(b1, alpha) where t1 > 0
• transfer = spot(b2, T) - spot(b1, T) where wait >= 0
• t2 = spot(b2, beta) - spot(b2, T) where t2 > 0
9
If we assume that the user will minimize wait, t1, transfer, and t2 (e.g., by not aimlessly riding
the bus around all day) then we can define:
• tot = wait + t1 + transfer + t2
Note that tot is a function with 6 parameters (alpha, start, b1, T, b2, beta). In order to find
the shortest travel time you must minimize tot. You will be given alpha, beta, and start, you
can choose any value for the other parameters (b1, T, b2) given the restrictions described
above.
Note that the term "bus" is being used in a technical sense here. For example, if the Number 6
bus stops in alpha at 9:45 and 10:12, they are considered two separate buses for the purposes
of this calculation.
Start-independent total time: What if the user does not care when they leave, but just
wants to know the shortest time it takes to get from alpha to beta whenever that may be?
In that case, wait is always 0 8
. As before, you will be given alpha and beta. tot is now
obtained by calculating tot over all values of (b1, T, b2) and taking the minimum result