Blog post banner

Realtime location tracking with Redis Pub/Sub

March 19, 2018

If you use Redis in your company, you know there’s valuable information flowing through it. It’s can be really useful to have that data easily available in Excel.

In this post you’ll learn how using Gridarrow allows you to:

  • connect to a Redis server with the Python Redis library
  • query or subscribe to whatever data you need
  • stream it instantly to connected Excel users

What is Gridarrow?

Gridarrow is the missing server for Excel. It connects just about any data source to Excel with simple Python scripts.
Why is it awesome?
  • It's real-time, so as soon as the data is updated in the Gridarrow App it's updated in Excel, no need to poll or refresh.
  • It's multi-user so your colleagues can connect to the same data at the same time.
  • It simplifies your spreadsheets by moving data retrieval and pre-processing into Python.
Request Demo

What is Redis?

Redis is one of the most useful components of the modern tech stack. Its unique in-memory data structure handling capabilities have allowed it to serve a huge range of use cases and industries. Redis is most often used for caching, queueing and Pub/Sub, but it also has modules for machine-learning, geospatial calculations and analytics.

Let’s see where those London trucks go

Below is a simple example where we’ve connected to a Redis Pub/Sub channel carrying logistics data from a fleet of delivery drivers in London. Here’s how it works:

  • The vehicles are publishing their location coordinates, delivery statuses and fuel to the vehicles Pub/Sub channel.
  • Gridarrow App subscribes to the channel and receives a JSON message for each update.
  • The message is deserialized and assigned to the vehicles_grid.

This is all that’s required to send the data to Excel!

Once the data is in Excel, everything is possible

For example, here’s a real time map of the vehicle’s locations.

Excel spreadsheet with a map of London with vehicles location updated in real time

To create this map, we’ve overlaid a scatterplot over a static image exported from Google Maps. We’ll write more about this technique in a later blog post.

Only 30 lines of code!

Here’s the full code listing that we used to connect to the Redis Pub/Sub channel and stream the data to Excel.

But before you jump into it, remember to:

 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738
import json
import os
from datetime import datetime

import redis

from gridarrow import Arrow

arrow = Arrow()

pubsub = redis.Redis(host=os.environ['REDIS_HOST']).pubsub()

vehicles_grid = arrow.grid("vehicles",
                           columns=['update_time', 'vehicle_id', 'latitude', 'longitude',
                                    'total_deliveries', 'remaining_deliveries',
                                    'speed', 'status', 'fuel'])

def start(_):

    next_row = 0
    row_ids = dict()

    for item in pubsub.listen():
        if item['type'] == 'message' and item['channel'] == b'vehicles':
            data = json.loads(item['data'].decode("utf-8"))
            data['update_time'] = datetime.fromtimestamp(data['update_time'])

            if data['vehicle_id'] not in row_ids:
                row_ids[data['vehicle_id']] = next_row
                next_row += 1

            row_id = row_ids[data['vehicle_id']]
            vehicles_grid[row_id, vehicles_grid.columns] = \
                [data[c] for c in vehicles_grid.columns]