When I’m travelling on Singapore Mass Rapid Transit (SMRT), I often make myself busy with my HP RX3715. Especially when the MRT is not crowded and I’m lucky enough to get a seat. The comfort condition is really supporting your creative mind, so better not waste it. Most of the time, I will open up Pocket Word, and jot down whatever ideas running in my head. Occasionally, I will revisit my old ideas and amend if not improve it.
I have been had this idea of creating a mini Bus Guide in Street Directory-style in my HP RX3715 for quite sometimes. Although I yet to have clear way on the implementation, I’d like to share the idea with you all hoping for your feedbacks.
ALAS, apparently such guide is already created. But it is a static document. Anyway, I will carry on with this project, hopefully I will learn something about Embedded System utilising J2ME/.NET Compact Framework/C++ from this.
So far my idea is represented in T-SQL syntaxes.
/* Table for Bus */
Create Table Buses (
Bus_ID int Identity(1,1) not null,
Bus_Name varchar(6) not null
/* Table for Bus routes */
Create Table Bus_routes(
Bus_Stop_Address varchar(64) not null,
Seq_Number int /* to make routes possible */
Get available bus in a particular address
Select distinct Bus_Name from Buses b inner join Bus_routes br on b.Bus_IDr =br.Bus_ID
where Bus_Stop_Address like ‘%@street%’ order by Bus_Name
Select a bus based on Start and destination, Direct Bus
Parameter: @route_start, @route_end
Select distinct Bus_Name from Bus_Routes a inner join Bus_Routes b on a.Bus_ID= b.Bus_ID and
a. Seq_Number <= b.Seq_Number inner join Buses bs on a.Bus_ID=bs.Bus_ID
where a.Bus_Stop_Address like ‘%@route_start%’ and b.bus_stop_address like ‘%@route_end%’
order by bs.Bus_Name
View Bus’ route
Select Bus_Stop_Address From Buses b inner join Bus_Routes br on b.Bus_ID=br.Bus_ID
Where b.Bus_Name = ‘@busname’ order by br.Seq_Number
Indirect Route with the smallest number of changing Bus
I’ll think about this on my next travel on MRT 🙂