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.

w.exec("\
    CREATE TABLE IF NOT EXISTS test_conn ( \
    id SERIAL NOT NULL PRIMARY KEY, \
    instance_num INT NOT NULL, \ 
    robot_json JSON NOT NULL, \ 
    created_at TIMESTAMP NOT NULL DEFAULT NOW()\
    );"
);

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;
        ++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);

    w.exec(output);

    w.commit();

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

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

    return 0;

}

Clearing the Database

Blog Series Recap

Adding an Instance Number to the Database

Clearing the Database

Visualizing Only the Relevant Data