Device Information in the Database

What device information do you want or need stored for each node/sensor device.

Currently, since monitAir is the only project.

DeviceID (this also gives project, mobile/static device information)
Approx location.
Description of device.
Description of software.
List of sensors.

I proposed before
exact location
height from ground
Pictures of the setup, design and in place.
Designer/builder/creator/supplier
Some of these have privacy issues

My reason for some of these is for Scientific Research.
Knowing what you are looking at, the design and location of the device all alter how you might use the information.

For example, mines open from all sides, away from building the BME is shaded from the sun by two levels of shielding.
Compared to another that is in a path width area on a fence in a nice open Stevenson style screen.
Or others, bolted to a wall on a house, perhaps in full sunlight or in constant shade
Some might be lower to the ground, some at people chest height/pram height others 2 meter off the ground or higher.

When it comes to modelling, all this matters… Propably.

Knowing the device is in the back garden, near a bus stop, on a lamp post may be important.

This isn’t for me, but for future analysts of the open data.
Some may need to be kept closed data, with access on request for privacy reasons. Allowence agreed by the owner?

Just some thoughts to think about as we evolve and people start to use the data.

Oh and creator/supplier so we know who to call or they can be listed together if that what someone want to look at.
E.g. Adam versions, connexion, zephyr (whoever has them) etc.

What would you like to see or know about?

(This may be edited to make it make sense)

Hey! Interesting points.

Currently we have device information split across 2 tables: devices and device_types. They have these structures:

CREATE TABLE `devices` (
  `device_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_name` varchar(16) NOT NULL,
  `device_type` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `device_latitude` double DEFAULT '53.725383',
  `device_longitude` double DEFAULT '-0.336571',
  `device_altitude` double DEFAULT NULL,
  `lat_lon` geometry AS (Point(device_latitude,device_longitude)) PERSISTENT,
  PRIMARY KEY (`device_id`),
  UNIQUE KEY `device_name_idx` (`device_name`),
  KEY `owner_fk` (`owner_id`),
  KEY `device_type_fk` (`device_type`),
  KEY `lat_lon_idx` (`lat_lon`(25)),
  CONSTRAINT `device_type_fk` FOREIGN KEY (`device_type`) REFERENCES `device_types` (`device_type`),
  CONSTRAINT `owner_fk` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`owner_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4
CREATE TABLE `device_types` (
  `device_type` int(11) NOT NULL AUTO_INCREMENT,
  `processor` text,
  `Connection` varchar(8) DEFAULT NULL,
  `particle_sensor` text,
  `temp_sensor` text,
  `power` text,
  `Software` text,
  `Other` text,
  PRIMARY KEY (`device_type`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4

In terms of the location, we store it as 2 floats - latitude and longitude. The resolution there is enough that we should be able to represent it’s location is sub-metre accuracy - so I’m not really sure what you mean about the exact location.

I think it’s probable that we’ll be able to tell how close a sensor is to the road using the currently stored location.

With the machine-readable data behind OpenStreetMaps, it should be possible to dynamically calculate the distance to the nearest road for each sensor. A PR that adds this information to the web interface would definitely be appreciated.

List of sensors.

We don’t actually store this. The HTTP API infers this implicitly via a rather interesting SQL query based on the readings stored in the database.

Pictures of the setup, design and in place.

This should not be stored in the database. Pictures should be stored in the file system, with a reference to it. As for why we’d need that information, I’m not entirely sure. You mention scientific research, but how useful is a picture, other than to see how a device is setup? I’m not sure you can do much analysis at scale with a picture.

Designer/builder/creator/supplier

We do store owner information. This includes emails, postcodes, email addresses, and telephone numbers. Obviously this information is sensitive, so it is not displayed in the web interface.

I do agree however that the structure of the devices and device_types tables could potentially be improved. Perhaps we could make use of some of the JSON functions that MariaDB offers and store an array of strings (["a", "b", "c", ....]) showing the part information?

Perhaps adding a housing column would be useful to contain information about where the sensor is located. For example stevenson_screen, box_mounted_on_wall, or box_mounted_on_lamp_post?

This is now changing. The database now has additional tables to allow a device to have any number of sensors. This is currently in beta test.

I though I replied to this but hey ho.

Pictures as it’s great for children and interested people having a nosey around.
For Scientific data, who knows but the more info the better.
Also someone may see a problem with the setup, to close to a boiler or extractor vent. The Enclosure etc.
It just info people could provide.

Location.
We have two locations.
The provided location that is stored and plotted on AQ map.
This is a supplied location, at the moment it is in the middle of the nearest street etc.
A precise location would be exactly where it is, hidden from normal view unless allowed.

We do need to move to a more automated setup, luftendata requires no admin input.

Nice to see the change to automate sensors.