Database overview

How Pi Gazing stores observations

Pi Gazing stores its observations in a MySQL database. You don't need to understand the details of how this database works in order to use the software. However, you will almost certainly need to understand how the database works in order to make changes to the Pi Gazing code.

When you set up the Pi Gazing software, you create a MySQL database and user account. By default, the database is called pigazing. The username and password for the user account that the software uses to access this database are both pigazing. So long as you are the only user of your computer, there is little security risk in having such an obvious password, because the user account is only accessible to local users on your computer.

What the database contains

Within this database, various kinds of entity are stored:

• Observatories
• Observations
• Files
• Metadata

An observatory is a single Pi Gazing camera. If several cameras are set up in the same location, these are different observatories and should be configured with different IDs.

An observation is a group of files, recorded at some particular time, which belong together. They might be various different versions of a still photograph, or files relating to a sighting of a moving object. All data recorded by a Pi Gazing camera is part of an observation, even if that observation only contains one file.

A file is exactly that. It might be a image file, a video file, a text file, or anything else. When files are inserted into the database, they get moved into a storage directory. By default, this is datadir/db_filestore within your pi-gazing installation directory. The filename is changed to something like 20150731_201629_b741391fffcc28d1. The first part is a time stamp, and the second part is a unique hex key.

A metadata item is an arbitrary piece of information which can be associated with observatories, observations or files. It can have either a numerical or string value. It might be, for example, the latitude of an observatory, the duration over which a moving object was seen, or an estimate of the sky clarity in a still photograph. A list of the standard fields currently in use in the Pi Gazing code can be found here.

Semantic types

Both observation and file records have semantic types. This is a string which defines what type of measurement is contained in the observation or file. For example, is it a still photograph or a moving object?

A list of the semantic types currently in use in the Pi Gazing code can be found here.

Browsing the Pi Gazing database

The easiest way to browse the data your Pi Gazing installation has recorded is to set up the web interface which you can find in src/observatory_website. This is set up as part of the installation instructions.

You may hit an issue that any Raspberry Pi which is being used for observing doesn't have many spare CPU cycles left for serving web pages. Indeed, if you make heavy use of the web interface while the Raspberry Pi is observing, you may ruin any observations that it is making, since the CPU won't be able to keep up with the incoming video stream, and it will miss frames.

For this reason, you're likely to want to migrate your observations onto a separate server during the daytime, where you can browse them at your leisure. The other advantage to this approach is that if you have multiple cameras, you can set them all to export to a common server, and you can view all their observations in one common place.

This is very easy to set up. When you configure the Pi Gazing software, one setting in your configuration/installation_settings.conf file is the URL of an external server to export observations to. Providing this server is running the Pi Gazing web interface, and has a user account set up with the role import enabled, your camera will upload all its observations to this server during the daytime.

Details of how to set this up can be found here.

Database identifiers

If you delve deeper into our MySQL schema, you will notice that most tables have two identification fields. Usually there is a numerical identifier for each entity, but also a publicId field, which contains strings of the form 20150731_201629_b741391fffcc28d1. In the case of files, this string ID is called repositoryFname.

The reason for this is that we need to be able to uniquely identify files and observations across the Pi Gazing network.

Suppose you have two cameras, A and B, which upload observations to your desktop PC (machine C). This in turn is set to upload observations to the official Pi Gazing repository (machine D). Then you upgrade your PC to a new computer (machine E).

Of course, cameras A and B will need to upload all their observations to your new computer, which won't have seen any of them before. But your new computer should not upload them to the Pi Gazing repository, lest it end up with two copies of your observations. To avoid this, we identify observations by their string IDs. Although they're randomly generated, it's hoped that they're long enough to be globally unique.

The numerical identifiers for each entity are local to your computer. They're a quick and easy way of referencing records, but the same observation will have a different numerical ID on every computer that stores it.