Besides providing the capability to monitor the fleet real-time, the system also reports mechanical and several other operational incidents.  Incidents are transmitted through the radio waves, which are shared with voice communications.  The data is stored in an Oracle database.  Although incident data is very useful, it is not enough for a more detailed analysis.

Nevertheless, our AVL system has the capability of sending data every two minutes (poll rate).  Data includes:  Route, Run, DateTime stamp, Speed, Operator ID, Vehicle number, Schedule Status (not active yet), Latitude, Longitude, etc.  A “snapshot” of real-time data is written to an Oracle table; however, it is replaced with new data every two minutes.

The Challenge

Real-time data is constantly reported, but not collected by the system.  If we were able to collect this data, the potential uses of the data would be tremendous.  For instance, bus speeds could be determined for different times of the day, allowing the transit agency to determine accurate running times between time points (pre-determined locations along the route).  With realistic running times it would be easier for bus operators to adhere to schedule.  In addition, bus locations could be displayed on a map using Geographic Information Systems (GIS) by route, time of day, or day of week.  This information could be used to determine if buses were ahead or behind schedule.

Having said that, the challenge is to store this data to be used for further analysis.  The main problem is that to be able to do something like this, the vendor would have to create a program that would be able to capture the data and store it into an Oracle table.  Further, provisions would have to be made as far as disk space, due to the constantly growing data.  Finally, there would be the question of whether we really need all these data to be collected continuously, or just for a limited period of time for analysis purposes.  There is the additional consideration of the time it will take for  the vendor to accomplish this and, of course, the additional cost to the agency.

The Solution

So why not create a program to capture this data and store it somewhere else?  After wrestling with the idea for a while, it occurred to me that if the data is constantly being sent to the Oracle table, all I needed was a program with a timer in order to collect the data every two minutes and a place to store the data.

Consequently, I created a program and a form with a timer, which was set up to be used with the OnOpen event.  The program starts collecting data when the form opens and when the form closes the data collection stops.  In other words this process can be turned On and Off by simply opening and closing the form.  The timer starts when the form opens, and at 2-minute intervals the rt_dat.prg is run.  The program basically “grabs” the data from the Oracle table every two minutes and stores it into a dbf table.  Below is the code for the form and the program that collects AVL data.

 

	Procedure Form_OnOpen    This.timer1 = new timer()    This.timer1.parent = this    // Form    This.timer1.interval = 120  // 2-minute polls    This.timer1.OnTimer  = this.avl     This.timer1.enabled  = true    return  Procedure Form_OnClose    This.timer1.enabled = false    This.timer1.parent = null Function avl()    do rt_data.prg    return

// tr_data.prg d = new Database( ) d.databaseName = "AVL_DATA" d.loginString = "username/password"  // Oracle Login d.active = true q = new Query( ) q.database = d q.sql = "select * from RT_DATA"      // Real Time Data q.active = true u = new UpdateSet( ) u.source = q.rowset u.destination = "AVL.DBF"            // Data repository if _app.databases[ 1 ].tableExists( "AVL.DBF" )    u.append( ) else   u.copy( ) endif

Data Collection and Analysis

Data was collected for a period of one week.  For this time period, there were more than 800,000 records collected into the dbf table.  Once the data is in dBASE, data manipulation becomes easy, not to mention the speed of querying dbf  tables.

This simple program opened a window of opportunities, enabling us to collect useful data and allowing us to do a more comprehensive analysis of our transit system.  For instance, figure 2 shows a picture of a GIS map created with the AVL data.  Similarly, figure 3 depicts a speed chart for route 18 from 5:30 am to 8:30 am.  Now, with this newly added functionality, data can be collected and intelligent decisions can be made on how to improve the operations of our transit system.

As you can see, the versatility of dBASE is just amazing.  Together with a little bit of creativity the results can be considerable.  To summarize, by creating a simple dBASE program, data can be collected “real time” from an  Oracle table and stored into a dBASE table, which serves as repository of the data.  Once the data is in dBASE, queries and general data manipulation become faster and easier.  Data can then be transformed into useful information, which will be beneficial to the transit agency.

It is my hope that this article will entice you to explore dBASE beyond its regular uses.  The functionality is there!