Back-end, Front-end, Web programming

Using Google Charts with data taken from MySQL database.

The following is part of a project that uses a Lidar to measure vehicles’ speed using an Intel Edison to control it; then the data gathered will be sent wirelessly to a server, store it in a database, from where the data will be taken out and displayed for users to check out what’s going on in their street. To visually display the statatistics, I chose to use Google Charts.

Initial difficulties

I worked before with another Google APIs, explicitly Google Maps API where I displayed some maps information and created polygons to represent areas in a building (later on I’ll publish what I did in that). The thing with any API is that you’ve to be sure what data it requests to later on display information, in this case Google Charts needs a matrix, and in the examples shown there was only static data. To have “dynamic and real-time” graphs by user request the easiest way to configure that matrix was using JSON format; it takes data from a database (using MySQL queries and PHP to connect). It took me a little time to figure out how to get the matrices with the precise data I needed to display graphs and tables.

The data

To make the tests I had a Ubuntu 15.04 server virtualized (I chose to use GUI because it was easier for me to keep checking the tests from within), installed (and slightly configured) Apache, PHP and MySQL. Then I designed a database that pretty much contained all what the projects needs to store:

The design

And the final structure looks as follows:

The database tree

devices

reading

In the end, I changed “time” to have no decimal figures, and “speed” to have 4 significant figures, as well as the names of the tables to be all lowercase.
To generate dummy data I used Microsoft Excel and saved the file as a text (*.txt) one with tab separated values (as it’s easier for MySQL to import that kind of data).
To import the data to the database we have to enter MySQL from the command line:

mysql –u username –p --local-infile database_name

The –local-infile database_name is necessary to actually insert data as for security reasons it’s impossible to do it otherwise.
Then, being logged-in MySQL, type in the command line:

mysql> USE database_name
mysql> LOAD DATA LOCAL INFILE ‘/file_location/file_name.txt’ INTO TABLE table_name LINES TERMINATED BY ‘\r\n’;

The LINES TERMINATED BY ‘\r\n’ is useful especially if using a file formatted from Windows, but it’d be healthy to add it just in case.

The form

To let the user choose what they want to visualize, a form should be used, for that I made a small form.php file:



	
	
	
	
	Gonzalo's form test



	
		Pointing to Espoon keskus
		Pointing to Leppävaara
	
Date (format yyyy-mm-dd):
Starting date:
End date:
Time (format hh:mm):
From:
To:

The form action=”lidar_report_a.php” method=”post” is a handler used to send the information gathered by the form to the file that will process the information, the “post” method is used to hide to the user what is the information sent (the “get” method would show what is the information passed). This will be explained in another post.

MySQL to JSON, JSON to Google Charts

The data sent from the form will be assigned to variables in the “lidar_report_a.php” file, those variables will be used at the query to get data from the database and then display it; that php file looks as follows:
(NOTE: for wordpress format, all “script” are removed, thus I changed them to “scritp” they can be noticed.)







Gonzalo-Lidar Google Charts!





#chart_div {
	height: 100%;
	width: 100%;
}



 'time', 'type' => 'string'),
	array('label' => 'speed', 'type' => 'number')
);
$tableT['cols'] = array(
	array('label' => 'date', 'type' => 'string'),
	array('label' => 'time', 'type' => 'string'),
	array('label' => 'speed', 'type' => 'number')
);
foreach($result as $r) {
	$temp = array();
	$temp[] = array('v' => (string) $r['time']);
	$temp[] = array('v' => (int) $r['speed']);
	$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
foreach($result as $rT) {
	$tempT = array();
	$tempT[] = array('v' => (string) $rT['date']);
	$tempT[] = array('v' => (string) $rT['time']);
	$tempT[] = array('v' => (int) $rT['speed']);
	$rowsT[] = array('c' => $tempT);
}
$tableT['rows'] = $rowsT;
mysqli_close($conn);
?>


google.setOnLoadCallback(drawLineChart);
function drawLineChart() {
	var table = JSON.parse('');
	var data = new google.visualization.DataTable(table);
	var options = {
		title: 'Gonzalo\'s Google Charts test',
		legend: { position: 'bottom' }
	};
	var chart = new google.visualization.LineChart(document.getElementById('linechart_div'));
	chart.draw(data, options);
}



google.setOnLoadCallback(drawHistChart);
function drawHistChart() {
	var table = JSON.parse('');
	var data = new google.visualization.DataTable(table);
	var options = {
		title: 'Gonzalo\'s Google Charts test',
		legend: { position: 'bottom' }
	};
	var chart = new google.visualization.Histogram(document.getElementById('histchart_div'));
	chart.draw(data, options);
}



google.setOnLoadCallback(drawTable);
function drawTable() {
	var table = JSON.parse('');
	var data = new google.visualization.DataTable(table);
	var chart = new google.visualization.Table(document.getElementById('tablechart_div'));
	chart.draw(data, {showRowNumber: true, width: '100%', height: '100%'});
}


/* Include here the divs with id="linechart_div", "histchart_div" and "tablechart_div" */



Later on I’ll explain each part of the code as this is the “quick guide” to accomplish your graphs. Now, the results:

The form in action

The results

If you have any question post it in the comments and we’ll dig on it, cheers!

(Here you can find the Github repository)

Sin categoría

First blog post.

Welcome to my new blog!

Hi! I’m Gonzalo and one of my main insterests is information technology.  Here I will publish (almost) all the things that I am using in several projects I am working in, with the hope that someone out there will find the help they could be desperately looking for.

See you around!