Skip to content

Sms queries

Jump to bottom
shekhark edited this page Jan 12, 2013 · 1 revision

This project began in February 2011 as "SMS-BEST" and our original aim in the lab was to use the [BEST-Database] with the RapidSMS web framework for serving the BEST's raw database over SMS.

Our idea was a multi-lingual audience and entry in as few characters as possible. One should get the needed info in no more than one or two messages (user query & server answer).

SMS stands for Short Messaging System. Simple semantics is the key. In 160 characters or less, serve information based on simple names of stops and areas, and numbers of bus routes.

Table of Contents

Scheduling

All SMS queries are stamped with time and day. All queries should be time and day-sensitive, calculating based on the times, runtimes and frequencies in the db, to return the most current data on running busses.

  • What to return when no busses available?
The relevant columns in the Atlas database for time and day are first_from, last_from, first_to, last_to, runtime1, runtime2 and runtime3 and frequency1, frequency2 and frequency3 (now called headway in new db) and schedule.
  • first_from is the time of first bus from start depot.
  • last_from is the time of the last bus from start depot.
  • first_to is the time of the first bus from the end depot.
  • last_to is the time of the last bus from the end depot.
  • runtimefor travel time in minutes from the full route for peak and non-peak hours, and is often divided into two or three times of day. We need to clarify which hours these are from BEST.
  • frequency now called headway which is the number of busses departing on the route per runtime -- the lower this number, the higher the frequency of busses departing and arriving in a runtime.
  • schedule is the days of the week running some variation of Mon-Sat, Sun-Hol, Holidays, Sundays, All Days.
Filtering for the above criteria based on the time and day of the query, return the route number, names, and time of one or two busses which last left depots at either start or end of the route.

Route Queries

These are the most simple queries, simply send a route number and return the last bus which left each end of the route.

User input for route should be just a number with the handler "route", not the exact string with suffixes or prefixes in use on BEST:

    • "-LTD" Limited Stops
    • "-Exp" Express
    • "C-" Corridor
    • "A-" or "AS=" Air Conditioned

Simple Route Query 

Q: route 6
A: 6 LTD from NAVY NAGAR 8.14am, ELECTRIC HOUSE 8.04am, from B.A.R.C. 8.16am, MAHUL 8.10am, TROMBAY 8.06am
  • Simple route query determines departures from depot using first_from and first_to based on time of day frequency and return all times from all depots on the route.
  • In most cases there will only be one or two up or down busses leaving on the same route at any given time of day.
  • Input given only as number, reply given with full route "6 LTD" as per BEST database.
  • Unlike with all other queries below, simple route queries come without direction of the bus, so we return times for both ends in the same message.

Route + Stop Query 

Q: route 6 + stop B.A.R.C.
A: 6 LTD leaves NAVY NAGAR 8.14am, arrives B.A.R.C. 9.54 a.m.
  • Combine route query with stop query to determine direction and calculate arrival time at end depot using first_from and runtime.
  • Same type of query can be used to determine the approximate arrival at any stop on that route, using first_from and runtime1 in Atlas table with stop_sr and stop_code in Routes, dividing the number of stops in stop_sr by the minutes of the journey in runtime.

Area and Stop Queries

Many Stops and Areas on a Route

This is a entry for our sample route 6LTD from the BEST Routes table 6LTD has 58 stops between Colaba and Trombay which are numbered in stop_sr and identified by their stop_code which can be looked up in the BEST Stops table. stage_code is redundant since it is always TRUE if distance is covered and false otherwise, which can also be determined from kms. The area_code for each stop can be looked up in the BEST Areas table.

Area to Area Query

Area + Area should return stops and times if only one route is found (there is only one bus from Colaba to Trombay, but many from Colaba to Mahim). 

Q: area COLABA + area TROMBAY
A: 6 LTD from NAVY NAGAR 8.14am, ELECTRIC HOUSE 8.04am, from B.A.R.C. 8.16am, MAHUL 8.10am, TROMBAY 8.06am

When there is more than one route found, return the numbers of Routes and time left from depot. 

Q: area HUTATMA CHOWK + area MAHIM
A: Route 1 LTD from R.C. CHURCH HH.MMam, Route 4 LTD from HUTATMA CHOWK HH.MMam

Example Route

The tables below are reproduced from the linked tables in the BEST database called Atlas, Routes, Stops, Areas.

This is a typical route from the BEST Atlas table for bus number 6LTD which will be used as an example for sample queries below. 6LTD runs from Colaba to Trombay. It has twelve different possible start and end points (Navy Nagar, Dr S.P.M. Chowk to Mahul, Anushakti Nagar, Trombay, etc.), 58 total stops, and varying frequencies for time of day and day of the week (Mon-Sat or Sun-Holidays).

ROUTE ROUTCODE FROM FIRST_FROM LAST_FROM TO FIRST_TO LAST_TO ROUTDIST RUN_TIME1 RUN_TIME2 RUN_TIME3 RUN_TIME4 FREQUENCY1 FREQUENCY2 FREQUENCY3 FREQUENCY4 SCHEDULE
6LTD 0061 NAVY NAGAR 7.55 11.15 B.A.R.C. 6.22 9.32 28.3 100 19 23 18 Mon-Sat
6LTD 0061 NAVY NAGAR MAHUL VILLAGE 8.10 9.05 28.3 Mon-Sat
6LTD 0061 ELECTRIC HOUSE REFINERIES 5.30 5.50 23.4 Mon-Sat
6LTD 0061 ELECTRIC HOUSE 6.45 7.23 B.A.R.C. 9.58 20.00 25.2 85 89 94 Mon-Sat
6LTD 0061 ELECTRIC HOUSE 11.32 19.31 B.A.R.C. Mon-Sat
6LTD 0061 ELECTRIC HOUSE 19.49 21.40 REFINERIES 23.4 Mon-Sat
6LTD 0061 ELECTRIC HOUSE 7.40 19.30 B.A.R.C. 6.20 20.05 25.5 80 85 24 25 Sun-Hol
6LTD 0061 ELECTRIC HOUSE 19.55 21.30 REFINERIES Sun-Hol
6LTD 0061 NAVY NAGAR 6.45 20.00 TROMBAY 5.30 18.16 29.2 105 110 120 22 23 Mon-Sat
6LTD 0061 DR.S.P.M.CHOWK 20.50 21.17 TROMBAY 18.38 20.10 25.7 Mon-Sat
6LTD 0061 DR.S.P.M.CHOWK 21.40 ANUSHAKTI NAGAR 22.6 Mon-Sat
6LTD 0061 NAVY NAGAR 7.00 19.00 TROMBAY 5.30 17.27 29.2 84 90 19 15 Sun-Hol
6LTD 0061 DR.S.P.M.CHOWK 19.55 21.45 DEONAR DEPOT 21.4 Sun-Hol
6LTD 0061 DR.S.P.M.CHOWK 19.05 19.39 TROMBAY 17.43 20.25 25.7 Sun-Hol
Clone this wiki locally