New `State` fields for airborne platforms

[IanMayo]

Analysis of airborne tracks traditionally requires the full 6 degrees of freedom.

Also, analysis of radar data can rely on measurements of pitch & roll - since they affect where the radar is "pointing".

Analysts are going to consider what additional fields they require, so we can create them via an Alembic Transition, and so that the fields are available via SQLAlchemy - when the analysts (or industry) eventually create the parsers for this data.

[IanMayo]

These new fields have been proposed:

Position:
North, East, Down with Reference Latitude, Longitude and Altitude.
Velocity North, Velocity East, Velocity Down,
Acceleration North, Acceleration East, Acceleration Down.

Object Body Axis:
x, y, z (right hand rule - x is movement along the roll axis (fwd), y is movement along the pitch axis (lateral), z is movement along the yaw axis (heave))
vx, vy, vz (velocity along each axis)
ax, ay, az (acceleration along each axis)

Orientation:
Then Heading, Pitch and Roll. (phi, theta, psi) in order of rotations applied
Heading Rate, Pitch Rate, Roll Rate. (phi_dot, theta_dot, psi_dot)
Rate of Change of Heading Rate, Rate of Change of Pitch Rate, Rate of Change of Roll Rate. (phi_dot_dot, theta_dot_dot, psi_dot_dot)

[IanMayo]

There are a lot of fields.  While some can be calculated from others (rates...), I think the spirit of analysts is that whatever field is received, it should be stored in that format. So, even though (I guess that) phi_dot and phi_dot_dot can be calculated, if the sensor just returns phi_dot_dot, then that measurement should be stored unchanged.

I have a "little" discomfort about the number of next fields to be added. This is partially down to the "legibility" of the database schema diagram, but also the potential impact on database size - we have lots and lots of State rows.  I guess we could avoid adding all those columns through one of these strategies:

1. encode the data into a text structure in the field. It's then down to the database client to decode the fields
2. create a new SixDOF table, with these new fields, indexed by a StateId field back to the relevant row in the States table. This will let all the fields be stored, without impact on the State table. Views can be provided which de-normalise the data to give datasets that include 6 Degs of Freedom.

[robintw]

I think I would recommend option 2, although either would work. This option is more in the spirit of standard relational database design, and would make it far easier to use with other tools - rather than having to decode a chunk of JSON in a database field (which, although Postgres does have support for native JSON fields, does make it harder to query etc).

[IanMayo]

Yes, we'll go with option-2.

Here are the units, for the Python API:

• speeds: metres per second
• accelerations: metres per second^2
• Heading Pitch Roll in radians, rad/sec, rad/sec^2

[IanMayo]

Table name: State_Detailed

The table will store 3 states for each of 9 types of measurement. The three states will be "normal", then "_dot" and "_dot_dot".

The units are as in the previous comment, but the units won't be in SQL, just in our custom SQLAlchemy object broker classes.

27 fields:

• north, east, down
• north_dot, east_dot, down_dot
• north_dot_dot, east_dot_dot, down_dot_dot
• x, y, z
• x_dot, y_dot, z_dot
• x_dot_dot, y_dot_dot, z_dot, dot
• phi, theta, psi
• phi_dot, theta_dot, psi_dot
• phi_dot_dot, theta_dot_dot, psi_dot_dot