mondaymaps

/** torque_cat visualization */

Map {
-torque-frame-count:938;
-torque-animation-duration:30;
-torque-time-attribute:"time_stamp";
-torque-aggregation-function:"CDB_Math_Mode(torque_category)";
-torque-resolution:2;
-torque-data-aggregation:linear;
}

mondaymaps

cartodb_id	the_geom	direction_id	minutes	departure_time	route_id	service_id	stop_sequence	the_geom_webmercator	trip_headsign	trip_id
1	-122.0880, 37.6704	0	421	2017-06-12T14:01:00Z	5	WKDY	16	-13590770.0000, 4532968.3122	Fremont	01DC10
2	-122.0861, 37.6682	0	421.25	2017-06-12T14:01:15Z	5	WKDY	16	-13590559.0000, 4532655.4049	Fremont	01DC10
3	-122.0842, 37.6659	0	421.5	2017-06-12T14:01:30Z	5	WKDY	16	-13590347.0000, 4532342.5071	Fremont	01DC10
4	-122.0823, 37.6637	0	421.75	2017-06-12T14:01:45Z	5	WKDY	16	-13590135.0000, 4532029.6186	Fremont	01DC10

mondaymaps

WITH a AS (
SELECT y.trip_id, y.departure_time, y.stop_sequence, y.stop_id, 
  ST_SetSRID(ST_MakePoint(z.stop_lon, z.stop_lat),4326) AS the_geom  
FROM   bart_stop_times y
LEFT JOIN  bart_stops z
ON y.stop_id = z.stop_id
WHERE y.departure_time > '07:00:00' 
  AND y.departure_time < '09:00:00'
),
b AS (

mondaymaps

l AS (
SELECT *, date '2017-06-12' + time '07:00' + minutes * interval '1 minute' AS departure_time
FROM k
)

mondaymaps

k AS (
SELECT x.the_geom, x.minutes, x.stop_sequence, x.trip_id, 
  y.route_id, y.service_id, y.direction_id, y.trip_headsign
FROM j x
LEFT JOIN bart_trips y
ON x.trip_id = y.trip_id
)

mondaymaps

the_geom	minutes	stop_sequence	trip_id
Point	421	16	01DC10
Point	421.25	16	01DC10
Point	421.5	16	01DC10
Point	421.75	16	01DC10
...	...	...	...
Point	424.75	16	01DC10
Point	425	17	01DC10
Point	425.25	17	01DC10

mondaymaps

j AS (
SELECT * FROM i
WHERE path=1
)

mondaymaps

i AS (
SELECT minutes, stop_sequence, trip_id, 
  (ST_DumpPoints(the_geom)).geom AS the_geom, 
  (ST_DumpPoints(the_geom)).path[1] AS path
FROM h
)

mondaymaps

the_geom	minutes	stop_sequence	trip_id
Line	421	16	01DC10
Line	421.25	16	01DC10
Line	421.5	16	01DC10
Line	421.75	16	01DC10
...	...	...	...
Line	424.75	16	01DC10
Line	425	17	01DC10
Line	425.25	17	01DC10

mondaymaps

h AS (
SELECT trip_id, (minutes_start + n) AS minutes, stop_sequence, 
  ST_LineSubstring(the_geom, (n)/length, (n+0.25)/length) AS the_geom
FROM g 
CROSS JOIN generate_series(0,30, 0.25) AS n
WHERE n < length
)