Thursday, October 10, 2013

Whereabouts Software, Part 4 - Arduino Code

The Arduino code is responsible for connecting to the Wifi network, getting the latest data from the server (with the help of the fetch_current.php script), and moving the servos to the correct location.

Arduino sketch:  Whereabouts.ino

Before you download and install the sketch to your Arduino, you will need to update some of the code for your specific environment:

/* Network Information */
char ssid[] = "your_SSID";      //  your network SSID (name) 
char pass[] = "your_WPA_pass";  // your network password

// WiFi/Ethernet vars
IPAddress server(10,10,10,10);

This is all pretty self-explanatory. Add the Wifi SSID, WPA password, and server IP address to the sketch.

// Servo control vars
// The number of people (or hands) that I have on the clock
#define NUM_PEOPLE 5
// Names of the people we track (must match what is returned from the server
String peopleNames[NUM_PEOPLE] = {String("DAD"), String("MOM"), String("CHILD1"), String("CHILD2"), String("CHILD3")};
// The locations that we will fill from the server
String peopleLocations[NUM_PEOPLE] = {String(""), String(""), String(""), String(""), String("")};

This requires coordinating the sketch with the database user table. The strings "DAD", "MOM", etc. are usernames in the user table. These will be matched in the fetch_current.php script. If you have more or fewer users, they will need to be adjusted here.

// Make the HTTP request
client.println("GET /server_path/fetch_current.php HTTP/1.0");

Update the 'server_path' with the URL path to the fetch_current.php script on the server.

There are a few other tuning parameters in the sketch to control the servo movement, but these should get you started.


Whereabouts Software, Part 3 - Fetch Current

The PHP script that is called by the Arduino looks into the database and grabs the most recent locations for each user.  It then returns the data in an XML output that the Arduino can parse.

Here's the source code:  fetch_current.php

You will need to update the following with your own server data:

// Database connection information
$db_hostname = "localhost";
$db_username = "db_username";
$db_password = "db_password";
$db_database = "whereabouts";

When executed with `php fetch_current.php` you should receive output similar to this:

<?xml version="1.0" encoding="ISO-8859-1"?>

The clock will use the user names and locations to move the hands to the appropriate places.

Whereabouts Software, Part 2 - Fetch Locations

The server side collection and processing is done with a PHP script on a LAMP (Linux, Apache, MySQL, PHP) server and is executed by cron.  I set mine to query the phone data every 15 minutes, and it didn't seem to kill my battery any more than normal.  Increase or decrease the timing as you see fit.

The script is dependent on the work of Tyler Hall and his sosumi PHP library to get the location data from the Find my iPhone service.  You will need to download the class from his website.

The script included below will query the phone data for users in the database whose 'location_source' is 'GPS'.  You will need to modify the following in the script to get the location grabbing code to work:

// Enter your iCloud username and password
$ssm = new Sosumi('your_apple_id', 'your_password');

// Database connection information
$db_hostname = "your_database_host";
$db_username = "db_username";
$db_password = "db_password";
$db_database = "whereabouts";

Once this is completed, run the script with `php fetch_locations.php` and observe the output.  You should see something similar to this:

Timestamp: 1381456148
Date Time: 2013-10-10 20:49:08

 LAST DATA: USER1                      | PHONE DATA: USER1                     
 Timestamp: 1381454227                 |  Timestamp: 1381455996                
  DateTime: 2013-10-10 20:17:07        |   DateTime: 2013-10-10 20:46:36       
  Latitude: xx.xxxxxxxx                |   Latitude: xx.xxxxxxxxx           
 Longitude: yy.yyyyyyyy                |  Longitude: yy.yyyyyyyyyyy          
  Accuracy: zz                         |   Accuracy: zzz                       
  Location: home                       |   Location: home                      
            NEW PHONE DATA!            | No change.  Still at: home            

 LAST DATA: USER2                      | PHONE DATA: USER2                   
 Timestamp: 1381455883                 |  Timestamp: 1381455907                
  DateTime: 2013-10-10 20:44:43        |   DateTime: 2013-10-10 20:45:07       
  Latitude: xx.xxxxxxxxxxx             |   Latitude: xx.xxxxxxxxx           
 Longitude: yy.yyyyyyyyyyy             |  Longitude: yy.yyyyyyyyy          
  Accuracy: yy                         |   Accuracy: yy                        
  Location: home                       |   Location: home                      
            NEW PHONE DATA!            | No change.  Still at: home            

Here's the source code:  fetch_locations.php

Whereabouts Software, Part 1 - MySQL DB

The Whereabouts code is comprised of three parts: the webserver code that fetches the data from the iPhones, the database that stores the information, and the Arduino that represents the server data in physical form in the clock.

This post is on the MySQL database that hosts the location data.  I've attached a SQL file that can be loaded into an empty database.  Some tweaking will need to be done for your specific environment which I will detail below.


The 'clock_location' table is a key/value pair table that represents the physical locations on the clock.  The names in the 'name' field are important and must match exactly (case-sensitive) the values that will be used in the Arduino code for clock locations.  The twelve locations provided are the ones I used, but may be modified as needed.

The history table is an empty table that will be populated each time the location fetching code is run.  This will be purged after 30 days by the same code.

The location table is a mapping of location coordinates to common names, IDs, clock locations, and users.  This will need to be populated with your own data before use.  Here's how it should be populated:

  • id - auto increment table primary key
  • clock_location_id - the ID for the clock position.  What location should be pointed at by this location?
  • name - a user friendly name for this location.  This is only used by humans, so go nuts.
  • latitude/longitude - the lat/long coordinates for this location
  • user_id - the ID of the user that this location is valid for.  A given location might not be the same for all family members!

The user table maps the users to how location data is fetched.  Here's how it should be populated:

  • id - auto increment table primary key
  • username - A human readable name for this user.  This will be used in the fetch_current.php script and the Arduino code.  Make sure that this matches in the Arduino code!
  • location_source - this should be 'GPS' for those with iPhones.  To override a location and 'force' a user to be represented at a given location, put 'clock_<>' in this field.  To have a user use the same location as another user (useful for children) put 'user_<>' in this field.
  • apple_id - When using more than one phone on "Find My iPhone" you will need to get the ID of the device and put it here.
  • hand_id - This was supposed to add more flexibility to the Arduino code, but for now it's not used.

Replaced Arduino Ethernet Shield with Wifi Shield

We bought a new home a few months ago, and I'm finally getting around to some updates on the Whereabouts Clock.  I decided that hosting an 802.11b WEP secured Wifi network just to host the clock was a bit silly.

Prior to the move I was using the Arduino Ethernet Shield attached to a 3Com OfficeConnect WL-524 to bridge the clock data to my Wifi network.  The only way to connect it as a bridge was to use WEP security, which by all accounts is now completely insecure.

I saw that Arduino now sells a shield with a built-in 802.11g that support open, WEP, WPA networks.  I picked one up and after much frustration with upgrading the firmware on my Arduino (Uno rev. 1) and the shield itself, I finally got it online.  This reduces the amount of components in the clock, and certainly the power consumption as well.

I also redid my homebuilt servo shield because the Wifi shield required the use of one of the pins I had been using for the clock servos.  Here's the schematic:

I used a perfboard that I cut to size, and recut to fit around the wacky pin spacing on the Arduino between digital pins 7 and 8.  The jumper J6 is only needed if your Arduino is a rev 1 or is missing the IOREF pin.  Rev. 3 and I think rev. 2 have these extra pins and the jumper may be omitted.  Without the jumper, the Wifi shield will not initialize.  The LEDs 1-3 are green, yellow, and red, respectively and are used to indicate connection to Wifi/power, data processing/waiting, and error.  Each servo drives one hand of the clock.

Here's the finished photos: