CHAPTER 9 ■ IOT PATTERNS: LOCATION AWARE
Database Table (MySQL)
As discussed in the previous two chapters, before you can send HTTP requests from
Arduino, you need to build a service that will receive the data.
The livestock tracking system will be displaying the latest GPS coordinates on a map,
so you need to create a database table that will store those GPS coordinates.
This chapter also uses MySQL as the database. Even though you are only going to
track a single device, the table structure will be the same as if you were tracking multiple
devices. So create a new table called GPS_TRACKER_DATA using the SQL script provided in
Listing 9-1. Run this script in an existing database or create a new one.
The first column will store the ID of the animal/device sending the coordinates;
the second column will store the latitude; the third column will store longitude; and the
fourth column will contain an auto-generated timestamp.
Listing 9-1. Create Table SQL
CREATE TABLE `GPS_TRACKER_DATA`
(
`CLIENT_ID` varchar(40) NOT NULL,
`LATITUDE` varchar(40) NOT NULL,
`LONGITUDE` varchar(40) NOT NULL,
`LAST_UPDATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`CLIENT_ID`)
)
F i g u r e 9-5 shows the structure of the GPS_TRACKER_DATA table.
Code (PHP)
Now that the database table is ready, you need to build two services. The first service that
will receive the GPS coordinates and store them in the newly created database table. The
second service will show the stored GPS coordinates on a map.
Figure 9-5. GPS_TRACKER_DATA table structure
