Build a Cow Counter dashboard Using VDP, Postgres and Metabase

In this tutorial, we showcase how to build an object detection ETL pipeline using VDP and create a Cow Counter dashboard with Metabase to provide farm cattle insights.

Herd cattle with drones?

In the recent documentary series Clarkson’s Farm (2021), Jeremy Clarkson took us on his journey and showed us how he herded sheep with a barking drone successfully — or maybe not 🤣 .

Joking aside, apparently sheep can follow drone pretty well with the right training and using drones emitting certain sounds was able to reduce the stress on the sheep.

Over the last few years, drone-based smart agriculture solutions have become increasingly popular due to numerous benefits, including deeper insights into farming assets and data-driven decision-making process for better efficiency and reduced operational cost.

To explain the solutions on a high level, it usually includes the following processes:

• Deploy drones and sensors to collect livestock data
• Use AI technology to process and analyse the data for locating, counting or monitoring cattle
• Provide easy-to-understand insights and takeaways to enable ranchers to make data-driven decisions or take immediate actions when issues arise

To showcase the above processes, read on or watch our video to see how to build an object detection ETL pipeline using VDP to analyse a drone video of a cattle farm, and create a Cow Counter dashboard with Metabase that provides insights about cattle in the video footage.

⚠️ Where there are discrepancies between tutorial and video, please follow the tutorial.

Prerequisites

• Docker and Docker compose
• Python 3.8+ with an environment-management tool such Conda

Build an object detection ETL data pipeline

In the Build a shareable object detection application with VDP and Streamlit tutorial, we built a SYNC object detection pipeline that responds to HTTP requests synchronously with VDP. This time, we are going to use the same model YOLOv7 to build an object detection pipeline but in ASYNC mode. Unlike a SYNC pipeline, this pipeline

• accepts HTTP requests with image payload,
• transforms the images in the payload into structured insights, and
• sends the structured insights into a PostgreSQL database.

⭐️ VDP on GitHub

Run VDP locally

$ git clone https://github.com/instill-ai/vdp.git && cd vdp
$ make all

Once all the services are up, go to the Console at http://localhost:3000.

Build an ASYNC data pipeline with YOLOv7

After onboarding, you will be redirected to the Pipeline page. Please follow the steps of Build an ASYNC detection pipeline.

1. add a HTTP source,
2. import a model from GitHub repository instill-ai/model-yolov7 with ID yolov7,
3. deploy a model instance v1.0-cpu of the imported model,
4. add a PostgreSQL destination, with ID postgres-db,
5. set up a pipeline with ID detection.

Build an ASYNC object detection pipeline with YOLOv7 via no-code Console

Alternatively, you could refer to the low-code example to build this pipeline programmatically via REST API.

Now you should see the pipeline detection in the Console.

Pipeline page on the VDP Console

Trigger the pipeline to analyse a drone video

Create a virtual environment and install dependencies

We’ll use Conda as the package management system like we did it in the last tutorial.

First, create and activate an environment named vdp-dashboard with Python 3.8:

$ conda create --name vdp-streamlit python=3.8 
$ conda activate vdp-dashboard

Then, go to /examples/async/yolov7 directory of the VDP project to install all dependencies.

$ cd examples/async/yolov7 
$ pip install -r requirements.txt

Trigger the pipeline

The directory of the project should look like the following

├── Dockerfile-metabase-m1
├── README.md
├── main.py
├── requirements.txt
└── utils.py

To run the demo for processing the video, we simply do

$ python main.py

You could skip to the next section to create the dashboard. But if you’re interested, here we dive into the demo details.

First, the script will download a 1-minute drone video of a cattle farm cows_dornick.mp4 from a public Google storage bucket.

ℹ️ The video used in the demo is sampled from a public video cows dornick.mp4 from here.

After finishing downloading the video, extract_frames_from_video function will extract frames from the video into a folder inputs at 30 frames per second.

Then, we trigger the pipeline detection to analyse the video by sending HTTP requests to process all frames in the input folder.

Each triggering pipeline operation will send the analysed result to the configured PostgreSQL database and return a list of unique indexes corresponding to all processed image frames in the payload.

It may take a while to analyse the full video. Meanwhile, we can connect to the Postgres database to check whether it is working as expected. You should see the tutorial database is already populated with some data 🚀.

In the next section, we will setup Metabase to connect to the data in the destination PostgreSQL database and build a dashboard based on the analysis results.

Create a Cow Counter dashboard

Metabase is an open-source business intelligence (BI) tool that helps you uncover the data insights like an analyst. Just connect to your database, you can dig deeper into your data, easily build interactive dashboards and share them with stakeholders.

Run Metabase locally

In this tutorial, we run Metabase on Docker. If you prefer, check out alternative ways to install Metabase.

$ docker pull metabase/metabase:latest 
$ docker run -d -p 3100:3000 --name metabase metabase/metabase

Note: If the official Metabase docker image doesn’t work on M1 (apple silicon) mac, try to build an image with the Dockerfile-metabase-m1 file.

$ docker build -f Dockerfile-metabase-m1 -t metabase/metabase-m1 . 
$ docker run -d -p 3100:3000 --name metabase metabase/metabase-m1

Once the Metabase startup completes, you can access it at http://localhost:3100.

Create the dashboard

During onboarding, add your PostgreSQL database.

Add your PostgreSQL database during Metabase onboarding

Then, click Browse data on the left sidebar, choose vdp-postgres ➝ Airbyte Raw Vdp:

Browse data in the PostgreSQL database

As you can see, the pipeline outputs are stored in the Airbyte Data ➝ Detection ➝ Objects field with JSON blob format.

Convert raw detections into multiple records

To flatten the raw detections (JSON blob) into multiple records for further analysis, Click +New on the top right corner, choose SQL query ➝ Select a database vdp-postgres, then copy and paste the following SQL query and press Run query:

Convert raw detections in the database into multiple records for future analysis

The transformed data counts every time a cow appeared in the video footage.

Visualise the data

Click on Visualization to open the visualization sidebar and choose Area. For Data tab, select the fields processed_at and category for X-axis, and select the field Count for Y-axis. You can customise the area chart as you want. Here we just enabled Show values on data points in the Display tab.

ℹ️ Metabase has a hard limitation 2,000 to the number of rows displayed for a table. Therefore, the dashboard won’t show all the detections.

To make the dashboard accessible, you can save this question into the Our analytics collection by clicking Save on the top right and give it a name cow counter.

Visualise video analysis results on Metabase

🎉 Congrats! You’ve built a simple yet intuitive dashboard to count cows in a drone video footage. To validate the dashboard, the demo script main.py also generates a video output.mp4 with detections drawn on frames by the end of the demo.

We use YOLOv7 in the ETL pipeline, since it is the state-of-the-art object detector that surpasses all known ones in both speed and accuracy. The above video showed that it worked fairly well considering it was only trained on the public MS COCO dataset without any fine-tuning.

Limitation

However, if you want to improve the performance, as you can spot that some cows were wrongly detected as “dogs” on a few frames, it is highly recommended to fine-tune the model on labelled drone data collected from the same domain.

Cows were wrong detected as dogs on some frames. This can be improved by fine-tuning the model on more drone data from the same domain.

We are working closely with our early users to deploy customised models for their use cases. If you’re interested, please fill out the form to tell us about yourself and your project, we will be in touch 👐.

Conclusion

In this tutorial, you’ve built an ASYNC object detection pipeline using VDP to process a video and sent the analysis result to a Postgres database. Also by using Metabase, you’ve turned the analysis result into an intuitive and interactive dashboard that you can share with stakeholders.

If you enjoyed VDP, we’re building a fully managed service for VDP — Instill Cloud (Alpha):

• Painless setup
• Maintenance-free infrastructure
• Start for free, pay as you grow

🚀 Join the Instill Cloud waitlist

We also invite you to join our Discord community to share your use cases and showcase your work with Data/AI practitioners.