NOTES

select routes.route_id, max(routes.route_short_name) as route_short_name, count(route_id) from routes join trips using (route_id) group by routes.route_
id order by max(routes.route_short_name);



mbta=# select * from trips limit 10;
 route_id  |         service_id         | trip_id  | trip_headsign | direction_id | block_id | shape_id
-----------+----------------------------+----------+---------------+--------------+----------+----------
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942700 | Bowdoin       |            0 | B946_-6  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942701 | Wonderland    |            1 | B946_-6  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942702 | Wonderland    |            1 | B946_-18 |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942703 | Bowdoin       |            0 | B946_-7  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942704 | Wonderland    |            1 | B946_-7  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942705 | Bowdoin       |            0 | B946_-6  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942706 | Bowdoin       |            0 | B946_-6  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942707 | Wonderland    |            1 | B946_-6  |
 948_-1079 | RTL111-hmb11011-Weekday-01 | 13942708 | Bowdoin       |            0 | B9462-3  |
 946_-1079 | RTL111-hmb11011-Weekday-01 | 13942709 | Wonderland    |            1 | B9462-3  |


mbta=# select * from stops limit 10;
   stop_id   | stop_code |     stop_name     | stop_desc |  stop_lat   |   stop_lon   | zone_id | stop_url | location_type | parent_station
-------------+-----------+-------------------+-----------+-------------+--------------+---------+----------+---------------+----------------
 place-alfcl |           | Alewife Station   |           |   42.395428 |   -71.142483 |         |          |             1 |
 place-andrw |           | Andrew Station    |           |   42.330154 |   -71.057655 |         |          |             1 |
 place-aport |           | Airport Station   |           |   42.374262 |   -71.030395 |         |          |             1 |
 place-aqucl |           | Aquarium Station  |           |   42.359784 |   -71.051652 |         |          |             1 |
 place-armnl |           | Arlington Station |           |   42.351902 |   -71.070893 |         |          |             1 |
 place-asmnl |           | Ashmont Station   |           |   42.284652 |   -71.064489 |         |          |             1 |
 place-bbsta |           | Back Bay Station  |           |    42.34735 |   -71.075727 |         |          |             1 |
 place-bmmnl |           | Beachmont Station |           | 42.39754234 | -70.99231944 |         |          |             1 |
 place-bomnl |           | Bowdoin Station   |           |   42.361365 |   -71.062037 |         |          |             1 |
 place-brdwy |           | Broadway Station  |           |   42.342622 |   -71.056967 |         |          |             1 |


mbta=# select * from stop_times limit 10;
 trip_id  | arrival_time | departure_time | stop_id | stop_sequence | stop_headsign | pickup_type | drop_off_type
----------+--------------+----------------+---------+---------------+---------------+-------------+---------------
 14021448 | 06:40:00     | 06:40:00       | 1       |             2 |               |           0 |             0
 14021524 | 06:15:00     | 06:15:00       | 1       |             2 |               |           0 |             0
 14045401 | 06:00:00     | 06:00:00       | 1       |             2 |               |           0 |             0
 14045403 | 07:05:00     | 07:05:00       | 1       |             2 |               |           0 |             0
 14045405 | 08:05:00     | 08:05:00       | 1       |             2 |               |           0 |             0
 14045407 | 09:05:00     | 09:05:00       | 1       |             2 |               |           0 |             0
 14045408 | 10:25:00     | 10:25:00       | 1       |             2 |               |           0 |             0
 14045409 | 19:00:00     | 19:00:00       | 1       |             2 |               |           0 |             0
 14045410 | 19:18:00     | 19:18:00       | 1       |             2 |               |           0 |             0
 14045411 | 20:30:00     | 20:30:00       | 1       |             2 |               |           0 |             0


Problem with arrival times in Postgres


mbta=# select max(arrival_time) from stop_times;
max
---------
9:52:00
(1 row)

May need to switch over to MySQL and use Postgres just for the
geographical part for now?

Try loading the CSV data into MySQL


Mysql load data infile 
https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bug/244406


sed 's/"""/"/g' stops.txt > stops.fixed.txt


The CSV import is messed up for MySQL.

Trying Postgresql furhter


grep  '"[[:digit:]]\{1\}:' stop_times.txt > bad.stoptimes.txt
sed 's/\<[[:digit:]]\{1\}:/0&/g'


------------------------------------------------------------------------
Thu May 26 10:58:53 EDT 2011

Reorganized the calendar table. Now we can do this sort of query:

mbta=# select * from calendar where service_days[(select 2)] = true;
mbta=# select * from calendar where service_days[(select 7)] = true;
mbta=# select * from calendar where service_days[(select 6)] = true;

Much happier with that.

select * from calendar where service_days[(select to_char(now(), 'ID'))::int - 1] = true;


OK I now have a much more useful calendar table.


         service_id          |  service_days   | start_date |  end_date
-----------------------------+-----------------+------------+------------
 BUSN11-hbf11011-Weekday-02  | {t,t,t,t,t,f,f} | 2011-02-28 | 2011-03-18
 BUSN11-hbg11011-Weekday-03  | {t,t,t,t,t,f,f} | 2011-02-28 | 2011-03-18
 BUSN11-hbg11016-Saturday-03 | {f,f,f,f,f,t,f} | 2011-03-05 | 2011-03-12
 BUSN11-hbg11017-Sunday-03   | {f,f,f,f,f,f,t} | 2011-03-06 | 2011-03-13
 BUSN11-hbl11011-Weekday-02  | {t,t,t,t,t,f,f} | 2011-02-28 | 2011-03-18
 BUSN11-hbl11016-Saturday-02 | {f,f,f,f,f,t,f} | 2011-03-05 | 2011-03-12
 BUSN11-hbl11017-Sunday-02   | {f,f,f,f,f,f,t} | 2011-03-06 | 2011-03-13


select * from calendar where service_days[(select to_char(now(), 'ID'))::int - 1] = true and start_date <= now() and end_date >= now();

This works great

select * from calendar where service_days[(select to_char(now(), 'ID'))::int - 1] = true and start_date <= now() and end_date >= now();
         service_id         |  service_days   | start_date |  end_date
----------------------------+-----------------+------------+------------
 BUSN21-hbf21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSN21-hbg21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSN21-hbl21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSN21-hbt21021-Weekday-02 | {t,t,t,t,t,f,f} | 2011-05-02 | 2011-06-24
 BUSN21-htt21021-Weekday-02 | {t,t,t,t,t,f,f} | 2011-05-02 | 2011-06-24
 BUSS21-hba21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSS21-hbb21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSS21-hbc21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSS21-hbq21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 BUSS21-hbs21011-Weekday-02 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 LRV211-hlb21021-Weekday-01 | {t,t,t,t,t,f,f} | 2011-05-02 | 2011-06-24
 LRV211-hlm21021-Weekday-01 | {t,t,t,t,t,f,f} | 2011-05-02 | 2011-06-24
 RTL211-hmb21011-Weekday-01 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 RTL211-hmo21011-Weekday-01 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 RTL211-hms21011-Weekday-01 | {t,t,t,t,t,f,f} | 2011-03-21 | 2011-06-24
 CR-Weekday                 | {t,t,t,t,t,f,f} | 2011-03-19 | 2011-06-24
 Boat-FallWeekday           | {t,t,t,t,t,f,f} | 2011-03-19 | 2011-06-24
 Logan-Weekday              | {t,t,t,t,t,f,f} | 2011-03-19 | 2011-06-24
(18 rows)


create view current_services as select * from calendar where service_days[(select to_char(now(), 'ID'))::int - 1] = true and start_date <= now() and end_date >= now();

fix calendar_date table to use date type


select service_id, service_type from (
  select service_id, 'normal' as service_type from calendar 
  where service_days[(select to_char(now(), 'ID'))::int - 1] = true and start_date <= now() and end_date >= now() 
  UNION
  select service_id, 'added' as service_type from calendar_dates 
  where exception_type = 'add' and date = now()
) services
EXCEPT 
  select service_id, 'removed' as service_type from calendar_dates 
  where exception_type = 'removed' and date = now();


CREATE FUNCTION active_services(date) RETURNS setof varchar AS $$
select service_id from (
  select service_id from calendar 
  where service_days[(select to_char($1, 'ID'))::int] = true and start_date <= $1 and end_date >= $1
  UNION
  select service_id from calendar_dates 
  where exception_type = 'add' and date = $1
) services
EXCEPT 
  select service_id from calendar_dates 
  where exception_type = 'remove' and date = $1;
$$ language sql;

select active_services(date('20110511'));
select active_services(date('20110530')) as service_id order by service_id;

------------------------------------------------------------------------

This is more explicit:


CREATE FUNCTION active_services2(date) RETURNS setof record AS $$
  select service_id, 'normal' as service_type from calendar 
  where service_days[(select to_char($1, 'ID'))::int] = true and start_date <= $1 and end_date >= $1
UNION
  select service_id, 'added' as service_type from calendar_dates 
  where exception_type = 'add' and date = $1
UNION 
  select service_id, 'remove' as service_type from calendar_dates 
  where exception_type = 'remove' and date = $1;
$$ language sql;


          active_services2
-------------------------------------
 (BUSN21-hbt21021-Weekday-02,normal)
 (RTL211-hmb21me7-Sunday-01,added)
 (Logan-Weekday,normal)
 (BUSN21-htt21021-Weekday-02,normal)
 (BUSS21-hba21011-Weekday-02,normal)
 (BUSN21-hbg21011-Weekday-02,normal)
 (LRV211-hlm21021-Weekday-01,remove)
 (BUSS21-hbc21011-Weekday-02,normal)
 (RTL211-hms21me7-Sunday-01,added)
 (BUSN21-hbl21011-Weekday-02,remove)
 (LRV211-hlb21021-Weekday-01,normal)
 (Boat-FallWeekday,normal)


select active_services2(date('20110530'));

select * from active_services2(date('20110530')) f(service_id text, service_type text);

Got it:

These two should be equivalent:

select active_services(date('20110530')) as service_id order by service_id;

select service_id, count(service_id) from active_services2(date('20110530')) f(service_id text, service_type text) group by service_id having count(service_id) < 2 order by service_id;

Use the first version, the second to check.


mbta=# select count(*) from trips where service_id in (select service_id current_services);
count
--------
121866
(1 row)

select count(*) from trips where service_id in ( select active_services(date('20110530')) as service_id order by service_id);



Now create a function that produces the trips records for a certain date.

CREATE FUNCTION active_trips(date) RETURNS SETOF trips AS $$
select * from trips where service_id in (select active_services($1) as service_id);
$$ LANGUAGE SQL;

------------------------------------------------------------------------
# FIND TRIPS REMAINING

# We should denormalize! This is too slow.

select trip_id, max(stop_times.arrival_time) from trips 
inner join stop_times using(trip_id)
where service_id in (select active_services(date(now())) as service_id)
group by trips.trip_id
having max(stop_times.arrival_time) > '12:00:00'
;
------------------------------------------------------------------------


This works:

select * from active_trips(date(now()));

Next we need a function that shows the active trips for a mode of transport


use nullif and coalesce:

select r.route_type, r.route_id, coalesce(nullif(r.route_long_name, ''), nullif(r.route_short_name, '')) route, 
trips.trip_id, trips.trip_headsign, trips.direction_id 
from active_trips(date(now())) as trips 
inner join routes r using (route_id) order by r.route_type, route, trips.direction_id;


get routes for the day, and the number of trips per route

GET ROUTES

select r.route_type, coalesce(nullif(r.route_long_name, ''),
nullif(r.route_short_name, '')) route, 
trips.direction_id, count(*) as trips_count
from active_trips(date(now())) as trips inner join routes r using (route_id) 
group by r.route_type, route, trips.direction_id
order by r.route_type, route, trips.direction_id;


 route_type |                     route                     | direction_id | trips_count
------------+-----------------------------------------------+--------------+-------------
          0 | Green Line                                    |            0 |         578
          0 | Green Line                                    |            1 |         577
          0 | Mattapan High-Speed Line                      |            0 |         163
          2 | Providence/Stoughton Line                     |            0 |          36
          2 | Providence/Stoughton Line                     |            1 |          37


GET ROUTES II

query for remaining trips

select r.route_type, coalesce(nullif(r.route_long_name, ''),
nullif(r.route_short_name, '')) route, 
trips.direction_id, count(*) as trips_left
from active_trips(date(   now()  )) as trips inner join routes r using (route_id) 
where trips.finished_at >   '20:00:00' 
group by r.route_type, route, trips.direction_id 
order by r.route_type, route, trips.direction_id;


GET ROUTES II b

select a.route_type, a.route, a.direction_id, b.trips_left from 
(select r.route_type, coalesce(nullif(r.route_long_name, ''), nullif(r.route_short_name, '')) route, trips.direction_id
from active_trips(date(   now()  )) as trips inner join routes r using (route_id)
group by r.route_type, route, trips.direction_id) a
left outer join
(select r.route_type, coalesce(nullif(r.route_long_name, ''), nullif(r.route_short_name, '')) route, 
trips.direction_id,
count(*) as trips_left
from active_trips(date(   now()  )) as trips inner join routes r using (route_id) 
where trips.finished_at >   '20:00:00' 
group by r.route_type, route, trips.direction_id) b
on (a.route_type = b.route_type and a.route = b.route and a.direction_id = b.direction_id)
order by route_type, route, direction_id;


GET ROUTES III

DROP FUNCTION available_routes(timestamp with time zone);

CREATE FUNCTION available_routes(timestamp with time zone) RETURNS setof record AS $$
select a.route_type, a.route, a.direction_id, 
coalesce(b.trips_left, 0), b.headsign from 
(select r.route_type, coalesce(nullif(r.route_long_name, ''), nullif(r.route_short_name, '')) route, trips.direction_id
from active_trips(adjusted_date($1)) as trips inner join routes r using (route_id)
group by r.route_type, route, trips.direction_id) a
left outer join
  (select r.route_type, coalesce(nullif(r.route_long_name, ''), nullif(r.route_short_name, '')) route, 
  trips.direction_id,
  count(*) as trips_left,
  array_to_string(array_agg(trip_headsign), ';') as headsign
  from active_trips(adjusted_date($1)) as trips inner join routes r using (route_id) 
  where trips.finished_at > adjusted_time($1)
  group by r.route_type, route, trips.direction_id) b
  on (a.route_type = b.route_type and a.route = b.route and a.direction_id = b.direction_id)
  order by route_type, route, direction_id;
$$ language sql;


select available_routes(now());


FOR ROUTES USE THIS CALL:

select * from available_routes(now()) as (route_type smallint, route varchar, direction_id smallint, trips_left bigint);

 route_type |                    route                    | direction_id | trips_left
------------+---------------------------------------------+--------------+------------
          0 | Green Line                                  |            0 |        121
          0 | Green Line                                  |            1 |        102


Make this into a database function?


\df+ available_routes


to_char(timestamp, "HH24:MI:SS")
p.191 postgres pdf

------------------------------------------------------------------------
TRIPS FOR A ROUTE

We can show this in the routes list

Next: the core part. Take one route name and direction, and return all
the trips for that route and direction.


Two functions:
- route_trips_today
- stop_times_today (calls the former)


CREATE FUNCTION route_trips_today(varchar, int) RETURNS SETOF trips AS $$
select trips.* 
from active_trips(date(now())) as trips 
inner join routes r using (route_id) 
where trips.direction_id = $2 and coalesce(nullif(r.route_long_name, ''), nullif(r.route_short_name, '')) = $1;
$$ LANGUAGE SQL;

mbta=# select * from route_trips_today('Red Line', 1);
mbta=# select * from route_trips_today('Providence/Stoughton Line', 1);
mbta=# select * from route_trips_today('1', 1);
mbta=# select * from stop_times where trip_id = 'CR-Providence-CR-Weekday-800';

This approach may need rethinking. To make the grid, we can do most of
that on the Ruby side: We can build a big table of all the stop_times
for a certain trip id set. Conceptually, this is easier.


HENCE:

CREATE FUNCTION stop_times_today(varchar, int) RETURNS SETOF stop_times AS $$
select * from stop_times st where trip_id in 
(select trip_id from route_trips_today($1, $2))
order by stop_id, arrival_time, stop_sequence;
$$ LANGUAGE SQL;

This should produce the raw data from which to generate the table grid
for a day:

CALL FOR TRIPS FOR A ROUTE:

- first argument is route name, then direction

select stops.stop_name, stops.stop_code, st.* from stop_times_today('1', 1) st join stops using(stop_id);

- more complete example:

select stops.stop_name, stops.stop_lat, stops.stop_lon, stops.parent_station, stops.stop_code, st.* from stop_times_today('1', 1) st join stops using(stop_id);

This will return 4032 rows for Bus, fewer for other routes;

Example record:
-[ RECORD 1 ]--+------------------------------------------------
stop_name      | Albany St @ Northampton St
stop_lat       | 42.33279
stop_lon       | -71.074516
parent_station |
stop_code      | 10099
trip_id        | 14442911
arrival_time   | 05:30:00
departure_time | 05:30:00
stop_id        | 10099
stop_sequence  | 30
stop_headsign  |
pickup_type    | 0
drop_off_type  | 0


------------------------------------------------------------------------

Need to fix available trip calculation for times from 12 am to 3am

The date also needs to be the PREVIOUS date if the time is after midnight.

The simple way is to parameterize available_routes(date, text) # where text is
the time string e.g., "24:05:00" for 5 minutes past midnight.


CREATE FUNCTION adjusted_date(x timestamp with time zone) RETURNS date AS $$
BEGIN
  IF extract(hour from x) < 4 THEN 
    RETURN date( x - interval '24 hours' );    
  ELSE 
    RETURN date(x);
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION adjusted_time(x timestamp with time zone) RETURNS character(8) AS $$
DECLARE
  h integer;
  m integer;
  s  integer;
BEGIN
  h := extract(hour from x);
  m := extract(minutes from x);
  IF h  < 4 THEN 
    h := h + 24;
  END IF;
  RETURN lpad(h::text, 2, '0') || ':' || m || ':00';
END;
$$ LANGUAGE plpgsql;




mbta=# select extract(hour from now());
 date_part
-----------
         0
(1 row)

mbta=# select extract(minutes from now());
 date_part
-----------
        33


mbta=# select extract(hour from now()) || ':' || extract(minutes from now()) || ':00';
 ?column?
----------
 0:36:00
(1 row)


May need to assign variables before function.



------------------------------------------------------------------------
Tue May 31 11:50:36 EDT 2011


The v3 client assumes stop_id is an integer because it calls stringValue on it.

Need to assign integer IDs to all the stops with string ids?

ALTER table stops add column stop_integer_id serial;


------------------------------------------------------------------------

REALTIME

route config has <direction tag="1_01203213_0" title="Harvard Station via Mass Ave." etc.

This looks like all that is necessary

_0 means outbound

The predictions file has 
<predictions routeTitle="1" routeTag="1" stopTitle="Dudley station" stopTag="64">
  <direction title="[headsign]">
   <prediction -> has a dirTag="1_100117v0_0" 

  I think I can infer everything from the prediction thing


Use this query to match nextbus routeTags with routes from GTFS

select route_id, route_short_name, tag nextbus_tag from routes left outer join nextbus_routes on (trim(leading '0' from  split_part(routes.route_id, '-', 1)) = nextbus_routes.
tag) where routes.route_type = 3;

select nextbus_predictions.*, stop_name from nextbus_predictions inner join stops on stops.stop_id = nextbus_predictions.stoptag ;

CREATE FUNCTION 


TODO: created_at for predictions

DONE: realtime bus

next: realtime subway

select platform_name, keys.stop_id, direction, platform_order, rt_subway_predictions.* from rt_subway_predictions inner join rt_subway_keys keys using(platform_key);


      platform_name      |   stop_id    | direction | platform_order |  line  | trip_id | platform_key | information_type |    arrival_time     | route
-------------------------+--------------+-----------+----------------+--------+---------+--------------+------------------+---------------------+-------
 BRAINTREE SB            | place-brntn  | SB        |             17 | Red    | 825     | RBRAS        | Arrived          | 2011-05-31 19:50:29 | 0
 HARVARD NB              | place-harsq  | NB        |             14 | Red    | 828     | RHARN        | Arrived          | 2011-05-31 19:53:04 | 0
 PORTER NB               | place-portr  | NB        |             15 | Red    | 828     | RPORN        | Predicted        | 2011-05-31 19:55:52 | 0
 DAVIS NB                | place-davis  | NB        |             16 | Red    | 828     | RDAVN        | Predicted        | 2011-05-31 19:57:59 | 0

To get the direction_id, we have to do a mapping.


CREATE VIEW:

create view view_subway_predictions 
as select platform_name, keys.stop_id, direction, platform_order, rt_subway_predictions.* from rt_subway_predictions inner join rt_subway_keys keys using(platform_key);


mbta=# select line, direction, count(*) from view_subway_predictions group by line, direction;
  line  | direction | count
--------+-----------+-------
 Blue   | EB        |    13   INBOUND
 Blue   | WB        |    18   OUTBOUND
 Orange | NB        |    21   INBOUND
 Orange | SB        |    31   OUTBOUND
 Red    | NB        |    88   IN 
 Red    | SB        |    74   OUT
(6 rows)


Good. Zoe3 RUBY_VERSION is 1.9.1


I think I need to switch from Sinatra to Rails 3
https://github.com/brasten/sequel-rails

We can use the Sintra app for serving routes and trips inside Rails
http://lindsaar.net/2010/2/7/rails_3_routing_with_rack



Wed Jun  1 14:19:31 EDT 2011
Deployed. waiting for DNS to update

Next bus google groups announced
http://groups.google.com/group/massdotdevelopers/browse_thread/thread/ecd7e68b908d2b3a


http://groups.google.com/group/massdotdevelopers

commuter connect API

http://groups.google.com/group/massdotdevelopers/browse_thread/thread/483254ba45a737c7
http://www.mbta.com/uploadedfiles/Rider_Tools/Developer_Page/CommuterConnectDoc_JKR%204%2015%201%2028pm.pdf

http://www.rajsingh.org/boston-area-data-feeds/


------------------------------------------------------------------------
Thu Jun  2 12:49:07 EDT 2011

TODO
- Make realtime fetch for bus live and cache for 1 minute instead of on a loop.
- restore mobile web 

alter table nextbus_predictions add column created timestamp default now();

Merge alerts with a trip map

select coalesce(nullif(routes.route_long_name, ''), nullif(routes.route_short_name, '')) from routes;

(cd /home/zoe/openmbta2 && /usr/local/bin/ruby -Ilib app/models/alert.rb)
select substring(title for 40), pubdate from t_alerts order by pubdate desc;


select substring(title for 90), pubdate from t_alerts where title ? ~ split_part(title, ' ', 1)  order by pubdate desc;

select guid,  CASE WHEN split_part(title, ' ', 1) = 'Red' THEN 'RED' ELSE split_part(title, ' ', 1) END as fixed_routetag, pubdate from t_alerts order by pubdate desc;

matching alerts
res = DB["select * from t_alerts where ? ~ split_part(title, ' ', 1) and pubdate > now() - interval '1 hour'  order by pubdate desc", route].all


fixes to mobile web version done

------------------------------------------------------------------------
Fri Jun  3 00:28:42 EDT 2011

Enhancements

- Horizontal orientation for better viewing schedules
- right index should abbreviate long route names and not be fat
- save start and end point stops, and show much more concise data. collect analytics based on stops.


Find all trips connecting two stops.

select array_agg(stop_id) from stop_times group by trip_id limit 10 where trip_id = '13942700';

For stop bookmarking
- tap a stop on map or grid, show a page where you can bookmark the stop
- also, here you can specify a destination stop and then get all the times for any point in time
  - work on the SQL for this.
  - the date picker should just pick a date, then resuse the grid controller to
    show all the scheduled time between the two stops for that day




Try to shorten the route coalescing

CREATE FUNCTION coalesce2(varchar, varchar) RETURNS varchar AS $$
select coalesce(nullif($1, ''), nullif($2, '')); 
$$ LANGUAGE SQL;


SQL to get all trips for route and two stops

-- pass in route, stop_ids. First stop is assumed to be earlier.
-- route is like '1' 
CREATE OR REPLACE FUNCTION trips_for_stops(varchar, varchar, varchar) 
RETURNS setof record AS $$
  select trips.trip_id, array_agg(st.stop_id), array_agg(st.arrival_time), 
  max(route_id) ,
  max(service_id)
  from trips inner join routes r using(route_id)
    inner join stop_times st using(trip_id)
    where coalesce2(r.route_long_name, r.route_short_name) = $1
      and st.stop_id in ($2, $3) 
    group by trip_id 
    having count(trip_id) > 1;
$$ LANGUAGE SQL;


select trips_for_stops('1', '2168', '73');


CREATE OR REPLACE FUNCTION trips_for_stops_without_route(varchar, varchar) 
RETURNS setof record AS $$
  select trips.trip_id, max(st1.arrival_time), max(st2.arrival_time),
  max(route_id) ,
  max(service_id)
    from trips
    inner join stop_times st1 on trips.trip_id = st1.trip_id
    inner join stop_times st2 on trips.trip_id = st2.trip_id
    where st1.stop_id = $1
      and st2.stop_id = $2
      and st1.stop_sequence < st2.stop_sequence
    group by trips.trip_id ;
$$ LANGUAGE SQL;

select trips_for_stops_without_route('2168', '73');


Strategy: store the trips_for_stops for 2 stops in SQLITE3 on client. Then
calculate and show the schedules for selected any on the client side. Use the
grid to show. No need for map?

So the work on the iPhone side for this is basically done. The client will do the 
heavy lifting and calculations using Sqlite.

Allow the user to flush the SQLITE cache.

The Sqlite database can also store route bookmarks?

So today, study the Sqlite3 iPhone APIs.

CoreData vs sqlite
http://cocoawithlove.com/2010/02/differences-between-core-data-and.html

send over the data as CSV
Plus the calendar table and calendar_dates

mbta=# select routetag, stoptag, dirtag, arrival_time, triptag, vehicle, block, trips.trip_headsign from nextbus_predictions inner join trips on trips.trip_id = triptag limit 20000;

Trip planner links
http://opentripplanner.org/wiki/kick-off-workshop



select shape_id, ST_AsText(geom) as geom FROM polylines;
select shape_id, ST_GeomFromtText(geom) as geom FROM polylines;


just do this to get the trip coordinates. it's simpler than using postgis functions.

select * from shapes where shape_id = '1000002' order by shape_pt_sequence asc; 



------------------------------------------------------------------------
Fri Jun 10 12:26:34 EDT 2011

Linestrings

select ST_AsEWKT(geog::geometry) from polylines where shape_id = '660002';
select ST_Text(geog::geometry) from polylines where shape_id = '660002';

=>
 LINESTRING(-71.1191941132 42.3753097136,-71.119142226
 42.375137763,-71.1188956911 42.3745329763,-71.1188231985
 42.3743414743,-71.1188411146 42.374254821,-71.1188944444
 42.3740642846,-71.1189316142 42.3739760486,-71.1189790136
 42.3739097991,-71.119098987 42.3737142696,-71.1192676853
 42.3735394819,-71.119484753 42.373444432,-71.1197797679
 42.3733724152,-71.1199079342 42.3733525326,-71.1203880045 42.3732

Ruby can parse this and pass it on to the iOS client.

------------------------------------------------------------------------
Thu Jun 16 12:23:32 EDT 2011


create view view_cr_pred as select route, trip, destination, stop, scheduled, flag, timestamp, lateness from rt_cr_predictions where flag != 'sch';


------------------------------------------------------------------------
Sat Jun 25 01:49:15 EDT 2011


HOW TO LOAD A NEW SET OF DATA:

Turn off Apache; turn off Cronjobs that access database; then run

  db/recreate.sh

Then turn on apache and cron jobs

I should come up with something less manual!

What about creating the new database, then switching the app over to the new database?



<20110718074959.25145.23381@celery-2.disqus.net> 7k  
- text/plain; charset=utf-8 
- text/html; charset=utf-8 
----------------------------------------------
from: Disqus <notifications-XK2EK48RXE@disqus.net>
date: Mon, Jul 18 07:49 AM +00:00 2011
to: dhchoi@gmail.com
subject: [openmbta] Re: OpenMBTA iPhone App

======

Ryanpeterson87 <ryanpeterson87@yahoo.com> (unregistered) wrote:

As this app is very useful fromy iPhone i don't have that many issues with it, however the one issue that I do have is a large one. Not being a native bostonian I sometimes find it difficult to determine which subway line or bus route will take me to say bunker hill from Brookline. It is a little easier when using the subway lines because there aren't as many to choose from. I believe that it would be of great help myself and others like me to be able to click on stops on a map without having to choose method previously or have a separate category that breaks down routes by specific area i.e. All transportation methods in Brookline or Charlestown etc.

IP address: 24.60.255.186
Link to comment: http://disq.us/2nd5cl

-----
Options: You can moderate through email. Respond in the body with "Delete". Respond in the body to post a reply comment.

Or use the moderate panel: http://openmbta.disqus.com/admin/moderate/
To turn off notifications, go to: http://disqus.com/account/



------------------------------------------------------------------------
HTML map


We can join the two tables; realtime bus plus trip ids

mbta=# select triptag, trip_id from view_bus_predictions inner join trips on (trip_id = triptag);

mbta=# select stop_name, trip_headsign, arrival_time, triptag, trip_id from view_bus_predictions inner join trips on (trip_id = triptag);

12800 realtime bus predictions result from this. Not historical information

  Can calculate whether bus is ahead of or behind schedule.


create view bus_realtime_compact as 
SELECT trips.route_id, view_bus_predictions.stop_name,
 trips.trip_headsign, view_bus_predictions.arrival_time,
 view_bus_predictions.triptag, trips.trip_id
    FROM view_bus_predictions
       JOIN trips ON trips.trip_id::text =
       view_bus_predictions.triptag::text
         ORDER BY trips.route_id;


Try to plot a line using shapes;

Trips has a shape_id

route_id 01 (Bus 1)

one trip_id is 15868005

> select * from shapes where shape_id = (select shape_id from trips where trip_id = '15868005') order by shape_pt_sequence asc;

Sample output:

 shape_id | shape_pt_lat  |  shape_pt_lon  | shape_pt_sequence | shape_dist_traveled
----------+---------------+----------------+-------------------+---------------------
 010017   |  42.329848413 | -71.0838758549 |             10001 |
 010017   | 42.3297880369 | -71.0832682623 |             10002 |
 010017   | 42.3300890857 | -71.0831982323 |             10003 |
 010017   | 42.3302964049 | -71.0831609938 |             10004 |
 010017   | 42.3304927064 | -71.0831123629 |             10005 |
 010017   | 42.3306434636 |  -71.083065881 |             10006 |
 010017   | 42.3308174283 | -71.0829870708 |             10007 |
 010017   | 42.3309835788 | -71.0827966229 |             10008 |
 010017   | 42.3309835788 | -71.0827966229 |             20001 |
 010017   | 42.3310546312 | -71.0827155314 |             20002 |
 010017   | 42.3313706037 | -71.0823724624 |             20003 |
 010017   | 42.3317515377 | -71.0819339134 |             20004 |
 010017   | 42.3319398162 | -71.0817166855 |             20005 |

What if we just have three trips per headsign, showing realtime.

Or write a function that will return a JSON array of lat, lng sufficient to draw a polyline
for a trip on a google map;

Also another function to determine whether a trip has the same shape <- this is valuable.


------------------------------------------------------------------------
postgis installation

sudo apt-get install postgresql-8.4-postgis
psql -d mbta -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d mbta -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d mbta -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

then load db/linestrings.sql

------------------------------------------------------------------------
postgis installation guide:
  http://blog.smartlogicsolutions.com/2010/03/04/installing-postgis-1-5-0-on-postgresql-8-4-on-ubuntu-9-10/

------------------------------------------------------------------------

example queries

-- select shape_id, ST_NumPoints(geom)  from polylines;

-- select shape_id, ST_Length(geom) from polylines;
-- select shape_id, ST_Summary(geom) from polylines;
-- select ST_AsGeoJSON(ST_Transform(geom, 4263)) from polylines limit 1;

select ST_AsGeoJSON(geog) from polylines where shape_id = (select shape_id from trips where trip_id = '15868005');



------------------------------------------------------------------------
Tue Oct 18 21:52:12 EDT 2011

THis query is good and reasonably fast:

select * from available_routes(now()) as (route_type smallint, route
varchar, direction_id smallint, trips_left bigint, headsign varchar);

create view select * from available_routes(now()) as (route_type smallint, route
varchar, direction_id smallint, trips_left bigint, headsign varchar);

ruby -Ilib lib/available_routes.rb

See doc/view_available_routes.txt for output.




Great idea for animation
http://dl.dropbox.com/u/10755342/eq/earthquakes.html
http://groups.google.com/group/d3-js/browse_thread/thread/c9aad0aa4b8dd813


http://bl.ocks.org/1263239

This is a simple slippy map that keeps the absolute coordinates of all 
tiles/location static, but moves the SVG viewbox as the user pans and 
zooms around. We can then draw SVG objects directly on the coordinate 
system and do not have to update location when the viewBox is changed. 
There are two components to this, both standalone. 

1) svg_interact: provides pan and zoom to any svg-object by moving the 
viewBox 

2) svg_map: a tiler that watches the viewbox location and size at 
regular intervals and fetches the appropriate cloudmake tiles to cover 
the viewbox with some extra padding. This tiler uses the .data() 
method to enter and exit tiles. Another alternative might be to keep 
an array and push old items out as new come in. (We need to discard 
old tiles as number of open images affects performance. The graphic 
contents are however automatically cached by the browser) 

I would really appreciate any feedback, particularly helpful hints on 
making this better and more d3 compatible (i.e. using pv.behavior to 
get better mouse functions).


Slider for time, and d3 animates motion of trains.




Start naming postgres function with a prefix mbta_ to distinguish from
postgis.

Concentrate on one route view.

Get all data for trips for the day or hour.

Write postgres function that returns a dictionary of stops, a list of
trips, and a dictionary of linestrings or JSON.


mbta=# select * from route_trips_today('1', 0);

mbta=# select * from route_trips_today('1', 0) order by finished_at limit 2;

 route_id |         service_id         | trip_id  |         trip_headsign          | direction_id | block_id | shape_id | finished_at
----------+----------------------------+----------+--------------------------------+--------------+----------+----------+-------------
 01       | BUSS41-hbc41011-Weekday-02 | 15869056 | Harvard Station via Mass. Ave. |            0 | C01-1    | 010017   | 04:57:00
 01       | BUSS41-hbc41011-Weekday-02 | 15869078 | Harvard Station via Mass. Ave. |            0 | C01-5    | 010017   | 05:17:00
(2 rows)


select ST_AsGeoJSON(geog) as json from polylines where shape_id = '010017';

use lib/polyline.rb to help generate data input to mustache.

pass view 

1. the list of trips from route_trips_today()
2. get a dictionary of json polylines by shape_id
3. get dictionary of stops per trip?


For any given time, we can find out the trips active (started but not
finished) at a certain time. Write a postgres function that added the
started_at column and maybe another function that filters down to those
active at time X.

Write a Ruby program that generates this json. Then feed it into
mustache.


Too much data to show all the stop times for the day, filter down to
trips that finish between time x and time x + interval.




  640  rlib lib/tripview2.rb '1' 0 > mustache/trip.yml 
  636  cat mustache/trip.yml mustache/trip.mustache | mustache > mustache/trip.html && open mustache/trip.html

Start a sinatra app





bus 350 not working 

  For agency=mbta stop s=16901 is on none of the directions for r=350 so cannot determine which stop to provide data for.



      res = DB["select stoptag from nextbus_route_configs where routetag = ?", route_tag].map {|s| s[:stoptag]}