School of Civil and Environmental Engineering
Term 2, 2024
CVEN9415: Transport Systems Part II
Assessment 3: Data Analysis and Simulation Techniques (Individual Assessment)
PART A: Data Analysis (20 Points)
Inputs required (refer to Input Data Description Document on Moodle)
• Intersection diagram files
• Traffic counts CSV file to be opened with SQLite browser. PLEASE name the SQL Table as
countsdata in SQLite Browser. NOT doing so will attract penalty
Solve the questions given below for the following TCS IDs: 686
Questions Points
1 Provide the intersection diagram, Google Satellite image of the site 2
2 Discuss the intersection layout based on the following: 3
A Determine the postcode where this intersection is located 1
B Detector IDs per approach 2
3 Student specific: Give the total hourly traffic counts on each incoming approach on all
Wednesdays and Sundays for the month and time allocated to you (in Q3 excel sheet),
answer the following:
7
A Write an SQL query to extract the total, average, maximum and minimum traffic count
information separately for Wednesdays and Sundays for the given inputs
3
B Tabulate and compare the statistics obtained in step (A) 3
C Comment on the observation made in the tables 1
4 Student specific: Give the total daily hourly traffic counts on each incoming approach
for the first 7 days of the month and 2 time periods allocated to you (in Q4 excel sheet),
answer the following:
8
A Write an SQL query to extract the total, average, maximum and minimum traffic count
information separately each time period for the given inputs
3
B Tabulate and compare the statistics obtained in step (A) 4
C Comment on the observation made in the tables 1
Total 20
# Students MUST refer to the spreadsheet “Student Allocation.xlsx” and its association sheets on
Moodle to get the time period(s) assigned to them. NOT complying with the made allocations will
result in ZERO Points.
PART B: Simulation Techniques
Use Excel wherever necessary and provide screenshots of results. You can also upload the
spreadsheet showing the calculations (Optional).
Questions
1. Develop a Linear Congruential Generator (LCG) using the properties given by Hull and Dobell
(1962). Consider m=500 in the LCG.
Steps Point
A Show calculations to generate the first 3 random numbers from this LCG. 1
B Determine the period of this LCG by generating more random numbers (use excel if
necessary). Is it consistent with the property proposed by Hull and Dobell?
1
C Plot the first 100 random numbers (Ri) from step (b) on a number line. Comment on
the plot
KS 1 Point
Plot 1 Point
2
2. Halton sequence:
i. Generate a random number using the equation: Xi = (171*X0 + 83) mod 373 where the seed
value (X0) is the last 2 digits of your ZID.
ii. Pick the 1st 2-digit prime number that appears in the resulting Xi sequence. Use this prime
number to generate a Halton sequence comprising 200 random numbers.
Steps Point
A Provide pseudo-code for generating Halton sequence. Discuss each step of the
pseudo-code
2
B Provide screenshots of the command used to generate the remaining Halton
sequence
1
C Plot the first 100 random numbers (Ri) on a number line. Comment on the plot and
compare it with the one presented in Question 1 (c)
2
3. A wheel of fortune is divided into ten equal sectors numbered from 1 to 10. Devise a Monte Carlo
simulation of this roulette and produce the result of five spins for which each outcome is given by
the following three events:
A: x < 3 B: 3 ≤ x ≤ 7 C: x > 7
Generate random number using the LCG: Xi = (172*X0 + 17876) mod 30307 where the seed value
(X0) is the first 4 digits of your ZID. Show necessary calculations. Comment on the obtained result
and how it can be improved (3 Points)
4. A random variable Y follows Gumbel distribution, the CDF of which is: ( ≤ ) = −
−
. Simulate
10 random numbers from this distribution using the LCG: Xi = (171*X0 + 24316) mod 30269 where
the seed value (X0) is the first 4 digits of your ZID. Show necessary calculations. (3 Points)
5. Passengers arrive at an airport check-in counter at an average rate of 54 passengers/hr. The
operator at the counter has a variable service time (following a distribution B) with an average of
1.5 minutes per passenger. Use the following LCG to simulate random numbers: LCG: Xi = (170*X0
+ 22834) mod 30323 where the seed value (X0) is the first 4 digits of your ZID.
a. Are analytical methods of queueing applicable to the available information? Give reasons (1
Point)
b. Generate a sequence of 7 time intervals of duration 3 minutes each. The model should be
able to calculate the number of arrivals, departures and queue length within each time
interval. Show necessary calculations. Assume that the toll booth is empty at time zero (5
Points)
c. Simulate first 5 passengers that enter the system, assuming that the first passenger enters
at time zero. The model should be able to calculate the time that each passenger spends in
the waiting line and the percentage of time that the operator is idle. Show necessary
calculations (9 Points)
Resources
1. VB code for Halton:
https://books.google.com.au/books?id=1JQSRLPnqBwC&pg=PA131&lpg=PA131&dq=generate+h
alton+draws+in+Excel&source=bl&ots=4M8sQYh7dl&sig=ACfU3U3rg0cAG2ICp-
xW7XikZnqe7igJRA&hl=en&sa=X&ved=2ahUKEwiU2fjXj9HiAhWO4nMBHa-
LDjwQ6AEwDXoECAgQAQ#v=onepage&q=generate%20halton%20draws%20in%20Excel&f=false
2. Nice Halton Animation: https://observablehq.com/@jrus/halton
Upload the following files:
1. Main File (Mandatory): This file must contain solutions to the above questions. The uploaded
file (in PDF format) should be named as ZID.pdf.
2. Supplementary File (Optional): This file (Excel spreadsheet) shows the detailed calculations
(like the random numbers used, formulae, etc.) made for the above questions.
Submission deadline through Turnitin: Friday 12th July 2024 by 04:00PM