Project in progress
OpenWeatherMap to SQL-Database Demo

OpenWeatherMap to SQL-Database Demo

Demo to show online OpenWeatherMaps.org and send data to your SQL server.

  • 1,500 views
  • 2 comments
  • 6 respects

Components and supplies

Apps and online services

About this project

OpenWeatherMap

If you like to add weather info to your connected Arduino, openweathermap.org is a free and easy to use service. It supplies weather info, but you can also use it to look up GPS position of a city, or receive the exact time (i.e. in case you don't want to use an NTP-server routine). Register on the site, generate an ApiKey and your are free to go. API calls look like this:

http://api.openweathermap.org/data/2.5/weather?q=Amsterdam, NL&APPID=dae1915abab0b947455a8dc969bb3b67&cnt=1&units=metric

and it returns a string of data in JSON-format. For this project we use the weather by city, but you can modify for other services like forecast info. Try this in your browser to verify the results. The JSON string can be parsed to pick out the data you need, and there is an excellent library by to have this done:

ArduinoJson

The library at https://arduinojson.org/ is very easy to use, its well developed and very flexible - although its wise to dig a bit deeper into the use of strings and pointers. Install the library in your Arduino IDE, and take a look at some examples how JSON strings are parsed: link

As I like to avoid the use of the 'String' type (as its not always so stable out of my experience), I use Char* to store string data, and in the library I use array-copy functions. All this is combined in a library:

OpenWeather Arduino Library

This library creates a new object type called OpenWeather, by calling the object function .setup(char* key) the APi key can be attached. by calling .getweather(char* Location) the weather data is retrieved of the Location, and stored in the object-variables. easy, simple, straight forward - see example code.

Install the library in your Arduino Library-directory (unzip). As the library is build with the MKR1010 WiFi library (WifiNINA.h), you can - if required - change this for your own wifi-board by changing the wifi-lib in OpenWeather.h:

ifndef OPENWEATHER_H
#define OPENWEATHER_H
//#define DEBUG_ON
#include "Arduino.h"
#include <ArduinoJson.h>
#include <WiFiNINA.h>                  // Replace with your own Wifi Library
#define MAXBUFFER 2000                // MAXBUFFER DATA
class OpenWeather
{
 public:
 OpenWeather();
 void setup(char *key);
 void setupserver(char *srvr);
 char* raw();
 void getweather(char *Loc);
 char WeatherApiKey[64];
 char WeatherServer[128]; 
 char WeatherBuffer[MAXBUFFER]; 
 float Longitude;
 float Latitude;
 char Main[24];
 char Description[64]; 
 float Temperature; 
 long Pressure;
 long Humidity; 
 float Windspeed;
 long Winddirection;    
 private:                      // 
 int _cstring(char *c1,char *c2);
 int _cstringc(const char *c1,char *c2);
};
#endif

MySQL-Server Upload

Second part in this demo is storing the weather data in an MySQL database. Its not really practical (as this info is on OpenWeather.org all the time anyway), but for IoT demo purposes its nice :) For this demo I'm using a local MySQL-server on my Synology: enabled Web-Server (Nginx back end), installed Mariadb (MySQL) and PhPMyAdmin (SQL-Manager)

Tricky parts to check for setting up the MySQL:

  • Create a new user (ie Arduino) with read/write allowance on your server-directory, give this user database Priviliges in PhPMyAdmin.
  • Install PhP (5.6 in this case), enable PHP - use phpinfo(); to check enables features
  • Enable PHP extensions -mysqli for MySQL access, and -gd (Graphics)

To setup the Database follow this example:

Example icreateproject - setup your database with PhPMyAdmin

PhP-Code

To move the data from the Arduino to the SQL database, we call PHP-scripts. in this case I use the HTTP-Get method (POST method is also possible, especially if you have larger sets of data - future post). I don't use any encryption of secure-call (SSL), as this runs on my local network. Calls look like this :

http://192.168.200.11/weather/write_data.php?temp=15&press=1012&hum=43

My local web-server IP is 19.168.200.11, I made a web directory 'weather' and call the script write_data.php, and pass variable temp, press and hum. This is the php script that passes the code to the MyQSL database:

<?php
include ('connect.php');
   // Prepare the SQL statement
   $sql_insert = "INSERT INTO data (description,temp,hum,press,speed,dir) VALUES ('".$_GET["description"]."','".$_GET["temp"]."' ,'".$_GET["hum"]."','".$_GET["press"]."' ,'".$_GET["speed"]."' ,'".$_GET["dir"]."' )";  
   // Execute SQL statement
if(mysqli_query($con,$sql_insert))
{
echo "Done";
mysqli_close($con);
}
else
{
echo "error is ".mysqli_error($con );
}
?>

The connect.php include file creates the database connection-string ($con) - see zip-file. Edit thjis file with the Web-Server user and password name (ie arduino). We can use a second php-script to call the database info, and put it in a table: display.php, result looks like this :

PChart Graphics in PhP

Additional it's possible to set the information gathered over time in a graphics form. There are many graphics libraries available, for this example I'm using pchart, an class-oriented library withe many options.

Install the library in the webs-erver path, and be sure you have the -gd extension enabled in PHP. See graphics.php as an example :

Code

OpenWeather_Arduino_Lib.zipC/C++
OpenWeather library for Arduino
No preview (download only).
weather_Php_files.zipPHP
Php Script files for OpenWeather example
No preview (download only).
WeatherTest.inoC/C++
Arduino Script file
/*
Open Weather Demo for MKR1010 (Wifi with UBLox NINA module / Espressif = wifi)
J.Vos 2019

Stream OpenweatherMaps data to your SQL server, and make PHP Graphics or lists.

Open Weather API : https://openweathermap.org
SQL example : https://icreateproject.info/2014/12/14/arduino-save-data-to-database/

*************************************************************/
#include <WiFiNINA.h>         // change to your Wifi or Ethernet library - also change this in the openweather.h library !
#include <WiFiUdp.h>          // for UDP NTP Server - get time
#include <RTCZero.h>          // for clock ticks
#include <OpenWeather.h>
#include "arduino_secrets.h"  // Secret info on Pass qwords and API keys etc.
#define DEBUG_X 1             // Debug info via Serial Print

//
//Define ServerSettings
//
#define VERSION "1.1.0"              // Software verson 
#define SSERVER_PORT 80              // SQL Server Port
char apiKey[]= SECRET_APIKEY; 
char ssid[] = SECRET_SSID;           // your network SSID (name) - arduino_secrets.h
char pass[] = SECRET_PASS;           // your network password - arduino_secrets.h
IPAddress SQLServer(192,168,200,11); //  SQL server for dAta logging (local IP adress of my Synlogy server)


//
// Define RTC timers
//
#define LIFETICK 300                 // Life tick in seconds 
RTCZero rtc;                         // RTC Clock
long LogCheckTime;
unsigned long epoch= 1541062800UL;   // epoch time global variable


OpenWeather MyWeather;               // OpenWeathermaps Object
char Loc[]= "Amsterdam,NL";          // Location string  - see openweathermaps.org how to use the API interface
   
void setup()
{ 

/*********** Serial SETUP  **********/
int t=10;  //Initialize serial and wait for port to open, max 10 second waiting
  Serial.begin(115200);
  while (!Serial) {
    ; delay(1000);
    if ( (t--)== 0 ) break;
  }
/*********** RTC SETUP  **********/
rtc.begin();
rtc.setEpoch(epoch);                             // Set Time RTC to base time
/*********** WIFI SETUP  **********/
if (WiFi.status() == WL_NO_SHIELD) {             // check for the presence of the shield:
  #if DEBUG_X
    Serial.println("WiFi shield not present");
  #endif
  while (true);                                  // don't continue if no shield
  }
delay(1000);
  #if DEBUG_X
   Serial.println("WiFi starting...");
  #endif  
StartMyWifi();                                   // Start Wifi login 
/*********** Weather Object SETUP  **********/
MyWeather.setup(apiKey);
MyWeather.getweather(Loc);
Serial.print("\n# Main Weather : ");Serial.print(MyWeather.Main);
Serial.print(" / ");Serial.println(MyWeather.Description);
LogCheckTime = rtc.getEpoch() + LIFETICK;        // Set initial Log Check timer
} // endSetup




//
// Main SUPER Loop
//
void loop()
{
  Serial.print("."); delay(5000);
  ManageTimeKeeper(); 
} // end Main loop



//
// Time Keeper , do the house keeping : check log time, send data to SQL server
//
void ManageTimeKeeper()
{

if ( rtc.getEpoch()>= LogCheckTime ){                                 // if log Timer: check weather and log SQL
#if DEBUG_X  
   Serial.print("\n# Log SQL");
#endif
  MyWeather.getweather(Loc);
  LogSQLServer();
  LogCheckTime = rtc.getEpoch()+LIFETICK;
  if ( (WiFi.status() != WL_CONNECTED) || (WiFi.RSSI() <= -90) || (WiFi.RSSI() ==0) ) StartMyWifi();  // check wifi in case not connected : re-connect
   }
}



/**** SQLroutine  *****/
void  LogSQLServer(){

WiFiClient clients;
clients.connect(SQLServer,SSERVER_PORT);

if (clients.connected() ) {                                                   // check connected
  clients.print("GET /weather/write_data.php?");
  clients.print("description=");clients.print(MyWeather.Description);         // write PhP command
  clients.print("&temp=");clients.print(MyWeather.Temperature);
  clients.print("&hum=");clients.print(MyWeather.Humidity);
  clients.print("&press=");clients.print(MyWeather.Pressure);
  clients.print("&speed=");clients.print(MyWeather.Windspeed);
  clients.print("&dir=");clients.print(MyWeather.Winddirection);
  clients.println(" HTTP/1.1");
  clients.print("Host: ");clients.println(SQLServer);
  clients.println("Connection: close");
  clients.println();
  clients.flush();
while ( clients.connected() ) clients.stop();
#if DEBUG_X
    Serial.print("*SQL data logged");           // print a message out the serial port
#endif

  }
 else
 {
 #if DEBUG_X
    Serial.println("*SQL not connected");           // print a message out the serial port
#endif
 }
}



/**** WIFI ROUTINES  *****/
// Login to local network  //
void StartMyWifi(){
int t=0;
int status = WiFi.status();

WiFi.disconnect(); delay(3000); printWiFiStatus();

if ( (status != WL_CONNECTED) || (WiFi.RSSI() <= -90) ||(WiFi.RSSI() ==0) ) {                  // only try if not yet connected
  while ( ((status != WL_CONNECTED) || (WiFi.RSSI() <= -90) || (WiFi.RSSI() ==0) ) && t<5) {   // attempt to connect to WiFi network, max 5 times
    
#if DEBUG_X
       Serial.print("Attempting to connect to Network named: ");
       Serial.print(ssid);                                                                     // print the network name (SSID);
#endif
       status = WiFi.begin(ssid, pass);                                                        // Connect to WPA/WPA2 network.
       delay(2000);                                                                            // wait 2 seconds for connection:
       t=t+1;                                                                                  // try-counter
  }
  if ( status == WL_CONNECTED ) {
       //server.begin();                                                                       // start the web server outside this loop!
#if DEBUG_X
       printWiFiStatus();                                                                      // you're connected now, so print out the status
#endif
  }
  else{                                                                                        // no connection possible : exit without server started
#if DEBUG_X
    Serial.println("Connection not possible after several retries.");                   
#endif
  }
}
else{
#if DEBUG_X
    Serial.println("Already connected.");                                                     // you're already connected
    printWiFiStatus(); 
#endif
    }
}


// SERIALPRINT Wifi Status - only for debug
void printWiFiStatus() {
#if DEBUG_X
  if (Serial) {
    // print the SSID of the network you're attached to:
    Serial.print("\nSSID: ");
    Serial.print(WiFi.SSID());

    // print your WiFi shield's IP address:
    IPAddress ip = WiFi.localIP();
    Serial.print(" - IP Address: ");
    Serial.print(ip);

    // print the received signal strength:
    long rssi = WiFi.RSSI();
    Serial.print("- Rssi: ");
    Serial.print(rssi);
    Serial.println(" dBm");
  }
#endif
}

Schematics

simple diagram
Simple schematic obadp4ynhx

Comments

Similar projects you might like

Alexa Controlled Door Sign Demo

Project tutorial by 3magku

  • 2,229 views
  • 0 comments
  • 7 respects

Arduino Train Demo 2

Project showcase by Steve Massikker

  • 5,453 views
  • 3 comments
  • 21 respects

Electronic Chessboard (4x4 Demo Prototype)

Project tutorial by Tahir Miriyev

  • 1,363 views
  • 6 comments
  • 7 respects

Gestured Controlled Smart Home

Project in progress by Joaquim Silveira

  • 18,267 views
  • 4 comments
  • 36 respects

Smart Garage

Project in progress by Chris Hockuba

  • 8,291 views
  • 5 comments
  • 30 respects
Add projectSign up / Login