Create DataTable Google Plots API from Temperature and Id Database
I would like to create an Annotation Chart using pool temperature data from a database. You can look at the database structure here on sqlfiddle or here on the registry , but to save the click, here's the structure I'm working with:
DROP TABLE IF EXISTS `temperatures`;
DROP TABLE IF EXISTS `pools`;
CREATE TABLE `pools` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `temperatures` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pool_id` int(10) unsigned NOT NULL,
`temperature` double(8,1) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `temperatures_pool_id_foreign` (`pool_id`),
CONSTRAINT `temperatures_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `pools` (`id`, `name`, `created_at`)
VALUES
(1,'Pool #1','2017-04-08 22:48:03'),
(2,'Pool #2','2017-04-08 22:48:03'),
(3,'Pool #3','2017-04-08 22:48:03');
INSERT INTO `temperatures` (`id`, `pool_id`, `temperature`, `created_at`)
VALUES
(31,1,100.1,'2017-04-09 02:44:56'),
(32,2,104.2,'2017-04-09 02:44:56'),
(33,3,97.0,'2017-04-09 02:44:56'),
(34,1,100.1,'2017-04-09 03:00:04'),
(35,2,98.4,'2017-04-09 03:00:04'),
(36,3,96.6,'2017-04-09 03:00:04'),
(37,1,100.1,'2017-04-09 03:37:13'),
(38,2,101.8,'2017-04-09 03:37:13'),
(39,3,96.4,'2017-04-09 03:37:13'),
(40,1,100.1,'2017-04-09 04:00:04'),
(41,2,101.8,'2017-04-09 04:00:04'),
(42,3,96.5,'2017-04-09 04:00:04'),
(43,1,100.1,'2017-04-09 05:00:04'),
(44,2,101.8,'2017-04-09 05:00:04');
Okay, essentially I created a controller that will return properly formatted JSON for use with ajax and google.visualization.DataTable (), like so:
var jsonData = $.ajax({
url: "/data/pool-temperature-timeline",
dataType: "json",
async: false
}).responseText;
data = new google.visualization.DataTable(jsonData);
chart.draw(data, options);
Of course, looking at the documentation, the annotation diagram expects everything to look like this:
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'Kepler-22b mission');
data.addColumn('string', 'Kepler title');
data.addColumn('string', 'Kepler text');
data.addColumn('number', 'Gliese 163 mission');
data.addColumn('string', 'Gliese title');
data.addColumn('string', 'Gliese text');
data.addRows([
[new Date(2314, 2, 15), 12400, undefined, undefined,
10645, undefined, undefined],
[new Date(2314, 2, 16), 24045, 'Lalibertines', 'First encounter',
12374, undefined, undefined],
[new Date(2314, 2, 17), 35022, 'Lalibertines', 'They are very tall',
15766, 'Gallantors', 'First Encounter'],
[new Date(2314, 2, 18), 12284, 'Lalibertines', 'Attack on our crew!',
34334, 'Gallantors', 'Statement of shared principles'],
[new Date(2314, 2, 19), 8476, 'Lalibertines', 'Heavy casualties',
66467, 'Gallantors', 'Mysteries revealed'],
[new Date(2314, 2, 20), 0, 'Lalibertines', 'All crew lost',
79463, 'Gallantors', 'Omniscience achieved']
]);
var chart = new google.visualization.AnnotationChart(document.getElementById('chart_div'));
Correct, so installation, now the question arises. What's the best way to organize the data so that 1.) there is always temperature data for pools 1, 2, and 3 for the same time (I'm worried the dataset might be incomplete for a given timestamp)? Should I organize it starting at the SQL level using some kind of smart query? Or am I organizing it in a controller using a bunch of foreach loops? This is the goal I am aiming for:
$dataTable->addRow(['created_at',
'temperature1', 'title1', 'text1',
'temperature2', 'title2', 'text2',
'temperature2', 'title2', 'text2',
]);
I could see that a smart query would be a good way to avoid doing the confusing logic and foreach loops in the controller. Perhaps if the data was organized in columns like:
created_at, pool_1_temperature, pool_2_temperature, pool_3_temperature
------------------------------------------------
2017-04-09 02:44:56, 100.1, 104.2, 97.0
2017-04-09 03:00:04, 100.1, 98.4, 96.6
2017-04-09 03:37:13, 100.1, 101.8, 96.4
Then I could easily go through this and create the DataTable. I'm not sure how to do this in MySQL, although even if it's a good idea.
Thank you for taking the time and in advance for your help. Hope I was clear enough.
PS. I guess the closest thing I came across is a Mysql query to convert rows to columns dynamically . I'm going to play with this some more ...
source to share
as long as the x-axis (first column) is date,
you don't need to worry about ...
there is always temperature data for pools 1, 2 and 3 for the same lifetime
the diagram must be able to do it
as such, you can use a query similar to the following ...
select
created_at,
case when
pool_id = 1
then
temperature
else
null
end pool_1,
case when
pool_id = 2
then
temperature
else
null
end pool_2,
case when
pool_id = 3
then
temperature
else
null
end pool_3
from
temperatures
I was unable to get the SQL reference provided to work,
so I was unable to check the sql
I'm not sure if the return null
will work
source to share
To make sure the data is dynamic if another pool is added in the future, I decided to go with a padded array with array_pad()
and loop through the temperature dataset, sorted as I go. I also used Lavacharts which made it easier to work with Google DataTables. So here's my code (note, it takes more work to add annotation fields):
$dataTable = \Lava::DataTable();
$dataTable->addDateTimeColumn('DateTime');
// Add data column for each pool
$pools = \App\Pool::get();
foreach($pools as $pool) {
$p = "Pool $pool->id";
$dataTable->addNumberColumn("$p Temp");
// TODO: Create annotate fields for min and max temperatures
// For this, we'll need to do some clever padding using array_pad()
// and more clever index incrementing in the for() loop below.
// Perhaps it best to calculate and prepare in the temperatures query?
//$dataTable->addStringColumn("$p Title");
//$dataTable->addStringColumn("$p Text");
}
// Gather all the temperature data we wish to display. A year ought to be enough.
// At one hour updates, that makes for about 8,766 datapoints.
$temperatures = \App\Temperature::where('created_at', '>=', \Carbon\Carbon::now()->subYear())
->orderBy('created_at', 'desc')
->orderBy('pool_id', 'asc')->get();
// Grab all the timestamps and organize into an array
$created_ats = \App\Temperature::groupBy('created_at')->pluck('created_at');
// Let go through each datetime field and collect all temperatures recorded on that datetime.
// Then, let store those temperatures into the appropriate index of the data row.
foreach($created_ats as $created_at) {
$dataRow = [$created_at]; // Start the array off by adding date to beginning
$dataRow = array_pad($dataRow, 1 + count($pools), null); // +1 to account for $created_at column
//$dataRow = array_pad($dataRow, 1 + (count($pools) * 3), null); // TODO: multiply by 3 for annotation fields
// Start going through each temperature recording and assign to proper spot in dataRow array
// If temperature is not found for the datetime, the array_pad() above already accounts for null
// in that index. Note, the created_at comparison only accounts for the hour, not seconds or minutes.
// TODO: Implement min and max temperature annotations.
//$maxTemperature = 0;
//$minTemperature = 999;
foreach($temperatures as $temperature) {
// TODO: Implement min and max temperature annotations.
//$maxTemperature = ($temperature->temperature >= $maxTemperature) ? $temperature->temperature : $maxTemperature;
//$minTemperature = ($temperature->temperature <= $minTemperature) ? $temperature->temperature : $minTemperature;
// Compare date and hour, then assign to appropriate index of the data row according to pool id.
// ie. Pool ID #1 needs to be placed in [1], Pool ID #2 in [2] and so forth. Remember, [0] is date.
if ($temperature->created_at->format('Y-m-d H') == $created_at->format('Y-m-d H')) {
for ($i = 1; $i <= count($pools); $i++) {
if($temperature->pool_id == $i) {
$dataRow[$i] = $temperature->temperature;
}
}
}
}
// We've gone through all temperatures for this created_at datetime.
// Add the resulting dataRow to the dataTable.
$dataTable->addRow($dataRow);
}
// What we're left with is a bunch of rows that look like this!
// TODO: Add annoation fields for min and max temperatures.
// $dataTable->addRow(['created_at',
// 'temperature1',
// 'temperature2',
// 'temperature2'
// ]);
$jsonData = $dataTable->toJson();
// At this point, return $jsonData for use with google.visualization.DataTable(jsonData);
// Or, cache it and then return it, or whatever.
I would recommend caching the data as it takes a bit of time to render in the view (~ 1.9). So maybe this is not the fastest way, but it does the trick for me. It would be interesting to dig further and find other optimizations. At the moment, I'm happy with that.
source to share