Storing a 3D point array representing a flight path? I need to store an array of 3D points in a column: latitude, longitude, altitude

What datatype would be required for this?

Also, if I wanted to extend this in the future to include time (ie an array of 4D points), how could I do so?

I have gone for a custom datatype, but I am not sure if this is wise:

CREATE TYPE position3d AS (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION, f3 DOUBLE PRECISION)


I then created a new column with type position3d[]
This is clearly geospatial. My advice to you is to install PostGIS. Store them as a point. Points can have z-cordinates. Creating a custom type is a bad idea.

CREATE TABLE foo (
  geog geography(POINT),
  alt  int
);

INSERT INTO foo(geog,alt) VALUES
  (ST_MakePoint(long,lat), alt);


If you want to extend to include time, just add a timestamp column.

I'm not sure what you mean when you say "an array of 3D points", but typically that either means you need to normalize into a point table, or store a MULTIPOINT, or by array you mean LINESTRING.

Flight path

If you're trying to store a flight path you want a LINESTRING you can create one with ST_MakeLine.

CREATE TABLE foo (
  id   serial                  PRIMARY KEY,
  path geography(LINESTRINGZ)
);
INSERT INTO foo(path) VALUES
  (
    ST_MakeLine(ARRAY[
      ST_MakePoint(1,2,3),
      ST_MakePoint(5,5,4),
      ST_MakePoint(10,10,5)
    ])
  );

# SELECT id, ST_AsText(path) FROM foo;
 id |             st_astext              
----+------------------------------------
  1 | LINESTRING Z (1 2 3,5 5 4,10 10 5)
(1 row)