The challenge

Did you know that Coca Cola ship more than 100,000,000,000 bottles a year? Like most vendors, they use pallets - plastic platforms which protect them in-transit. Pallets themselves are a valuable, reusable asset and a large pallet provider might have hundreds of millions of pallets in circulation. Unfortunately pallets build-up at some sites while other sites run short, which is bad for:

• the pallet provider, who would prefer to “sweat” their assets 24/7
• the vendor, who suffers significant opportunity-cost if they can’t ship goods because they don’t have enough pallets
• The customer, who is inconvenienced if their warehouse is clogged-up with pallets.

So increasingly the operations teams of pallet companies are putting geo-trackers on (at least a statistical proportion of) their pallets and from these intend to derive key metrics to run their business better. So like almost every other IoT use-case:

• this is about making more efficient use of something
• the pallet company isn’t selling things, they’re selling a service i.e. “the availability of a pallet in the right place at the right time”. So they need to actually deliver that service to a high quality, and might even need to prove that they are doing so to their large customers.

The key business metrics which show how well the service is being delivered include:

• How much of the time the average pallet is in-use (ideally 100%)
• How many pallets have not moved for 7 days (ideally 0%)
• Which distribution centres have too many pallets (ideally 0%)

The data

The geo-tracker on each pallet reports location as a pair of (longitude, latitude) properties once per hour. Here we see the positions that one pallet reported as it moved around the supply chain (on Roman roads apparently):

The solution

First we need to detect when a pallet is stationary. Some tracking devices might emit a separate “moving” property, but if they don't, then we’ll have to work it out from the data. The positions reported could have some noise on them, so the coordinates may change slightly even when the pallet is stationary.  So the first thing to do is use DevicePilot Calculated Properties to turn changes in (lat,long) position into metres travelled. We can then sum distance moved over a time period, and then use relational queries to answer some questions about the pallets:

• sort and threshold this to get a list of “pallets which haven’t moved in the last week”
• show which pallets are used the most, and least, during that time, and average utilisation percentage

Next, the question is where these stationary pallets are, for which we need to do a geo-lookup. If we have a list of known sites (e.g. warehouses) then we can use Pythagoras to find the distance from each pallet to each site, sort by distance and pick the lowest one. There are some tips on how to do this in SQL here. We could even threshold to spot pallets which have been stationary for a long time somewhere far from any known site (maybe they’re in a ditch, or at “Dodgy Dave’s Pallets Emporium, no questions asked”).

Now we have identified the site, we can do an analysis by site, e.g.

• which sites have the most pallets, which the least
• min, max and average stocking levels at each site
• rate a which pallets tend to arrive at and leave from a site 

We can also start to look at site sequence. Perhaps each site has a function, e.g. one of:

• vendor where for loading
• warehouse for storage
• customer for unloading
• pallet cleaning service

and we’re interested in identifying pallets which have not followed the sequence correctly (e.g. are being re-used before being cleaned).

Conclusion

Combining streaming and relational queries can turn raw geolocation data into meaningful insights for a large estate of mobile assets. Next, read about using it to optimise stock management for vending machines.