May 5, 2014

CTFS-ForestGEO database workshop in Singapore

CTFS-ForestGEO hosted a workshop in Singapore May 2nd - May 8th for a number of plot database managers. The workshop was organized around documentation developed to enable sites to fulfill a large part of the data management role locally with support from the CTFS-ForestGEO Systems Group. Among the plots represented at this workshop are Brunei, Hong Kong, Lambir, Pasoh, Palanan, Wanang, Bukit Timah, Sinharaja, Mo Singto, Doi Inthanon, and Huai Kha Khaeng. This workshop will focus on actively working with each plots own datasets wherever possible.

Workshop organizers Shameema Esufali and Suzanne Lao worked to introduce more advanced concepts to prepare data managers to screen and correct datasets in an organized manner.  They plan to go through the steps necessary for deriving location data from field maps using PointPicker and ImageJ, and show the latest versions of their toolkit including the dataentry system CTFSWEB, the generic form builder CTFSGDE, and the tool for correcting species allocation, TaxonEd. Some of the participants are brand new to MySQL so Shameema and Suzanne started off with a refresher (see Sandra's comments below). 

Fairoz kicks off the blog series with a recap of day 1 of the workshop in Sunny Singapore:
"Playing host to the participants from the various plots, I dropped by the Robertson Quay Hotel where they are staying to welcome them over breakfast. Saw many familiar faces and some new ones as well. Counted heads and found one to be missing.. Sandra! Found out she’ll be flying in late and will come directly to the workshop venue.
The bus took us to the west side of Singapore where the National Institute of Singapore (NIE) is located, and the venue for the workshop. Setting up individual workstations were less challenging this time round with many of us carrying our own universal adaptors and sharing around with those whose cables could not fit into the power points.

The venue for the workshop
Next came the installation of XAMPP, the latest ctfsweb version and a text editor to get us ready to start stringing together queries on our consoles. Morning was spent getting mysql running on the various operating systems on individual laptops. What a relief if was after lines and lines of sourcing and finally seeing the ‘mysql>’ line appear in the command prompt.

After a short recap on the need for database setup and simple commands, we were all punching scripts; SELECT-ing trees and JOINing tables, GROUP-ing census and ORDER-ing them alphabetically. But don’t forget to LIMIT those scripts or you might have rows and rows of data streaming down window.

So I will SELECT ‘participants’ FROM ‘various plots’ and welcome them to the country and the workshop."

Sandra Yap shares a recap of day 2:
"I'm not the best person to describe how Day 1 went. I came in late but directly from the airport so I hope to be forgiven. I can say, however, that a few people worked late into the night on the exercises Shameema and Suzanne tested us on. Those exercises were quite a challenge especially for those who don’t regularly use mysql.  Unused muscles (in the mysql parts of our brain) had to stretch and warm up.
As I said, warming up followed us through to Day 2. We got quizzed as soon as we opened our computers :) But I think we passed with flying colors.
It’s 11:30 am and everyone is trying to set up their systems to input census data. Small groups huddled around a laptop and heads whipping right and left throwing puzzled looks seem commonplace. PCs on Windows 7 or 8 or Unix and Macs combined with capital/small letter issues are presenting a challenge this time for Shameema and Suzanne."

Dr. Sandeep Pulla provided the pictures and commentary for day 3 of the workshop:

"On day 3, folks who already had their data in the CTFS database worked on executing MySQL statements to check and fix common problems with raw data files that could not be uploaded into the database as is. They also devised clever ways to check data correctness and consistency, exercising their newly acquired MySQL skills. The rest (from Mudumalai, Hong Kong, etc.) worked on uploading their own data into staging tables. This was followed by screening and uploading these tables into the final database. Suzanne conducted a "spot the SQL error" quiz to liven things up."

Day 3 in pictures:

Participants worked very hard and ate well

MySQL expert Musalmah shares a few tips with Dr. Kamariah.

Kenneth stares at an error in phpMyAdmin hoping it would go away. He fixed it subsequently (figure not shown).

Anuttara and a guest share a light moment.

Mohaziah and Suzanne discuss the finer points of relational databases.
Something clearly blew up on Jinlong’s computer. We capture this tense moment with Shameema, Komgrich, Witchaphart, and Jinlong.

Brain fuel was in ample supply.
From their happy expressions, one would think Nik and Fairoz were playing a computer game (they were not).
Shirley in a troubled moment. Her biscuit fell down.
The bananas and other snacks were supplied by kind courtesy of Dr. Lum.

Jonathan re-emerges from his laptop (after apparently being swallowed by MySQL).

Anutarra shares day 4:
"Today the class started with the demonstration of “ctfsgde” which is about generic data by Suzanne. Then we moved on to the very important part of database management“data screening” to find errors of our plot data. However we did some practice using the database called “green” instead of “blue”. We had “cleangreen” for sql to check the errors. Coming to this step, some people started to frown. This is only searching for errors. We haven’t begun fixing them. Come on guys. We can do it. Before fixing those errors, scripts must be written. Please keep this sentence in mind. When all sql statements run successfully, data screening on ctfsweb will be done for the next step. I’d better not go to more details.

In the afternoon we went to the Southern Ridges for a field trip, starting at Mt. Faber where we could see Sentosa and the sea. We began walking from here to Henderson waves where the wave-pattern bridge locates. This bridge is the highest pedestrian bridge in Singapore. Besides you can see the height above mean sea level on the floor at several points. We kept walking until arriving the forest walk. At this point many people started to shoot a lot of photos. The forest walk is an elevated steel walkway through the secondary forest. Let’s take a look of photos from the field trip. Clear your head and mind to get ready for the next day."

Photos from the forest walk. Passing a secondary forest.

A shot from the Henderson wave bridge

At Henderson wave, the wave-pattern bridge

Group Photos