Skip to content

Demo: a real time IoT monitoring system for cold transit trucks

Build a complex data engineering pipeline with a few simple SQL queries

Interrupted or incorrect temperature control during transit is the cause of over one-third of the world’s food spoilage. This represents billions of dollars in losses every year. Maintaining the correct temperature in transit is THE challenge in cold chain logistics.

This demo sets up a monitoring system for cold transit trucks using Kinetica. There are two streaming data inputs of interest – GPS location and refrigeration metrics. The key challenge is that we need to combine the two streaming inputs (location and metrics) that are recorded using sensors that transmit the information at different timestamps. We solve this by performing an inexact ASOF join that is kept updated in real time using a materialized view.

CREATE OR REPLACE MATERIALIZED VIEW transit_trucks.vehicle_analytics 
REFRESH EVERY 5 SECONDS AS 
SELECT
   vl.TRACKID AS vehicle_id,
   DATETIME(vm.ts) as DATETIME,
   vm.pressure 
FROM
   transit_trucks.vehicle_locations vl 
   INNER JOIN
      transit_trucks.vehicle_metrics vm 
      ON vl.TRACKID = vm.id 
      AND vl.TIMESTAMP = vm.ts

With just a single SQL statement we get an always on up to date view of cold storage metrics for different transit trucks. This view is then plugged into downstream alerting and decisioning systems.

The template from this demo can be used for any real time monitoring system that requires you to combine streaming inputs that are recorded at different timestamps.

You can watch the demo video below.

Try it yourself

Deploy Kinetica

You will need an instance of Kinetica to run this demo. There are three options (in order of preference):

  1. Kinetica on the cloud: The cloud version of Kinetica comes with an interactive notebook environment called Workbench. This is the preferred mode for deploying this demo. 
  2. Free developer edition: The developer edition of Kinetica is a containerized application that can be run on your personal computer. Developer edition does not currently include Workbench. 
  3. On-premise version of Kinetica: Please reach out to support@kinetica.com for a license. 

Access demo files

You can access the relevant material for the demo from our examples repo here

Workbook files can only be loaded if your version supports workbench. So if you are on a cloud instance of Kinetica, you can download the workbook JSON file and import it.

If you are on a version of Kinetica that does not support workbench, you can execute queries in the included SQL file using the query interface on GAdmin (the database administration application). 

Contact Us

We’re a global team, and you can reach us on Slack with your questions and we will get back to you immediately.

Get notified when we publish new posts:

White Paper

Build Real-time Location Applications on Massive Datasets

Vectorization opens the door for fast analysis of large geospatial datasets
Download the white paper