I would store them separately and not as a composite type. There is a geospatial component here. I would store that as a PostGIS Geography type which is in fact composite: the x getter is ST_X
, and y getter is ST_Y
CREATE TABLE plane_path (
id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
geog geography(pointz, 4326),
ts timestamp with time zone,
pitch float,
yaw float,
roll float
);
Or if you want, you could represent your angle as a DOMAIN and put it on the table too.
CREATE DOMAIN angle AS float CHECK (value between -90 and 90);
CREATE TABLE plane_path (
id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
geog geography(pointz, 4326),
ts timestamp with time zone,
pitch angle,
yaw angle,
roll angle
);
With this schema and PostGIS, you could easily aggregate the points into a linepath representing the trajectory with ST_MakeLine
CREATE TABLE ( geog geography(pointz 4326), pitch float, yaw float, roll float )
. With this method of storage you could use a PostGIS aggregate to get a linesegment that represented the flight path (and the like)