FlightAware Discussions

Piaware + VRS log

Hi, I hope someone can help me with this. For the last week I have set up Piaware on my Raspberry Pi 4 and I am succesfully feeding to Flightaware and FR24. I have no experience with feeding and Linux but through excellent tutorials and different forums including this one I have managed to come this far. I wish to have a daily log that can be saved for all the aircraft that are picked up by my antenna, including date/time, flight number/callsign, aircraft type and registration or hex code. I understand that there are several different ways of doing this but per now the best one is to use the daily log function on Virtual Radar Server. So I have successfully installed VRS, and added the Web Admin Plugin and the Database Writer Plugin. I have downloaded and installeed MySQL. The tutorial I am using on “Pimylifeup.com” is saying that I need to install PHPadmin and a web server like Apache to run MySQL on a GUI. But on forums I read that another web server like Apache probably will seriously interfere with the Piaware feed, so I dare not go any further with this.

So my questions are:

  1. Is it true that setting up for example Apache and PHPadmin will affect the Piaware feed?
  2. Can I create a successful daily log with SQL without a web server?
  3. Any suggestions on how to move forward?
  1. Don’t know if it will affect the feed itself, but it would affect the local access to the Skyaware-page unless you know how to install Apache to listen on a different port than 80.
  2. Yes, it can be done. However, it may severely limit the life of the SD-card. Running something that constantly writes on the SD-card will have a negative effect on it’s lifespan.
  3. I would get another RPi4, boot it from an external disk via USB (SSD or an ordinary harddrive), install VRS on it and the SQL server as well. No problem getting a feed to this new RPi4 from your current RPi4. But it’s not cheap and it can be done with some old hardware (PC) that you may have.

Check this project. It collects information from dump1090 and stores it in a MySQL DB
The same guy has a light-weight solution with CSV files

1 Like

Thanks for your help, bive! I will consider your advice about getting an SSD or harddrive.

1 Like

Yeah, I took a look at that solution. But after reading through the comments section it seemed like it was not compatible with Piaware 5.0, so I didn’t really go for that one. But I guess it can work with some alterations.

I didn’t read before about that. Wondering what about that compatibility as the script only scans the json output. Maybe a little tweaking fixes it.

But i am not a good coder

If you have a Windows machine on your network you could rebroadcast your Pi VRS to a Windows machine running a VRS session and do the logging there, on more robust Windows disk/SSD.

I have VRS on my Pi setup to rebroadcast the merged feed to a Windows VRS session. On native Windows, VRS peak CPU usage is 0.1% with about 50mb of RAM used. VRS really prefers Windows.

I never access the VRS web pages on the Pi itself, but I occasionally check the Windows VRS session to see the range plot (which is the only reason I use VRS). This was especially cool when I had my second/backup/test station also broadcasting to the Windows VRS since I could compare and overlay range plots.

It’s easy to feed one VRS session from multiple others. And you can do cool things like combine multiple Pi feeds into merged feeds etc.

From my very old Bookmarks:
https://forum.virtualradarserver.co.uk/viewtopic.php?t=1802

And this one for basic VRS setup:

1 Like

Hi. Yes, I finally figured out how to create a log in VRS, so now I just use copy/paste on my Windows PC and save it in Excel manually. Of course it would have been better if it was logged automatically but for now I am quite pleased with the solution.

A log was my main reason for installing VRS but I am sure there are other cool features that can be explored. I will check out the feature you mention. Thanks!

1 Like

The VRS is using a SQLite Database format. If you know about the structure and some Python skills, you can export the data from there via script.

I see. I got to educate myself a bit first then…

Enjoy it. I am playing around with that from time to time, but for your specific purpose i cannot help you because my skills are pretty low

I use the same instructions to install VRS.

You can produce a CSV file of flight information from the VRS database with a single command. You will just need to install sqlite3 for this to work. Note that you only need VRS and sqlite3 installed. You do not need MySQL, Apache, or phpMyAdmin.

Install sqlite3:

sudo apt update
sudo apt install sqlite3

Create the following Bash script file to hold a few convenient variables to be used with the sqlite3 command. Make sure to edit the SQBFILE variable so it has the actual path to your BaseStation.sqb database file.

#!/bin/bash
SQBFILE="/your/path/to/BaseStation.sqb"  # Edit to use the correct path to your BaseStation.sqb database file produced by VRS.
#DATESTRING=$(date --date="yesterday" +"%Y-%m-%d")  # Gives yesterday's date (YYYY-MM-DD).
DATESTRING=$(date --date="today" +"%Y-%m-%d")       # Gives today's date (YYYY-MM-DD).
SQLTEXT="SELECT \
Flights.StartTime, \
Flights.EndTime, \
Flights.Callsign, \
Aircraft.Type, \
Aircraft.Registration, \
Aircraft.ModeS, \
Aircraft.ICAOTypeCode, \
Aircraft.RegisteredOwners \
FROM Flights \
INNER JOIN Aircraft ON Aircraft.AircraftID=Flights.AircraftID \
WHERE Flights.StartTime LIKE '${DATESTRING}%' \
ORDER BY Flights.StartTime;"
sqlite3 -header -csv "$SQBFILE" "$SQLTEXT" > vrslog_${DATESTRING}.csv  # Example output filename: vrslog_2021-05-16.csv

This Bash script will produce a date-stamped CSV file with the flight information you requested. (I also added “ICAOTypeCode” and “RegisteredOwners” information in case you found those interesting as well.) The information in the CSV file will be identical to that provided by a VRS report. As it is right now, you will get the flights from the current day. If you prefer, there is an option in the script to get all the flights from the previous day by using the alternate DATESTRING variable that is currently commented out.

One possible idea is to set up a cron job with this Bash script to run at every 2:00AM (just need to pick a time well after midnight) to produce a CSV log file of the previous day’s flights. You can store all these log files in a directory of your choice. Your system will now automatically log and archive all the daily flights for you on a regular basis. You can still have a separate Bash script to create the continually updating CSV log of today’s flights.

1 Like

If you include the Routes view from the SQB file, you are getting the complete information as shown in the VRS internal report

1 Like