Welcome to EnviroDIY, a community for do-it-yourself environmental science and monitoring. EnviroDIY is part of WikiWatershed, a web toolkit designed to help citizens, conservation practitioners, municipal decision-makers, researchers, educators, and students advance knowledge and stewardship of fresh water. New to EnviroDIY? Start here

Connecting to the Internet

Home Forums Mayfly Data Logger Connecting to the Internet

This topic contains 21 replies, has 4 voices, and was last updated by  Scott Murrison 3 months, 2 weeks ago.

  • Author
    Posts
  • #12198

    Scott Murrison
    Participant

    Hello

    I am trying to use two mayfly dataloggers to collect data and port it to a sql database. I am an infant when it comes to anything related to electronics and programming. I have managed to get my remote unit programmed and talking with an xbee radio with a xbee explorer. It even shows the data on a serial monitor. Now I am trying to figure out how to use the second XBEE/Mayfly to collect the data from the remote unit and post it to the SQL database. What I need to know is what hardware do I need to connect the base station mayfly to the internet and if there are any sample sketches available to show how to get the data to a SQL database on a website? I am using XBEE radios to communicate between the remote station and base station. Since the XBEE headers are occupied, how do I connect the mayfly to an Ethernet shield to connect to my router?

    Thanks for the help.

    Scott

  • #12201

    Scott Murrison
    Participant

    Ok, I was able to get data from a mayfly with ultrasonic sensor to a mayfly connect to a computer via xbee radios. Everything is being read on a serial monitor. Now I want to connect the second mayfly to the internet so I can forward the data to a sql database. What hardware is needed to connect the mayfly to the internet? I know there is an Arduino Ethernet shield, but is it compatible with the mayfly? I am clueless when it comes to this. So far it has been google and youtube to the rescue to get the mayflys to communicate via xbee radios. However, I haven’t been able to find any tutorials specifically for connecting the mayfly to the internet. Is the mayfly the best option for the gateway, or would it be better to go with a Raspberry Pi or another adruino board?

    Thanks for the help.

    Scott

  • #12202

    Shannon Hicks
    Keymaster

    The easiest way build a base station for relaying data heard via Xbee is to use an Uno Ethernet board (it’s like an Uno but has built-in ethernet hardware, so no separate shield is needed). Then connect the Xbee to the Uno Ethernet and write a script to capture all the incoming Xbee data and send it to the appropriate web site URL. I built several systems like this over the past few years, and they work non-stop relaying several thousand transmissions each day. I’ll post a picture of the boards and the little enclosure I put them in, but it’s all relatively simple and around $100.

    If your logger location has Wifi, you could also just use a Wifi-bee module and send data directly to the web from the Mayfly. Or use a GPRSbee to send it via cellular.

    As for the database, if you don’t have experience with SQL and HTML, it’s not simple to set up all the database stuff and the supporting web host and pages. That’s how I did it for the past 5 years with our original system, and it’s probably too complicated for most users. But we’ve been developing a new system at https://data.envirodiy.org where users can send their data to the site where it can be stored and can easily be seen with a time series viewer. We’re working on the documentation on how to write the Arduino code for how to send data to the site, but there should be some examples in our Github repository to get you started.

  • #12204

    Scott Murrison
    Participant

    Shannon

    Thanks for the guidance. I have a friend to do the SQL Database for me, so that shouldn’t be a problem.

    These units will go in runoff retention ponds at cattle feed yards to monitor water levels. Most of the facilities don’t have wifi access, so the WIFI Xbee radio isn’t really an option. The GPRSBee is a good option, but I am looking at about 150 units. The best cell phone network out in my area is the Big Red One. They charge way to much to use their network. We do have AT&T, but the service is sketchy in most areas. Do you know of any lower rate sim cards that may work? These units will only wake one time per day then go back to sleep. Data transmission will be really low. I would like to use GPRSBee if possible, that would allow me to avoid using a mesh networks with XBEE radios.

    I am very interested in seeing the photos of the units you built.

    Again, thanks for the information.

  • #12219

    Scott Murrison
    Participant

    Shannon

    I received the Arduino Ethernet board and xbee shield. Now, I am looking for example code to receive the data via xbee from the remote unit and port it to a URL. Is there any example code you would be willing to share.

    Thanks
    Scott

  • #12220

    Shannon Hicks
    Keymaster

    Here’s a couple photos of the UnoEthernet/Xbee receiver I built a long time ago. I has been running non-stop since February 2013 and receives about 4500 transmissions per day. With an average of 10 parameters per transmission, that’s 16.5 million parameters per year, so this little box has passed over 80 million parameters into my simple database in the past 5 years. I haven’t had to restart it do anything in the past 5 years except once when I had to update the IP address of the destination website nameserver when the host did some updates that resulted in a new address.

    The Xbee module is a XbeePro 900Mhz and is mounted to an Xbee adapter board from Adafruit. I drilled holes in the case and bent the LEDs so that they poke through the case you can see the Xbee’s status. It’s held on to the case by the SMA jack of the XbeePro. It’s connected to a coax line that runs to the roof where we mounted a high-gain omnidirectional antenna.

    This is what we use for receiving data from all of the stations within about a mile of our research lab. The rest of our dataloggers in other locations send data to the database via cellular modules with a $5/month cell plan from Hologram.io

    We have more than 100 loggers streaming live data every 5 minutes, that adds up to around 9 million points per month and over 100 million points per year.

  • #12223

    Shannon Hicks
    Keymaster

    Here’s the code running on the UnoEthernet/Xbee shown above, but with the URL and IP addresses removed. I based this all from some examples I found about 6 years ago, so there’s probably better ways to do this nowadays.

  • #12224

    Scott Murrison
    Participant

    Shannon

    Thank you very much. This is exactly what I am wanting to do. I appreciate your willingness to help.

    Scott

  • #12250

    Scott Murrison
    Participant

    Shannon

    Can you tell me where you source your cellular modules?

    Thanks

    Scott

  • #12251

    Shannon Hicks
    Keymaster

    As mentioned above, I use lots (100+) of GPRSbee modules, they are available from SODAQ in the Netherlands. They are 2G, so they only work in certain areas, but we find there’s coverage in about 90% of our locations. There are 3G and 4G bee modules available from other companies like Digi, but they cost at least twice as much so we usually just use the GPRSbees.

  • #12253

    Scott Murrison
    Participant

    Shannon

    I got your code to communicate with MySQL database. I have a table set up for ID(auto-increment, primary key) TimeStamp, and thisData. The auto increment ID and TimeStamp work, but the data is not populating the table, column is there, it is just blank. I do get data on the serial monitor. I’m guessing the problem is in the php file of the database setup.

    Any Ideas?

  • #12254

    Shannon Hicks
    Keymaster

    There’s a lot of things that could cause your data to now show up in the table, either on the Arduino end or the server/PHP end. Have you tried visiting the URL using your web browser to make sure you’re able to post data to the table using a properly formatted URL? If you can do it with a browser, then you know your PHP code is right and the issue is with your Arduino sketch. But if you can’t add data to the table via your browser, then you’ll need to fix your PHP code on the server.

  • #12255

    Scott Murrison
    Participant

    Ok, I can post data to the database via the URL, so the problem is somewhere in the Arduino code. I’m using your code above at my base. I changed all the required parameters to my network settings. It appears the Arduino Ethernet is communicating with the website as the php script is inserting the id and timestamp in the database table. I’m a little confused with the Arduino code. The php script is looking for “thisData” from the Arduino. Is that the string that is sent from your code above?

    Thanks for the help.

    Scott

  • #12256

    Shannon Hicks
    Keymaster

    The code I posted above is for the Arduino/Xbee receiver module. All it does is “visit” a URL that it “hears” on the radio. The Mayfly logger in the field is the unit that actually generates the rest of the URL. I put the beginning of the URL in the receiver code above just for simplicity, but in real life the entire URL is generated on the field loggers because sometimes they visit different PHP scrips or domains. If you have more than one logger sending data, you’re going to want to have different tables for each station. Then you have the parameters to send. So the logger payload that goes into “thisData” in the example would be a string that should look something like this: UnitId=001&Temperature=14.2&Voltage=2.5&Depth=342.5

    And the full URL would be: https://www.somewebsite.com/demo.php?UnitId=001&Temperature=14.2&Voltage=2.5&Depth=342.5

    Your PHP script would then put the 3 parameters into the table name designated by “UnitId”.

  • #12257

    Scott Murrison
    Participant

    Ok, so it is the code on my sensor/logger that needs to be changed. Do you by chance have any example code that I can look at to try and figure out where I need to go? The sensor is an ultrasonic sensor. I have attached the code I am using for the sensor. This code will change as I only want to send data one time per day.

  • #12258

    Shannon Hicks
    Keymaster

    Where did you get that code example? It looks like a combination of some older code I used to use plus some newer stuff, but there are several mismatches and missing things so it won’t work properly as written. To transmit on an Xbee module, you should use Serial1. Serial0 is the port you should use for printing to the serial monitor. Your example above has all the status stuff happening on Serial1, so that should be changed to just Serial and then you can transmit the Xbee string payload on Serial1, since that’s the default configuration of the solder jumpers on the back of the Mayfly.

    Once you have taken whatever sensor measurement you want, you can build a data record string append to the date/time/battery/boardtemp string and then write it to the memory card, and then also use those same variables to build the URL string in a separate function. Then just send that URL string to the Xbee serial port (assuming you’ve turned on the Bee module in advance, I use the pin-sleep configuration so I’m able to toggle the Beee module on and off).

    Also, in your code above, the onboard battery voltage measurement part in the createDataRecord has the ratio as 1.47, which is only correct for Mayfly boards v0.3 and 0.4. For boards v0.5 and above, the ratio is 4.7. Which version of Mayfly do you have?

  • #12263

    Scott Murrison
    Participant

    I’m 99% sure most if not all of this code is yours or someone associated with Envirodiy. If you can’t tell, I know very very very little about arduinos. I have a friend that put this together for me and I pointed him to this website for guidance. I’m sure he used the copy and paste method for coding. He didn’t have time to work with the xbee radios, so I took the code and changed things to serial1 to try and get the communication to work. It works, but obviously it isn’t the way things should be done. I have the V0.3 boards. I got them when you first went live with them. They are great boards and fun to play with.

    The plan is to take the sonar reading, battery voltage, and date string send it to the base unit then have the base unit send it to the website and MySQL database. I would also like to save the string to an SD card at the base station. This way I have a backup if the network should go down.

    You mentioned having a table for each sensor in the database. I could have up to 15 sensor loggers communicating to the base station in 1 to 5 minute intervals for one client. Is it possible to combine the strings from all sensor loggers into one and send it to one table in the database? The sensors will be setup on individual ponds, then the report would look like:
    Pond 1, Pond 2, Pond 3, etc.

    These could probably be inserted into separate tables in the database then generate a report to combine them by date read.

    This is getting way over my head. I appreciate all the help and code examples you are willing to share.

    Scott

  • #12277

    Sara Damiano
    Participant

    Most of your code looks familiar, though very mish-mash. I’m glad it works.

    As far as getting the data into a website or MySql database… Doing that is all going to be the responsibility of your web receiver, that is, whatever website the internet connected radio receiver visits. You have to use your php or whatever set up for that.

    Have you looked at the Wiki Watershed/EnviroDIY data portal? https://data.envirodiy.org/ If you are able/willing to have your data publicly viewable, you can take advantage of the structure already in place there to store/view/graph your data. We’ve also created a library to simplify sending data there from many different kinds of sensors, including the Maxbotix sonar sensors. https://github.com/EnviroDIY/ModularSensors. The communication with the data portal in this library is assumed to be via direct internet connection (cell/wifi) but you could also use most of the functionality and set it to another radio instead.

  • #12278

    Scott Murrison
    Participant

    Sara

    Thanks for the information on the data portal. I would like to use it for the simplicity, but I’m sure my clients would not like to have their data publicly available.

    I have the database set up and the php script working to add the data to the database. Now I just need to get the sensor data sent to the database. I was confused on how Shannon’s code worked. After her explanation I think I have an idea. Basically my sensor code needs to be changed to create a string with the website address and the sensor data, and time stamp. I have spent several days reading examples trying to figure out how to generate the script. Finally last night I found one that I believe does what I need it to. Now I just have to sit down and figure out what parts of the existing code I don’t need and remove it. This is all way above my pay grade, so anything I come up with will not look right and probably won’t be the most efficient way to tie everything together. Add that to my lack of knowledge and that always makes if fun to try and figure out where the problems are.

    Thanks for the help.

    Scott

  • #12279

    Scott Murrison
    Participant

    FYI, the link to the data portal is not working. Or at least it isn’t working for me.

    • #12280

      Heather Brooks
      Keymaster

      It appears that the forum software is adding https to links. If you just type data[dot]envirodiy[dot]org into your browser you should be able to reach the data portal.

  • #12281

    Scott Murrison
    Participant

    Thank you Heather.

You must be logged in to reply to this topic.