How to draw the bus route

c-pearl.com/bus_route.aspx

First, I need to have the data of the bus services and the bus stops in sequence. This data is provided by the Land Transport Authority. But the data comes in a complicated format. I have to write a script to extract the data that I needed in a form that is easy to use.

Second, I have to write a stored procedure to display the bus stops (and the GPS locations of the bus stops) in the sequence for each bus service. The GPS locations for each stop are obtained from another table.

Third, I have to write a webpage that display the data from Google Map. It also uses the Google API to plot the route of the bus by joining all the stops using the GPS location. This API has a function called "snap to road". It will attempt to draw the route to follow the road. It works most of the time, but does not work well when the bus stops are far apart.

Here is the map of the route for a few services. They are quite attractive.