Contents


Building location-aware IoT apps, Part 2

Build a PHP app that delivers geo-targeted marketing messages to an IoT device

Develop PHP apps for the Internet of Things with IBM Watson IoT Platform, IBM Bluemix, MySQL, and MQTT

Comments

Content series:

This content is part # of 2 in the series: Building location-aware IoT apps, Part 2

Stay tuned for additional content in this series.

This content is part of the series:Building location-aware IoT apps, Part 2

Stay tuned for additional content in this series.

IoT devices and applications have immense potential for technology and business. They make it possible to combine data streams from multiple sources in real time, analyze their effect on a system, and then react automatically and intelligently to them. And two-way communication, between the sensors and devices on the front line and the rules, filters, and business logic in the back office is key to achieving quick, efficient, and appropriate turnaround in response to external events.

In a previous article, I introduced you to the IBM Watson IoT Platform and explained how you can create useful applications by connecting it to data streams generated by IoT devices. As an example, I showed you how to integrate a PHP application that runs on IBM Bluemix with IBM Watson IoT Platform to receive GPS data from an Android smartphone and then use that data to track the location of the device in real time by using a web browser.

But there's more. The IBM Watson IoT Platform includes a number of other interesting features, such as filters and actions for automated data processing and support for publishing commands to devices. These features of Watson IoT Platform make it possible to implement two-way communication between IoT devices and IoT applications: applications can read incoming data streams from IoT devices, process this data in a useful way, and transmit the resulting information or commands back to IoT devices to "do something."

In this tutorial, I'll build on this idea and show you how to create an IBM Bluemix application that integrates with the IBM Watson IoT Platform and reacts to incoming GPS data from an Android smartphone by finding and delivering proximity-based marketing offers back to the device.

The idea of the sample app is fairly simple. As you travel around your local neighborhood or city, your smartphone will automatically notify you of nearby marketing offers, promotions, or events. So, for example, head to the mall, and you'll receive a notification with a coupon for 25% off a lunch pizza. Or, hit the movie theater, and you'll get notified about a live music event at a nearby bar. You receive relevant, localized offers in real time; also, the promoters get access to new prospects that were not in their direct line of sight.

So, how will it work?

  • First, we'll use the IoT Starter Application for Android to turn your Android phone into a GPS sensor that constantly publishes its location to the IBM Watson IoT Platform.
  • Then, we'll use the IBM Watson IoT Platform to monitor this data. When we determine that the device is stationary for a reasonable amount of time, we'll trigger a PHP application that is running on IBM Bluemix.
  • Next, the IBM Bluemix application will check a database to find available marketing offers that are near the device's reported location. If it finds a match, it will send an alert to the IoT device (Android smartphone) using the IBM Watson IoT Platform.
  • Finally, the alert is received by the IoT Starter Application for Android on the smartphone and sends a notification to the user.

What you'll need to build this IoT app

There are quite a few moving parts to this application, so here's a quick list of what you will need:

Because you are developing a PHP app that is very similar to the one in my previous tutorial, you should complete the steps in that tutorial first, and then come back to complete the steps in this tutorial. In particular, you need to complete these steps:

  • Step 1, where you create an IBM Bluemix app that is bound to the Internet of Things Platform service instance.
  • Steps 2 and 3, where you register an Android smartphone with your IBM Watson IoT Platform organization and where you compile and install the IoT Starter Application for Android on your smartphone.
  • Step 4, where you publish GPS data from the registered Android smartphone to the Internet of Things Platform service instance.
  • Step 5, where you generate an API key for the IBM Bluemix app to connect with the IBM Watson IoT Platform organization.
1

Create the offer database

First, we need to create a MySQL database that stores geo-targeted marketing offers. This database will also store the device's most recent location and a list of offers that have already been delivered to it. The PHP application will connect to this database on a regular basis, both to find offers and to update the device's location.

The easiest way to create this database is with the IBM Bluemix ClearDB Managed MySQL Database service, which provisions a hosted MySQL database in the cloud. The default plan includes a limited quota of free storage.

  1. Log in to your Bluemix account.
  2. From your dashboard, click Create Service.
  3. From the list of services, select Data and Analytics, and then select the ClearDB Managed MySQL Database service.
  4. Select the free CB5 plan, and ensure that the Connect to field is set up so that the database instance is bound to the same Bluemix application that hosts your Internet of Things Platform service instance.
    Figure 1. ClearDB service instance creation
    ClearDB service instance creation
    ClearDB service instance creation
  5. After the database instance has been initialized, open the ClearDB dashboard, and click the Endpoint Information tab to view and note the credentials for the instance.
    Figure 2. ClearDB service instance credentials
    ClearDB service instance credentials
    ClearDB service instance credentials

Now that we have our MySQL database set up, we need to create the necessary tables and initialize it with some marketing offers.

  1. Use the MySQL command-line client on your development system (or any other MySQL management tool) to connect to the MySQL instance and run the SQL commands below to create the necessary tables:
    CREATE TABLE `offer` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `latitude` decimal(10,8) NOT NULL,
      `longitude` decimal(11,8) NOT NULL,
      `message` text NOT NULL,
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `device_offer` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `device_id` varchar(255) NOT NULL,
      `offer_id` int(11) NOT NULL,
      `offer_delivery_date` date NOT NULL,
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `device_location` (
      `device_id` varchar(255) NOT NULL,
      `latitude` float(6,4) NOT NULL,
      `longitude` float(7,4) NOT NULL,
      `wait_time` int(11) NOT NULL,
      `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`device_id`)
    );

    Here's what each of these tables does:

    • The offer table holds a list of marketing offers. Each offer contains a message and the GPS coordinates for the location that is hosting the offer. Offers are identified and delivered to registered devices by the PHP application from this data.
    • The device_location table holds the last reported GPS coordinates for each registered device and the amount of time it has been in that location. This data is used to calculate when a device has been in a single location long enough to justify sending it an offer.
    • The device_offer table holds a list of offers delivered to each registered device each day. This data is used to ensure that the same device does not receive the same offer more than once per day.
  2. You can get started by seeding the offer table with some sample data, as shown below. Update the example GPS coordinates to reflect locations near you (and remember that you can use Google Maps to obtain the GPS coordinates for a specific location).
    INSERT INTO `offer` VALUES (51.5101195,-0.134737,'Get 20% off your pizza today!');
    INSERT INTO `offer` VALUES (51.5101195,-0.134737,'Buy one, get one free on event entry tonight!');
2

Define trigger rules and actions for device data

With the database defined and populated, the next step is to connect the data that's coming into the IBM Watson IoT Platform from the Android smartphone (which is now doubling as an IoT device) with the MySQL database by using a PHP application. While you can use a variety of ways to get the data to the database, this article will focus on using the trigger rules and actions in IBM Watson IoT Platform to invoke the PHP application and process the data.

If you followed the instructions in my previous article, you should already have an Android device type configured in your Internet of Things Platform service instance, an Android device registered with your IBM Watson IoT Platform organization, and an incoming data stream from the device to the IBM Watson IoT Platform. For this sample app, the next step is to create a schema for the Android device type, and then use that schema to configure trigger rules and actions.

  1. Open the IoT Starter for Android app on your Android device, enter the required credentials, and click Activate Sensor to connect it to the IBM Watson IoT Platform.
  2. In Bluemix, verify that your device data stream is appearing in your Internet of Things Platform service instance dashboard.
  3. From the Devices menu, select the Manage Schemas section, and click the Add Schema button.
  4. On the Device Type screen, choose Android from the selection list, and then click Next.
    Figure 3. Device schema creation
    Device schema creation
    Device schema creation
  5. On the Properties screen, click Add a property, select the From Connected tab, and select the d.latitude, d.longitude, and d.timestamp properties. Click "OK" to add these properties to the schema.
    Figure 4. Device schema creation
    Device schema creation
    Device schema creation
  6. Now that the schema is defined, go to the Rules menu. In the Browse section, click the Create Cloud Rule button.
  7. Enter a rule name (such as offer), and select the Android schema that you just created. Click Next to proceed.
    Figure 5. Rule creation
    Rule creation
    Rule creation
  8. Add the trigger rule and action to be performed to the cloud rule as follows:
    • In the If section, click New condition and add the condition timestamp != 0 by using the selection tools. Click OK to save the condition.
      Figure 6. Condition definition
      Condition definition
      Condition definition
    • In the Trigger section, set the rule to trigger if conditions persist for 1 minute.
      Figure 7. Trigger frequency definition
      Trigger frequency definition
      Trigger frequency definition
    • In the "Then" section, click New action and in the dialog box, click the Add action link. A number of pre-defined actions are available, including sending an email, displaying an alert, triggering an IFTTT recipe, or connecting to a Node-RED HTTP input node or webhook. Select the action type as webhook action and set the webhook URL to http://my-iot-app-[initials].mybluemix.net/webhook-offer.php and the action method to POST.

      Remember that you must replace the example host name in the action URL with the actual host name for your Bluemix application and don't worry about the PHP script at the end of the URL – it doesn't exist right now, but that URL will exist at the end of the article.

      Leave the other fields at their default values, and ensure that the request body includes the raw device message in the {{message}} placeholder as shown below. Click Finish to save the action.

      Figure 8. Action definition
      Action definition
      Action definition
  9. Verify that the new cloud rule appears in the Rules -> Browse section and activate it by sliding the State toggle.
    Figure 9. Rule activation
    Rule activation
    Rule activation

The end result of all this configuration is that while the device is active and sending data to the IBM Watson IoT Platform, the conditional test will always return true (the device time stamp will always be greater than zero). Therefore, the webhook action will be executed at the trigger frequency (every minute) and will send the device data to the specified URL by using POST.

To put it another way, with this configuration in place, you now have a way to process device data every minute as long as the device is active and connected to the IBM Watson IoT Platform.

3

Process device data and publish device commands

At this point, you have a database that contains geo-targeted offers, an Android smartphone (IoT device) that's transmitting its GPS coordinates, and a trigger that requests a URL every minute as long as it is receiving device messages. All that's left is to glue these components together with a PHP application and host that application at the specified URL.

Before we get into the nitty-gritty of the code, let's broadly outline what the PHP "glue" needs to do:

  • It needs to receive and decode the JSON data that is transmitted to it from the device.
  • It needs to update the database with the current location of the device or, if the device is stationary, it needs to update the recorded wait time.
  • When the device has been stationary for a sufficient amount of time, it needs to find available offers that (a) are within the device's radius and (b) have not already been sent to the device that day.
  • It needs to push all such offers to the device by using MQTT, on the command channel for the IoT Starter for Android application.

It seems like a lot of work, but it's actually not as difficult as it might appear at first glance. Here's the PHP application code, which should be saved as webhook-offer.php in your development directory:

<?php
// include class
require('phpMQTT.php');

// load configuration values
$config = array(
  'org_id' => 'IOT-ORG-ID',
  'port' => '1883',
  'app_id' => 'phpmqtt',
  'iotp_api_key' => 'IOT-API-KEY',
  'iotp_api_secret' => 'IOT-API-TOKEN',
  'device_id' => 'DEVICE-ID',
  'qos' => 1  
  'db_host' => 'DATABASE-HOST-NAME',
  'db_user' => 'DATABASE-USER-NAME',
  'db_pass' => 'DATABASE-USER-PASSWORD',
  'db_name' => 'DATABASE-NAME',
  'wait_time_trigger_min' => 5,         
  'proximity_trigger_km' => 1,          
);
$config['server'] = $config['org_id'] . '.messaging.internetofthings.ibmcloud.com';
$config['client_id'] = 'a:' . $config['org_id'] . ':' . $config['app_id'];

// report MySQL errors as exceptions
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// get JSON packet posted to webhook
$json = file_get_contents('php://input');

// decode JSON packet
// extract device ID, message contents
$data = json_decode($json);
$deviceId = $data->deviceId;
$message = json_decode($data->message);
$longitude = round($message->d->longitude, 4);
$latitude = round($message->d->latitude, 4);

// open database connection
$mysqli = new mysqli($config['db_host'], $config['db_user'], 
  $config['db_pass'], $config['db_name']);
if ($mysqli->connect_errno) {
  error_log("Failed to connect to MySQL: " . $mysqli->connect_error);
}

try {
  // check if this device already has a location record
  $sql = "SELECT latitude, longitude, wait_time FROM device_location 
    WHERE device_id = '$deviceId' LIMIT 0,1";
  $result = $mysqli->query($sql);
  $row = $result->fetch_object();
  // check if the last location recorded in the database matches the current location
  // if yes, update the wait time
  // if no, update the location record
  if ($result->num_rows == 1 && $row->latitude == $latitude 
    && $row->longitude == $longitude) {
    $wait_time = $row->wait_time + 1;
    $sql = "UPDATE device_location SET wait_time = '$wait_time', 
      updated = NOW() WHERE device_id = '$deviceId'";
    $mysqli->query($sql);
  } else {
    $sql = "DELETE FROM device_location WHERE device_id = '$deviceId'";
    $mysqli->query($sql);
    $wait_time = 0;
    $sql = "INSERT INTO device_location (device_id, latitude, longitude, 
      wait_time, updated) VALUES ('$deviceId', '$latitude', '$longitude', 
      '$wait_time', NOW())";
    $mysqli->query($sql);
  }
  
  // if the device has been in the same location
  // for the trigger number of minutes
  // find offers within the configured proximity radius
  // which have not already been delivered to the device today
  if ($wait_time == $config['wait_time_trigger_min']) {
    $proximity = $config['proximity_trigger_km'];
    $sql = "SELECT o.id, o.message, 
        (6371 * acos(cos(radians($latitude)) * cos(radians(latitude)) * 
        cos(radians(longitude) - radians($longitude)) + sin(radians($latitude)) * 
        sin(radians(latitude)))) AS distance
          FROM offer o
          LEFT JOIN device_offer dos ON o.id = dos.offer_id
        WHERE (dos.offer_delivery_date != DATE(NOW())
          OR dos.offer_delivery_date IS NULL)
        HAVING distance < $proximity
        ORDER BY distance
        LIMIT 0,10";
    $result = $mysqli->query($sql);

    if ($result->num_rows > 0) {
      // if offers found
      // initialize MQTT client
      $mqtt = new phpMQTT($config['server'], $config['port'], $config['client_id']); 
      $mqtt->debug = false;
      
      // connect to broker
      if(!$mqtt->connect(true, null, $config['iotp_api_key'], 
        $config['iotp_api_secret'])){
        error_log('Failed to Could not connect to IoT cloud');
        exit();
      } 

      // iterate over offer list and publish to device
      // update database with offer delivery status
      while ($row = $result->fetch_object()) {
        $offerId = $row->id;
        $message = $row->message;
        $mqtt->publish('iot-2/type/Android/id/' . $config['device_id'] . 
          '/cmd/alert/fmt/json', '{"d":{"text":"' . $message . '"}}', 1);      
        $sql = "INSERT INTO device_offer (device_id, offer_id, offer_delivery_date) 
          VALUES ('$deviceId', '$offerId', DATE(NOW()))";
        $mysqli->query($sql);
      }  

      // disconnect MQTT client
      $mqtt->close();      
    }    
  }
  
} catch (Exception $e) {
  error_log($e->getMessage());
}

// close database connection
$mysqli->close();

The application uses the phpMQTT library, a PHP class that lets you work with MQTT messages and brokers. Clone or download it to your PHP development environment, and place it in the same directory as the PHP application.

The PHP application code begins by loading this library and setting up various needed parameters in the $config array at the top. The parameters to be specified are:

  • The server name, which is usually named after your organization identifier under the 'messaging.internetofthings.ibmcloud.com' domain.
  • The server port, which is 1883 for unencrypted connections and 8883 for encrypted connections.
  • The client identifier, in the format a:ORG_ID:APP_ID, where the APP_ID is a user-supplied value.
  • The client API key and secret, which are needed to access the service.
  • The database host name, access credentials, and database name, which can be obtained from the ClearDB dashboard as shown earlier.
  • The number of minutes to wait for a device to become stationary (defaults to 5 minutes) and the geographical radius to check around the device's location for available offers (defaults to 1 km).

With the configuration out of the way, let's look at the application code in detail:

  1. This application is invoked every minute by the cloud rule that we set up in the previous step, and it will be passed a JSON packet that contains the raw data from the device, including its GPS coordinates. Therefore, the first order of business for the PHP application is to read and decode this JSON packet and assign the key pieces of information to PHP variables.
    <?php
    // ...
    // get JSON packet posted to webhook
    $json = file_get_contents('php://input');
    $data = json_decode($json);
    $deviceId = $data->deviceId;
    $message = json_decode($data->message);
    $longitude = round($message->d->longitude, 4);
    $latitude = round($message->d->latitude, 4);
    // ...
  2. Next, the application opens a connection to the MySQL database and checks the 'device_location' table to see if there is already a location record for the device that invoked it. If there is, it further checks if the location has changed since the last update and then either updates the location record (if the location has changed) or updates the wait time by adding 1 minute to it (if the device is stationary).

    Notice also that the latitude/longitude field values in the 'device_location' table are less granular than those values in the 'offer' table. This design is deliberately done to provide some error margin, as otherwise even a small shift of the device (for example, from one part of a store to another) would result in new location coordinates being generated and the wait timer being reset.

    <?php
    // ...
    // check if this device already has a location record
    $sql = "SELECT latitude, longitude, wait_time FROM device_location 
      WHERE device_id = '$deviceId' LIMIT 0,1";
    $result = $mysqli->query($sql);
    $row = $result->fetch_object();
    // check if the last location recorded in the database matches the current location
    // if yes, update the wait time
    // if no, update the location record
    if ($result->num_rows == 1 && $row->latitude == $latitude 
      && $row->longitude == $longitude) {
      $wait_time = $row->wait_time + 1;
      $sql = "UPDATE device_location SET wait_time = '$wait_time', updated = NOW() 
        WHERE device_id = '$deviceId'";
      $mysqli->query($sql);
    } else {
      $sql = "DELETE FROM device_location WHERE device_id = '$deviceId'";
      $mysqli->query($sql);
      $wait_time = 0;
      $sql = "INSERT INTO device_location (device_id, latitude, longitude, 
        wait_time, updated) VALUES ('$deviceId', '$latitude', '$longitude', 
        '$wait_time', NOW())";
      $mysqli->query($sql);
    }
    // ...
  3. If the device has been stationary for the configured interval, the next step is to look for available offers in the geographic neighborhood of the device. This design is accomplished by using the device's current location as the center, and "drawing" a circle around it to find matching offers within its radius. The calculation is somewhat complicated, but you can find the corresponding SQL query, together with links to a mathematical explanation and discussion of the formula, in the store locator example in the Google Maps documentation linked in the sidebar.

    Apart from finding offers in a specific radius, the SQL query in this PHP application also needs to check those offers against the 'device_offer' table to ensure that they haven't already been delivered to the device on the current day. This design is achieved by joining the 'offer' table with the 'device_offer' table and adding criteria to the SQL query to filter the result set and return only those offers within the configured geographic radius and which have not yet been published to the device on the current day. Here's the query:

    SELECT o.id, o.message, 
      (6371 * acos(cos(radians($latitude)) * cos(radians(latitude)) * 
        cos(radians(longitude) - radians($longitude)) + sin(radians($latitude)) * 
        sin(radians(latitude)))) AS distance
        FROM offer o
        LEFT JOIN device_offer dos ON o.id = dos.offer_id
      WHERE (dos.offer_delivery_date != DATE(NOW())
        OR dos.offer_delivery_date IS NULL)
      HAVING distance < $proximity
      ORDER BY distance
      LIMIT 0,10
  4. If the SQL query returns one or more matches, the application needs to publish these offers to the device by using MQTT. For this purpose, it initializes a new phpMQTT object and uses the object's connect() method to connect to the IBM Watson IoT Platform by using the configured API key and authentication token. The object's publish() method is then used to send the offer message to the device's iot-2/cmd/alert/fmt/json topic. Messages that are published on this command topic are automatically converted to alerts by the IoT Starter for Android application on the device.
    <?php
    // ...
    $mqtt = new phpMQTT($config['server'], $config['port'], $config['client_id']); 
    $mqtt->debug = false;
    
    // connect to broker
    if(!$mqtt->connect(true, null, $config['iotp_api_key'], 
      $config['iotp_api_secret'])){
      error_log('Failed to Could not connect to IoT cloud');
      exit();
    } 
    
    // iterate over offer list and publish to device
    // update database with offer delivery status
    while ($row = $result->fetch_object()) {
      $offerId = $row->id;
      $message = $row->message;
      $mqtt->publish('iot-2/type/Android/id/' . $deviceId . 
        '/cmd/alert/fmt/json', '{"d":{"text":"' . $message . '"}}', 1);      
      $sql = "INSERT INTO device_offer (device_id, offer_id, offer_delivery_date) 
        VALUES ('$deviceId', '$offerId', DATE(NOW()))";
      $mysqli->query($sql);
    }  
    
    // disconnect MQTT client
    $mqtt->close();   
    // ...
  5. After the message has been published to the device, the application also updates the 'device_offer' table so that the same offer is not resent to the user if the application is triggered again on the same day.

    Here's an example of an offer that is received by the IoT Starter for Android application from the PHP application:

    Figure 10. Example device notification
    Example offer as device notification
    Example offer as device notification
4

Deploy the PHP application to IBM Bluemix

The final step is to deploy the PHP application to IBM Bluemix.

  1. Create the application manifest. Be sure to use host and application names that match those names of your existing Bluemix application with the bound ClearDB Managed MySQL Database and Internet of Things Platform services.
    ---
    applications:
    - name: my-iot-app-[initials]
    memory: 256M
    instances: 1
    host: my-iot-app-[initials]
    buildpack: https://github.com/cloudfoundry/php-buildpack.git
    stack: cflinuxfs2
  2. You must also configure the buildpack to use the MySQLi extension for PHP. Create a .bp-config/php/php.ini.d/php.ini file in your application directory with the following content:
    extension=mysqli.so
  3. Now, go ahead and push the application to Bluemix as shown below:
    shell> cf api https://api.ng.bluemix.net
    shell> cf login
    shell> cf push

You should now be able to browse to the application at http://my-iot-app-[initials].mybluemix.net/webhook-offer.php. Since you're browsing to it, and not posting any JSON data to it, you should see an error message; however, this will still confirm that the URL is active and ready to receive POST data from the IBM Watson IoT Platform. In case you don't see anything, refer to the link in the sidebar for information about how to obtain a debug log.

Conclusion

As this article has demonstrated, the IBM Watson IoT Platform not only lets you receive data from connected devices, but also publish data back to them (and they can then use this data to perform calculations, display alerts or do other interesting things). But that's not all you can do. The IBM Watson IoT Platform also lets you configure trigger rules and actions for device data, making it possible to send alerts, trigger recipes, or request remote applications URLs. Combine these capabilities with the diverse range of services available in IBM Bluemix, and you'll see the immense scope available to build sophisticated and scalable IoT applications that integrate the best features of both Bluemix and Watson IoT platforms.

If you'd like to experiment with this application on IBM Bluemix and the IBM Watson IoT Platform, download the source code from GitHub, take a closer look at how it works by using the explanations provided in this article as reference. Then, modify the configuration to match your environment and deploy it in combination with an Android device to see how it works. Have fun!


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Internet of Things, Open source, Web development, Mobile development
ArticleID=1047701
ArticleTitle=Building location-aware IoT apps, Part 2: Build a PHP app that delivers geo-targeted marketing messages to an IoT device
publish-date=07172017