What are "relevant traffic statistics"

[joshproehl]

We need to decide on and document exactly what statistical information is going to be shown, so that we can begin creating the stories necessary to build this functionality.

This issue blocks #10

Currently we have the following high level overview:

• Statistics going from beginning-station-id to end-station-id
• based on time of day. Allow user to select their desired travel time.
• Show information about traveling near their desired time. (i.e. 15 minute intervals on either side for x intervals. Is there a better time?

Open questions:

• How large should the intervals be?
• What data are the statistics based on?
  • Daily data in this time block? (For how many days?)
  • Daily data for this day of the week? (For how many weeks?)
• Other data we want to display? (Peak traffic time on this segment? Worst day of the week to travel on?)

[pgicking]

• How large should the intervals be? 15 minute intervals, but the loopback data we use should be the 5 minute versions.
• What data are the statistics based on? The 5 minute loopback data tables.
• Daily data in this time block? (For how many days?) Data should be at the time the user selects and then 15 minute intervals an hour ahead and an hour behind. This can be adjusted as we see how long it takes it to do this.
• Daily data for this day of the week? (For how many weeks?) Kristin suggested we take 6 weeks worth of a day in both directions to give us the best amount of data points while avoiding any seasonal quirks that might effect the prediction. I.e. we dont want winter traffic effect our summer predictions.

Additionally she suggested that we dont do every day of the week but instead use "buckets". This might complicate our calculations more but it will give us more data points to average so we're less screwed by outliers. Her suggested buckets

Monday | tues/wed/thurs | Friday | Sat/Sun

Her reasoning behind that the grouped days are usually very similar in their traffic patterns.

• Other data we want to display? (Peak traffic time on this segment? Worst day of the week to travel on?) Considering our progress, these would have to be stretch goals.

[pgicking]

Here is her starter query for calculating current traffic that we can work off of

--- Query to get average speed for October 2014 for roadway segments
--- in 1-minute bins 
--- all days, all times

--- Use corridorid 10003 - corridor created for this usage 
--- You can create your own corridors, I can update db or
--- you can make tables that mirror corridorstationlengths and
--- corridors

create temp table tt_dec_2014_jan_2015_1min (
        year integer, month integer, day integer, hour integer, minute integer, 
        starttime timestamp with time zone,
        avgspeed double precision, 
        traveltime double precision, 
        countreadings integer);

--- query for all 1 min speeds and tts aggregated over all stations 
insert into tt_dec_2014_jan_2015_1min(

SELECT year, month, day, hour, minute, st as starttime, 
       round2(avg(speed)) as avgspeed, 
       sum(traveltime) as traveltime, 
       sum(cr) as countreadings
FROM ( 
      --- convert speed to traveltime
      SELECT D.stationid,
             extract('year' from starttime) as year, 
             extract('month' from starttime) as month,
             extract('day' from starttime) as day,
             extract('hour' from starttime) as hour,
             extract('minute' from starttime) as minute,
             date_trunc('minute', starttime) as st,
             round2((C.corridorlength/(avg(speed)))*60) as traveltime,
             avg(speed) as speed,
             count(*) as cr 
      FROM ( 

         --- I was using just freeway.data, but this union
         --- will grab both types of data
         --- I'm starting with 20-sec data, but you can use 5min data

         --- combine DAC and ATMS data 
         --- SELECT detectorid, starttime, speed, volume FROM loopdata_2014_12 
         --- UNION ALL
         SELECT detectorid, starttime, speed, volume 
         FROM freeway.data 
         WHERE starttime BETWEEN 
         '2014-12-01 00:00'::timestamptz AND '2015-01-08 00:00'::timestamptz 
          ) as L, --- subquery
          detectors D, stations S, corridorstationlengths C
      WHERE 

      L.detectorid = D.detectorid 
      AND D.stationid = S.stationid
      AND S.stationid = C.stationid

      AND C.corridorid = 10003

      AND speed > 0

      GROUP BY D.stationid, year, month, day, hour, minute, st, C.corridorlength --- grouping by segment
     ) as Q
GROUP BY year, month, hour, day, minute, st  
ORDER BY year, month, hour, minute 
);

[joshproehl]

Traffic stats query merged, closing.