invisiblefunnel/stop-times-viz.md

## stop-times-viz.md

      
    Raw
  

              stop-times-viz.md
            
          
    1. Load data


Setup postgres or your database or choice.
Download the onebusaway gtfs loader.
Find the appropriate database jar (postgresql, 9.4-1201 JDBC 41 is what I used)

java -classpath path/to/onebusaway-gtfs-hibernate-cli-1.3.4.jar:path/to/postgresql-9.4-1201.jdbc41.jar \
  org.onebusaway.gtfs.GtfsDatabaseLoaderMain \
  --driverClass=org.postgresql.Driver \
  --url=jdbc:postgresql:sfmta_gtfs \
  --username=... \ # optional
  --password=... \ # optional
  path/to/sfmta_gtfs.zip
2. Dump stop times and locations to a CSV

psql -d sfmta_gtfs -a -f stop-times.sql
-- stop-times.sql
-- Don't forget to add headers to the resulting CSV before uploading to CartoDB
COPY (
  SELECT
    -- agency,
    -- shortname,
    -- longname,
    lon,
    lat,
    (CASE
    -- before midnight
    WHEN arrivaltime < 86400 THEN ('2015-06-09 ' || TO_CHAR((arrivaltime || ' second')::interval, 'HH24:MI:SS')::text)::timestamp
    -- after midnight, subtracting 24 hours creates the looping effect
    WHEN arrivaltime >= 86400 THEN ('2015-06-09 ' || TO_CHAR(((arrivaltime - 86400) || ' second')::interval, 'HH24:MI:SS')::text)::timestamp
    END) AS arrivaltime
  FROM gtfs_stop_times
  INNER JOIN gtfs_stops
  ON gtfs_stops.id = gtfs_stop_times.stop_id
  INNER JOIN gtfs_trips
  ON gtfs_trips.id = gtfs_stop_times.trip_id
  INNER JOIN gtfs_routes
  ON gtfs_routes.id = gtfs_trips.route_id
  WHERE serviceid_id = '1' -- find the weekday service_id
) TO '/Users/drw/Desktop/sfmta-weeday-service-stop-times.csv' WITH CSV;
3. Upload to CartoDB


Pay CartoDB money if your CSV is larger than 50mb
Create a new dataset with the CSV
Select the Torque map setting from the Wizard menu in the sidebar of map view.
Set Time Column to arrivaltime
Tweak the settings to taste.