Fullpaper Format

4 downloads 8775 Views 196KB Size Report
warehouse to create DTTM. On the ... on creating speed profiles [4]. ... using database's custom-written functions into the schema described in Section 3.1. ..... [1] “EXAT ITS for iPhone, iPod touch, and iPad on the iTunes App Store” Available at.
Profiling Bangkok Travel Time Using a Data Warehousing Approach Pimwadee Chaovalit Researcher, National Electronics and Computer Technology Center 112 Phahonyothin Road, Khlong Nueng, Khlong Luang, Pathum Thani, 12120, Thailand TEL: +66 2564 6900 ext.2622, E-mail: [email protected] Tanachart Kohprasurt Co-researcher, National Electronics and Computer Technology Center 112 Phahonyothin Road, Khlong Nueng, Khlong Luang, Pathum Thani, 12120, Thailand TEL: +66 2564 6900, E-mail: [email protected] Wasan Pattara-Atikom Researcher, National Electronics and Computer Technology Center 112 Phahonyothin Road, Khlong Nueng, Khlong Luang, Pathum Thani, 12120, Thailand TEL: +66 2564 6900 ext.2626, E-mail: [email protected] ABSTRACT Most public travel time information services nowadays are static. They give estimated travelling time from any point A to any point B, regardless of hours of day, days of week, or holiday patterns. However, for a heavily trafficked city with quite unpredictable traffic like Bangkok, static travel time information may not be informative enough for commuters. We propose constructing travel time profiles based on attributes of time, day, and roads for a road network. Using a data warehouse schema, we demonstrate modeling dynamic travel time profiles of Bangkok roads. In future, other types of interesting information such as major sports events or road closures can add more richness to the model. The study shows that the data warehousing approach can empower traffic controllers and road users with answers to various types of travel time questions regarding various constraints. Keywords: Travel Time Estimation, Data Warehouse, Star Schema Design 1. INTRODUCTION In today’s urbanized society, the ability to obtain an accurate estimation of travel time based on different times of day or different days of week can be immensely beneficial. In Thailand, we still lack dynamic travel time estimation system for the general road network. To our best knowledge, dynamic travel time information service is provided by the Expressway Authority of Thailand [1] and is limited to the expressway network. With available travel time data on roads across Bangkok over a period of time, we can construct a data framework and system which stores and reports travel time statistically and 1

dynamically. The profile system can allow data selection in higher granularities if travel time data in a lower granularity is too sparse (for example, instead of querying road profile for a specific Monday, select travel time profile for Mondays). We choose data warehousing as our approach because 1) with data warehousing, the travel time profile of roads can be answered from a set of data. and 2) it allows more flexibility in generating travel time profiles as users of data warehouse can change the conditions of queries which they ask from a database [2]. This rest of this paper is organized as follows. Section 2 describes related work in profiling travel time. Section 3 illustrates our methodology which utilizes data warehouse for profile construction. Section 4 discusses preliminary evaluation on the accuracy of the travel time profiles. Section 5 provides summary of this work. 2. RELATED WORK Pfoser et al. proposed a construction of Dynamic Travel Time Maps (DTTM) which assign weights to historical travel time [3]. The work described in [3] uses spatiotemporal data warehouse to create DTTM. On the other hand, some commercial products may be working on creating speed profiles [4]. To the best of our knowledge, the speed profiles has yet to reach information coverage of Thailand. 3. METHODOLOGY 3.1 Travel Time Data Warehouse Design Star Schema is a relational database model used in data warehouse design. It comprises one fact table and several dimension tables. All tables in the star schema is joined in a star-like shape [2]. The fact table in the star schema stores core measurements of the database, which in this case is travel time. Dimension tables store attributes of travel time, such as time of day and day of week. In other words, dimension tables contain constraints which will be asked in travel time profiles. They are designed based on attributes which travel information users might be interested in. Besides, dimension tables are flat and unnormalized to avoid excessive joining among tables, thereby supporting easier browsing. Data warehouse design for Bangkok travel time profile is presented in Figure 1. Fact Table One set of travel time summary, for each combination of travel time for a specific road link at a specific time of day on a specific day, is stored within fact table. Travel time records are identifiable by three primary keys: day_key, time_key, and link_key. A set of travel time summary contains sufficient information for incremental calculation of average travel time for each combination. Those pieces of information are a) the number of information items in this specific combination group, b) the sum of travel time for this specific combination group, c) the sum squared of travel time for this specific combination group. With information pieces a), b), and c), we can further correctly calculate the average travel time and the standard deviation of travel time within that specific combination of road link at a specific time on a 2

specific day.The summary of travel time can be accurately aggregated over various day_key and/or time_key and/or link_key, as proved in [3].

Figure 1. Star Schema of Link Travel Time Profile Road profiles produced from our proposed implementation can therefore be accompanied with information such as the number of data items from which the profile is processed and the variance of travel time for the profiles. In addition, we can provide the date range of data items from which the profile is processed in the mindate and maxdate columns. Time Dimension Time dimension allows the analysis of travel time for different times of day. Implementing the Time dimension enables us to query travel time profiles during rush-hour periods, non rushhour periods, daytime, nighttime, and so on. Day Dimension Day dimension stores various descriptions of each day, for example, weekdays, weekends, and holidays. Day dimension is essential because we need to slice travel time data by various aspects of days, for instance, workdays or holidays. In addition, we may need to know travel time by seasons, or by major events like the country-wide first day of school. Describing these characteristics within a day assists us in querying those particular days for constructing interesting profiles. Link Dimension Link dimension will help us group travel time by road links. All road links differ in aspects such as road widths (described in meter), road surface (e.g. concrete, asphalt, laterite), the number of traffic lanes, and road types (e.g. highway, country road, bridge). These additional information on road links will allow us to produce travel time profiles by different road types or road widths, if needed. 3

Event Dimension Event dimension is a future extension of the current implementation. It can help us determine travel time profiles according to events, e.g., increased travel time due to local sports events, which leads to higher traffic than usual in respective areas or decreased travel time due to a popular television program on-air, which results in commuters staying off the roads. 3.2 Processing Travel Time Raw Data Into Star Schema Travel time data was processed from probe vehicle’s locations and timestamps. The travel time data described within this paper was extracted from October 2012 to March 2013, with a total of over 11 million records. Each record of data contains timestamp of the record, probe vehicle ID, road link, speed, and spot travel time, which was calculated from the length of road link divided by spot speed. All records of raw travel time data were processed offline using database’s custom-written functions into the schema described in Section 3.1. The postprocessed schema contains over 5 million rows, a factor of 0.5 to the original data due to the effect of data aggregation. An overview of the processing stage is depicted in Figure 2.

Figure 2. Star Schema of Link Travel Time Profile Even though the implementation described in this paper was carried on in an offline manner, a periodical update batch processing is possible using database trigger that will automatically process travel time raw data to be stored in the schema. Periodical batch processing is a more widely used technique in data warehousing, compared to online implementation, as the online implementation has a constantly heavy processing load. 3.3 Travel Time Profiles We implemented a proof of concept prototype for dynamic profiles of Bangkok road network. All dimension tables (day, time, and link) were built and populated in advance so that fact table records can be generated on-the-fly after receiving the raw travel time data. Table 1 shows travel time information from 4 different profiles of 4 different road links. As an example, four potentially useful profiles are queried from the data warehouse. The profile descriptions are: Profile 1: General Profile of the Road Link Profile 2: Rush Hours on Work Days (i.e. not weekends and not a part of long weekends) Profile 3: Weekends (including long weekends) Profile 4: Rush Hours Before Long Weekends 4

Table 1. Four travel time information profiles of four different road links Link ID

Road Name

Road Length

Profile 1

Profile 2

Profile 3

Profile 4

450392

Highway 7 (Motorway)

2.4 kms

2.87 mins (n=3542)

4.16 mins (n=759)

2.16 mins (n=1337)

7.32 mins (n=129)

565602

Highway 7 (Motorway)

5.3 kms

3.64 mins (n=2621)

4.08 mins (n=504)

3.52 mins (n=1106)

3.75 mins (n=66)

6966

Bang Na Trat Rd.

1.2 kms

3.44 mins (n=2517)

2.23 mins (n=738)

6.04 mins (n=866)

1.70 mins (n=97)

450553

Wongwaen Rop Nok Tawan Ok Rd.

2.7 kms

7.67 mins (n=2599)

17.79 mins (n=553)

2.54 mins (n=926)

2.81 mins (n=70)

Table 2. Average (standard deviation) execution time to query the profiles Link ID

Profile 1

Profile 2

Profile 3

Profile 4

Query time for all 4 links

1.5(0.2) secs

1.5(0.3) secs

1.5(0.3) secs

1.4(0.1) secs

The profiles show different characteristics of the road links. For example, most links have a shorter travel time for long weekends while Link 6966 takes longer to travel in weekends. Other interesting profiles can also be defined and queried. For instance, we may want to know the expected travel time on Children’s Day, which is celebrated annually on the second Saturday of January. Query execution time is reported in Table 2. The execution time result was conducted on a local database on a Macbook Pro with 2.3 GHz Intel core i5 processor with 8GB of RAM. 3.4 Profile Extensibility Previously, we have shown four distinct travel time profiles for Bangkok road sections and how various profiles have different characteristics which can aid commuters in their transit decision making. In this section, we show a system implementation that profiles can be created extensively and creatively around the designed star schema. We have implemented a total of 31 different profiles based on their different definitions described in Table 3. Table 3. Average (standard deviation) execution time to query the profiles Profile Number 1

Profile Name

Profile Description

Valentine’s Day Travel time queried from February 14th of every year Morning Traffic during the time of 7-10 am. Traffic might be heavier in some areas that are famous for wholesale flowers in Bangkok. 5

Profile Number

Profile Name

Profile Description

2

Valentine’s Day Travel time queried from February 14th of every year Evening Traffic during the time of 6-10 pm. This traffic pattern can be found around malls and restaurants, since people will dine out or go on dates significantly on Valentine's Day Evening.

3

Songkran Festival Travel time queried from April 12th-16th of every year Morning Traffic during the time of 7-10 am. In Bangkok, traffic is these mornings maybe lighter than usual in some residential areas, while heavier in some streets famous for playing Songkran. Songkran, a water splashing festival, is celebrated in Thailand officially during April 13th - 15th. However, the country often observe Songkran during an extended period of up to 5 days.

4

Songkran Festival Travel time queried from April 12th-16th of every year Evening Traffic during the time of 3-10 pm. Traffic nearby some streets, such as Khao San Road (a famous backpacker street in Bangkok) will be especially heavy.

5

Father’s Day Travel time queried from December 5th of every year Morning Traffic during the time of 7-10 am.

6

Father’s Day Travel time queried from December 5th of every year Evening Traffic during the time of 6-10 pm. Bangkokians tend to dine out or visit family today.

7

Mother’s Day Travel time queried from August 12th of every year Morning Traffic during the time of 7-10 am.

8

Mother’s Day Travel time queried from August 12th of every year Evening Traffic during the time of 6-10 pm. Bangkokians tend to dine out or visit family today.

9

Labor Day Morning Travel time queried from May 1st of every year during Traffic the time of 7-10 am. Many workers, including bankers, are given a day off on Labor Day, therefore potentially leading to lighter morning traffic.

10

Labor Day Evening Travel time queried from May 1st of every year during Traffic the time of 5-9 pm. Many workers, including bankers, are given a day off on Labor Day, therefore potentially 6

Profile Number

Profile Name

Profile Description leading to lighter evening traffic.

11

Beginning of Month Travel time queried from the first week of every month Morning Traffic during the time of 7-11 am.

12

Beginning of Month Travel time queried from the first week of every month Evening Traffic during the time of 3-7 pm. The Beginning of Month signifies the period of time when Bangkokians’ salaries are recently paid. This can lead to more spending and higher traffic pattern.

13

Ending of Month Travel time queried from the 4th and 5th weeks of every Morning Traffic month during the time of 7-11 am.

14

Ending of Month Travel time queried from the 4th and 5th weeks of every Evening Traffic month during the time of 3-7 pm. The Ending of Month could mean less spending in general and lower traffic pattern.

15

Christmas Day Travel time queried from December 25th and of every Morning Traffic year during the time of 7-10 am.

16

Christmas Day Travel time queried from December 25th and of every Evening Traffic year during the time of 5-9 pm. Some Bangkokians may lightly adopt this holiday and celebrate it on Christmas Day, instead of Christmas Eve as celebrated in some western countries.

17

Weekend Traffic

Morning Travel time queried from weekends during the time of 7-11 am. The traffic pattern here should be quite different from weekdays morning.

18

Weekend Traffic

Evening Travel time queried from weekends during the time of 6-10 pm. The traffic pattern here may be different from weekdays evening.

19

Regular Traffic

Monday Travel time queried from Regular Mondays (i.e. not part of a long weekend) during the time of 7-9 am and 5-8 pm.

20

Regular Monday Travel time queried from Regular Mondays (i.e. not part Morning Traffic of a long weekend) during the time of 7-10 am. Monday morning traffic is notorious in Bangkok for having very bad traffic.

21

Regular

Monday Travel time queried from Regular Mondays (i.e. not part 7

Profile Number

Profile Name Evening Traffic

Profile Description of a long weekend) during the time of 5-8 pm. Monday evening traffic can potentially be heavier than other days in a week.

22

Regular Traffic

Friday Travel time queried from Regular Fridays (i.e. not part of a long weekend) during the time of 7-10 am and 5-8 pm.

23

Regular Friday Travel time queried from Regular Fridays (i.e. not part Morning Traffic of a long weekend) during the time of 7-10 am. It would be interesting to study the pattern of Friday morning traffic.

24

Regular Friday Travel time queried from Regular Fridays (i.e. not part Evening Traffic of a long weekend) during the time of 5-8 pm. Friday evening traffic is notorious in Bangkok for having very bad traffic.

25

Workdays Traffic

Morning Travel time queried from Mondays to Fridays during the time of 7-10 am.

26

Workdays Traffic

Evening Travel time queried from Mondays to Fridays during the time of 5-8 pm.

27

Children’s Day Travel time queried from the second Saturday of Morning Traffic January during the time of 7-10 am. On Children's Day, Government places such as the House of Parliament, Air Force Base, or companies such as TV broadcasting companies, hold open houses for families with children to visit.

28

Children’s Day Travel time queried from the second Saturday of Evening Traffic January during the time of 3-9 pm.

29

New Year Morning Travel time queried from December 30th to January 2nd Traffic of every year during the time of 7-10 am. New Year’s have heavy traffic for connection roads with upcountry, but traffic frees up downtown more than usual.

30

New Year Evening Travel time queried from December 30th to January 2nd Traffic of every year during the time of 3-9 pm. New Year’s have heavy traffic for connection roads with upcountry, but traffic frees up downtown more than usual.

31

New

Year

Night Travel time queried from December 30th to January 2nd 8

Profile Number

Profile Name Traffic

Profile Description of every year during the time of 12-4 am. At night, some commuters are travelling long distance on roads to and from the upcountry and may create higher traffic than usual.

4. PRELIMINARY EVALUATION ON ACCURACY We conducted a preliminary comparison on travel time actual travel time and estimated travel time given by the profiles. The experiment was set up as follows. Three students volunteer to record travel time of their trips in Bangkok during July 2013. A total of 12 trip records were reported. Since road links are short sections of road, each recorded trip is comprised of various road links. An estimated time for a trip combines estimated time from multiple road links included in the trip. Figure 3 shows records of all 12 trips, each with estimated travel time and real travel time.

Figure 3. Comparison of estimated travel time and real travel time of trips in Bangkok The estimated travel time displayed in Figure 3 were estimated for traveling over several road links from the historical records of the same day of week at the same time with the trip. For example, If the trip occurred Thursday afternoon at 1:30 pm, the estimated travel time will use a custom profile queried from travel time of all past Thursdays at 1:30 pm. Figure 3 shows that in 10 out of 12 trips, estimated travel times differ from actual travel time for only a few minutes, ranging from 0 to 6 minutes per trip. In 2 out of 12 trips (Trips number 3 and 4), there are a high difference (14 and 26 minutes) between estimated and actual travel time. Our volunteers explained that the vehicles were traveling especially faster than the estimated time because traffic on different lanes have distinctly different patterns. The slow lanes were much slower due to traffic waiting to turn left, while the volunteer was on a much faster lane. 9

Another reason for errors is that different types of vehicles (i.e. bus, taxi, private car, motorcycle) have different driving pattern, that is, buses are much slower than private cars and taxis because they have to periodically stop at bus stops. Motorcycles in general can be a little bit slower than other types of vehicles in free-flow traffic, but much faster than buses, private cars, and taxis in congested traffic due to their ability to swerve in and out of lanes. Overall, our experiment is still in the process of data collection and more data is needed to draw certain conclusions. However, the trend shows that in most cases estimated travel time is close to actual travel time, which means that historical travel time has the potential for travel time estimation. In a few exceptional cases, travel time is estimated very far from actual travel time due to reasons outlined above. 5. SUMMARY AND DISCUSSION This paper discusses an approach to make profiles of dynamic travel time for road networks. This paper contributes a data warehouse design framework which allows for aggregating travel time summary over different times, days, and road links to generate various kinds of travel time profile. We apply the framework to Bangkok travel time profile. Such a travel time profile generation might not be possible or easily queried with other data structures. The implementation of travel time profile is initially conducted on 31 specialized profiles on our system. Users can also query travel time by defining days of week and time duration of day. The implementation shows high potential of using such data warehouse framework to store travel time data. The framework possesses flexibility to create custom profiles and adaptability to trip travel time estimation. The evaluation of this system is in progress with more historical data needed in order to smooth out outliers present in data. Moreover, if probe data collected from specialized vehicles such as motorcycle are available, it may better describe travel time profiles by various vehicle modes. 6. REFERENCES [1] “EXAT ITS for iPhone, iPod touch, and iPad on the iTunes App Store” Available at https://itunes.apple.com/us/app/exat-its/id524332817?mt=8 (January 10, 2013) [2] Kimball R. and Ross M., The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (2nd edition), Wiley, April 26, 2002. [3] Pfoser, D., Tryfona, N, and Voisard A., “Dynamic Travel Time Maps - Enabling Efficient Navigation” in Proceedings of the 18th International Conference on Scientific and Statistical Database Management (SSDBM06) (Vienna, Austria, 2006). [4] “TomTom Historical Traffic – Predict Traffic Flow”. Available at http://www.tomtom.com/en_gb/licensing/products/traffic/historical-traffic/speed-profiles/ (August 2, 2013)

10