The Internet of Things (IoT) has taken off and we now see it everywhere. Open Source software packages have been around for many years but recently we have also seen Open Source hardware take off. Now I find myself playing with hardware again after too many years of doing just software development. Open Source hardware has made it so much easier and inexpensive to build electronic gadgets and especially IoT devices. It also comes with a big fun factor.
In this blog, I am going to discuss my first project that was not out of some Arduino or Raspberry PI project cookbook. Here we are going to combine some Open Source hardware along with the Informix database server to collect and analyze GPS data from a vehicle using a homemade IoT gateway.
My principal focus in the Informix development organization continues to be the enhancement of our Spatial and TimeSeries capability. Recently we introduced a feature called the Spatiotemporal Search for Moving Objects, or STS for short. GPS data fits well into a Timeseries since it inherently has a timestamp, carries the global position and typically several other measurements such as altitude, speed, and direction of travel. This data can be easily combined with sensor data from the environment or the vehicle itself. With STS, this information is stored in a TimeSeries with all the current Informix TimeSeries analytics capabilities. The real power of STS is that it processes this GPS data into subtracks (or spatial line strings) when the vehicle is moving, and creates stationary states with a position when it is not moving. In addition, the STS engine will tell you the time periods when there was no data (for example, when the vehicle is parked overnight or when there is a loss of signal going through an underground tunnel). Originally, we aimed this at moving fleets of vehicles such as buses or taxis. But we have tested it with data from boats and even downloaded data from a bicycle tracking system. We have also added support for sub-second GPS data generated by commercial planes. I have done several talks drilling down on STS functionality including Session C10 "Sensors on the Move: Tracking and Analysis of Moving Objects in IoT" at IIUG 2017 (http://iiug2017.org). The STS User Guide is found at https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sts.doc/.
For this project, I am using a Raspberry Pi 2 (https://www.raspberrypi.org/) with Raspbian Jessie OS and the Ultimate GPS Breakout board from Adafruit
(https://www.adafruit.com/product/746) with an external antenna (https://www.adafruit.com/product/960). For a power supply, I used a USB Battery that I picked up at a trade show (but I could have easily used a vehicle base cell phone USB charger.
I used the open source GPSd package (http://www.catb.org/gpsd/) to manage the GPS and get the position data in the form of a JSON document containing a wealth of information:
The Informix server, which includes STS, is found here:
The Informix 12.10 Developer Edition for ARM can be downloaded from the IBM trials and downloads site under "No-charge products, tools, and toolkits"
The GPS board draws power from the GPIO pins and it communicates with the Raspberry Pi via the UART GPIO pins. The GPS breakout board does contain an on-board small antenna but I found the external antenna provides a strong lock on the GPS satellites.
A pleasant surprise with the GPS board is that it supports a battery powered clock. After I soldered a battery holder for a 3v CR1220 Lithium Cell and inserted the battery, and after it gets the first GPS time from the satellites, the GPS board always has an accurate time even before locking onto the satellites. With a simple change to the NTP configuration in Raspbian, the Raspberry Pi will always have the time set even if it is not connected to the internet.
The wiring was very straight forward:
Here we see the Ultimate GPS breakout board wired to the GPIO header:
Once we have all the hardware assembled, I booted the Raspberry Pi and configured Raspbian Jessie. Then I installed the GPSd package and followed the instructions to test and verify that the GPS board was working and locked onto the satellites. I wrote a script to using the GPSd command to capture the data in to text file. Once I was satisfied this worked, I turned to Informix.
I installed and used the default Informix server right out of the "box". After creating a database, I used the following commands to create my gps timeseries table:
GPS Timeseries Table
I wrote some code to take the JSON output from the GPSd client to transform it into an unload file suitable for the TimeSeries Loader (TSL). I also had to do the following edits to the input stream:
1) Along with the GPS positions, there are several informational JSON documents; for example, updates on the what and where the satellites are in the sky. I purged these JSON values so they were not loaded into the TimeSeries.
2) Before the GPS has locked into enough satellites (or if it loses the connection), it will still produce position rows without a position. I also purged these JSON values.
3) The time strings in the JSON value are in ISO8601 format. I did an inline replacement to the Informix DATETIME YEAR TO SECOND format.
4) This GPS produces a position every second. I filtered out 9 out of 10 positional rows to only capture a position every 10 seconds. This was an arbitrary decision on my part. (STS has been tested with 1 position per second and 1 position every 1/10 of a second).
I used the following SQL to generate the subtracks.
SQL to Generate Subtracks
The STS engine takes several parameters in the form of a JSON document. In the above SQL statement, the parameters are telling STS to expect a GPS position every 10 seconds, the maximum duration of a subtrack of a vehicle is 1800 seconds (or 30 minutes), and the maximum area of the bounding box of the subtrack is no larger than 128000 square meters. Finally, the "position" field tells STS in which fields the longitude and latitude are found.
For my initial implementation, the script would use TSL to load the TimeSeries data and build STS subtrack information. STS also supports periodic subtrack building using the DB Scheduler.
Once everything was setup and running smoothly. I shut it down, unplugged the HDMI monitor cable and wireless keyboard/mouse and took it out to my vehicle for a road test in "headless" mode. I kept it in the vehicle for a couple of days capturing data with great success. Since this data is stored in the Informix database, with Enterprise Replication, each gateway device’s GPS data can be replicated to an enterprise server for deeper analysis.
This is a work in progress. I am using the experience and lessons learned to drive future enhancements and make it easier to deal with data coming from the GPS. Some of the data collected from this exercise has now become part of our testing and my talks on STS. I plan to take this further by collecting sensor data with the GPS position. I have received a small 3.5" touch screen to enable displaying the GPS status, track information and allow queries.
Connect with me on Twitter