Arctic Miner – Part 5

Blog Series Recap

Part 1 (click here to read): Becoming acquainted with Dr. Lawlor’s robotics experiments at UAF.

Part 2 (click here to read): Writing a C++ script to collect data from the robot and insert it into the PostreSQL database.

Part 3 (click here to read): Creating a Jupyter Notebook script to display the database content in real time.

Part 4 (click here to read): Successfully testing the robot and live data feed together.

Link to the Github repository where all of the code takes place.

Adding an Instance Number to the Database

After successfully testing the robot, Dr. Lawlor has a few requests for additional features.

First, we need a way to group the individual rows of data together. Once we start recording data, we want all the data from that recording session to have the same unique identification number. This will allow us to call all the data from that session and replay it in the visual feed, while ignoring data from other sessions.

For this, we come back to our C++ script.

We add a new column to our code that creates a SQL table, with the column named instance_num.

    CREATE TABLE IF NOT EXISTS test_conn ( \
    instance_num INT NOT NULL, \ 
    robot_json JSON NOT NULL, \ 

Now we’re going to do several things at once. We initiate the instance_num variable at 0, since the program won’t know at this point if there’s data in the database.

We reach out to the database to find out what’s there. If there is a previous instance of data records, we want to grab the most recent identifying number and increment it by 1.

On the other hand, if there is nothing in the database, we can just leave the instance_num value at 0.

// Set default instance_num to 0
int instance_num = 0;

// Check if there's an existing previous instance_num entry
// If so, iterate +1 over the last instance_num
try {
    cout << "Reaching out to db: " << endl;
    pqxx::work w(psql_conn);
    pqxx::result r = w.exec("SELECT * FROM test_conn ORDER BY created_at DESC LIMIT 1;");

    cout << "Reached" << endl;

    // Test, if there is data in the returned result, then iterate
    // If not, then keep the instance_num at 0
    if (sizeof(r) > 0) {
        stringstream instance_num_str;
        instance_num_str << r[0][1].c_str();

        instance_num_str >> instance_num;

} catch (const std::exception &e) {

    cout << e.what() << endl;
    return 0;


cout << "Session instance number: " << instance_num << endl;
cout << endl;

All that’s left is to pump this info into the database. The following code is similar to what we had before, but with the instance_num variable added.

stringstream output_assembled;
output_assembled << "INSERT INTO test_conn ";
output_assembled << " ( instance_num, robot_json )  VALUES  ( ";
output_assembled << instance_num << ", '";
output_assembled << output_json.dump();
output_assembled << "');";

string output = output_assembled.str();

try {

    pqxx::work w(psql_conn);



} catch (const std::exception& e) {

    cout << e.what() << endl;

    return 0;


Clearing the Database

The previous database table did not have a column for the instance_num variable. Without the correct number of columns between the C++ script and the database, there will be errors.

Since all of our current data is just a series of test samples, we can go ahead and delete the old table using a simple bash script provided by Dr. Lawlor.

# Drop the postgres database table used to store robot data.

sudo -su postgres << EOF

psql << SQL

\c test_cpp
DROP TABLE test_conn;



With the old table deleted, the C++ script will automatically create a new one with the correct number of columns.

Visualizing Only the Relevant Data

Our attention turns to the Jupyter Notebook script that displays our live data.

We’ll do two things here.

First, we will finish Dr. Lawlor’s first request by changing the code so that only data from one specific instance will display.

Second, we will respond to another of Dr. Lawlor’s requests. The task here is to make the display include not just two live data feeds, but three.

The reason for the second task is that three graphs will allow us to view: 1) the power being sent to a motor; 2) the movement of an actual tool or arm; 3) the resistance the tool or arm is experiencing as the robot mines against the ground.

WordPress and Jupyter Notebook don’t integrate together very well, so I’ll post everything all as one big code snippet in just a moment.

Most of the code below is the same as the code from Part 4, but with a few key differences.

The trickiest material comes in this section here:

def retrieve_data(show_print):
    # Prepare to select from the test_conn table
    results = None

    if df.empty:
        s = == curr_instance_num)
        rp = connection.execute(s)
        results = rp.fetchall()
        if (results == None):
            raise SystemExit("The postgresql database is empty.")
        print_data(show_print, results)
        results = session.query(test_conn).filter(test_conn.c.instance_num == curr_instance_num).order_by(
        if (results == None):
            raise SystemExit("The postgresql database is empty.")
        print_data(show_print, [results])

    # If the dataframe is empty, add this first result
    if (len(df) == 0):
        # To Do: Lift below into its own function, so as not to repeat
        for result in results:
            df.loc[len(df)] = [result[0], result[1], result[2][sub_plot_0_name], result[2][sub_plot_1_name], result[2][sub_plot_2_name], result[3]]

    # To Do: Change so that all results that are not yet in dataframe are added
    # up to the last one added

    # Otherwise, only add if the id in the result is different from the last id in dataframe
    elif (results[0] != df.iloc[-1]['id']):
        df.loc[len(df)] = [results[0], results[1], results[2][sub_plot_0_name], results[2][sub_plot_1_name], results[2][sub_plot_2_name], results[3]] 

The above snippet of code is where the script grabs only the relevant data from the database, and ignores everything else.

Adding in the third graph was fairly easy. There are several places in the code below where you can see the number of graphs expand from two to three — easy peasy.

Here is the full Jupyter Notebook at this point, with all changes.

There is one final step that I cannot finish at the moment, because it will be most efficiently solved if I wait for input from Dr. Lawlor.

I’m waiting for him to show me how his codebase can provide my C++ script with the data regarding load and resistance. This will allow the third graph in our display to show meaningful data.