1．データ受け入れ先となるテーブルを作成
※database nameはsampleとする
# mysql -u [user name] -p[password] [database name]
create table google_temp ( id integer NOT NULL AUTO_INCREMENT PRIMARY KEY, observation_time char(20), temp_c char(10) );
2．Shell Scriptの作成
指定した部分を正しく抜き出せるかコマンドライン上で試行錯誤しつつ確認
２－１．
#curl --silent --location http://www.google.com/ig/api?weather=Yokohama,Kanagawa
<?xml version="1.0"?><xml_api_reply version="1"><weather module_id="0" tab_id="0" mobile_row="0" mobile_zipped="1" row="0" section="0" ><forecast_information><city data="Yokohama, Kanagawa Prefecture"/><postal_code data="Yokohama,Kanagawa"/><latitude_e6 data=""/><longitude_e6 data=""/><forecast_date data="2012-01-12"/><current_date_time data="1970-01-01 00:00:00 +0000"/><unit_system data="US"/></forecast_information><current_conditions><condition data="Mostly Cloudy"/><temp_f data="41"/><temp_c data="5"/><humidity data="Humidity: 31%"/><icon data="/ig/images/weather/mostly_cloudy.gif"/><wind_condition data="Wind: N at 12 mph"/></current_conditions><forecast_conditions><day_of_week data="Thu"/><low data="32"/><high data="43"/><icon data="/ig/images/weather/mostly_sunny.gif"/><condition data="Partly Sunny"/></forecast_conditions><forecast_conditions><day_of_week data="Fri"/><low data="34"/><high data="54"/><icon data="/ig/images/weather/mostly_sunny.gif"/><condition data="Partly Sunny"/></forecast_conditions><forecast_conditions><day_of_week data="Sat"/><low data="37"/><high data="48"/><icon data="/ig/images/weather/mostly_sunny.gif"/><condition data="Partly Sunny"/></forecast_conditions><forecast_conditions><day_of_week data="Sun"/><low data="36"/><high data="48"/><icon data="/ig/images/weather/mostly_sunny.gif"/><condition data="Partly Sunny"/></forecast_conditions></weather></xml_api_reply>
２－２．
#curl --silent --location http://www.google.com/ig/api?weather=Yokohama,Kanagawa | cut -d "<" -f 17
temp_c data="5"/>
２－３．
#curl --silent --location http://www.google.com/ig/api?weather=Yokohama,Kanagawa | cut -d "<" -f 17 | sed 's/temp_c data=\"//'
5"/>
２－４．
#curl --silent --location http://www.google.com/ig/api?weather=Yokohama,Kanagawa | cut -d "<" -f 17 | sed 's/temp_c data=\"//' | sed 's/\"\/>//g'
5
指定した部分を正しく抜き出せたら，Shell Scriptに組み込む
# vi get_temp.sh
#!/bin/sh
observation_time=`date +%Y%m%d%H%M%S`
str_google_temp=`curl --silent --location http://www.google.com/ig/api?weather=Yokohama,Kanagawa | cut -d "<" -f 17 | sed 's/temp_c data=\"//' | sed 's/\"\/>//g'`
echo "insert into google_temp (observation_time,temp_c) values ("$observation_time","$str_google_temp");" >> google_temp.sql
/usr/bin/mysql -u [user name] -p[password] -s [database name] < google_temp.sql
rm google_temp.sql
exit 0
3．Shell Scriptへの実行権限付与
# chmod u+x get_temp.sh
4．Shell Scriptの実行
# ./get_temp.sh
5．シェルスクリプトを用いてMySQLに値が登録された事の確認
# mysql -u [user name] -p [database name]
select * from google_temp;
6．crontabへの登録
# crontab -e
6,16,26,36,46,56 * * * * /root/get_temp.sh &
7．crontabに正しく登録されたか確認
# crontab -l
８．MySQLに登録された値の表示
# cd /var/www/html
# vi google_temp.php
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>SA2011 sample</TITLE>
<STYLE TYPE="text/css">
<!--
.table1 {
width: 600px;
border-collapse: collapse;
border: 1px #111111 solid;
}
.table1 TD {
border: 1px #111111 solid;
}
-->
</STYLE>
</HEAD>
<BODY>
<H1>SA2011 google_temp</H1>
<br />
<?php
$db_user = "[database username]";
$db_password = "[database password]";
$db_name = "sample";
$db_table_name = "google_temp";
$db_host = "localhost";
$db = mysql_connect($db_host,$db_user,$db_password);
mysql_select_db($db_name,$db);
$strsql = "SET CHARACTER SET UTF8";
mysql_query($strsql,$db);
$str_sql = "select tmp.id, tmp.observation_time, tmp.temp_c from (select id, observation_time, temp_c from ".$db_table_name." order by id desc limit 144) AS tmp order by tmp.id";
$rs = mysql_query($str_sql,$db);
$num = mysql_num_fields($rs);
print("<table CLASS='table1'><tr><th colspan=3>google_temp data</th></tr>");
print("<tr>");
for ($i=0;$i<$num;$i++){
print("<td><b>".mysql_field_name($rs,$i)."</b></td>");
}
print("</tr>");
while($row=mysql_fetch_array($rs)){
print("<tr>");
for($j=0;$j<$num;$j++){
print("<td>".$row[$j]."</td>");
}
print("</tr>");
}
print("</table>");
mysql_free_result($rs);
mysql_close($db);
?>
</BODY>
</HTML>
９．MySQLに登録された値の表示(可視化)
# cd /var/www/html
# vi google_temp_chart.php
<html>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'date');
data.addColumn('number', 'temp_c');
data.addRows([
<?php
$db_user = "[database username]";
$db_password = "[database password]";
$db_name = "sample";
$db_table_name = "google_temp";
$db_host = "localhost";
$db = mysql_connect($db_host,$db_user,$db_password);
mysql_select_db($db_name,$db);
$strsql = "SET CHARACTER SET UTF8";
mysql_query($strsql,$db);
$str_sql = "select tmp.observation_time, tmp.temp_c from (select id, observation_time, temp_c from ".$db_table_name." order by id desc limit 144) AS tmp order by tmp.observation_time";
$rs = mysql_query($str_sql,$db);
$num = mysql_num_fields($rs);
while($row=mysql_fetch_array($rs)){
print("['".$row[0]."',".$row[1].",],");
}
mysql_free_result($rs);
mysql_close($db);
?>
]);
var options = {
width: 800, height: 480,
title: 'google_temp'
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="chart_div"></div>
</body>
</html>
