Storing 3D Point Arrays for Flight Paths in PostgreSQL

arraypostgispostgresqlspatial

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[]

Best Answer

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)

Related Solutions

PostGIS – Removing Latitude and Longitude from Table

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input:

SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need
FROM   addresses a
JOIN   cafes     c ON c.id = a.cafe_id
    , (SELECT lonlat
       FROM   addresses
       WHERE  cafe_id = 10
       AND    type = 'SearchAddress') t -- center cafe
WHERE  ST_DWithin(t.lonlat, a.lonlat, 4828)
ORDER  BY distance;

Table alias was missing: cafes c.
Makes no sense to have a.cafe_id, c.id in the SELECT list, since those are the same by definition. Pick the columns you actually need.
The columns type or state shouldn't be type text (or character varying(255)), I suggest an enum for a hand full of types or a adr_type look-up table and adr_type_id int REFERENCES adr_type (adr_type_Id in addresses.
Either way, I also suggest a partial, covering index for a faster look-up of the center coordinate. Building on "There can only be one of those addresses with that type.":
```
CREATE addresses_search_idx ON addresses (cafe_id, lonlat)
WHERE type = 'SearchAddress';
```
A lot more details discussed in chat (don't know for how long the link will be good).

PostgreSQL – summing arrays by index

I would use unnest together with array_agg, like this: SQL Fiddle

PostgreSQL 9.3.1 Schema Setup:

create table t (
A double precision[5],
B double precision[5]);

insert into t values
('{3,2,0,3,1}', '{1,0,3,2,5}');

Query 1:

with c as(
select unnest(a) a, unnest(b) b from t)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c

Results:

|         A |         B |         C |
|-----------|-----------|-----------|
| 3,2,0,3,1 | 1,0,3,2,5 | 4,2,3,5,6 |

As commented below, the query above will work well for the arrays of the same size. Otherwise it may produce an unexpected result.

If you need to support the arrays of different size, use this query:

with a as(
  select unnest(a) a from t),
b as(
  select unnest(b) b from t),
ar as(
  select a, row_number() over() r from a),
br as(
  select b, row_number() over() r from b),
c as(
  select ar.a, br.b from ar inner join br on ar.r = br.r)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c;

Best Answer

Flight path

Related Solutions

PostGIS – Removing Latitude and Longitude from Table

PostgreSQL – summing arrays by index

Related Question