:軌道に関するよくある質問

ST_makeTrajectory コンストラクターを使用して、座標点のデータを軌道オブジェクトに変換できます。 手順は以下のとおりです。

-- Create an extension.
create extension ganos_trajectory cascade;

-- Create a point table.
create table points(id integer, x float8, y float8, t timestamp, speed float8);
insert into points values(1, 128.1, 28.1, '2019-01-01 00:00:00', 100);
insert into points values(2, 128.2, 28.2, '2019-01-01 00:00:01', 101);
insert into points values(3, 128.3, 28.3, '2019-01-01 00:00:02', 102);
insert into points values(4, 128.4, 28.4, '2019-01-01 00:00:04', 103);

-- Create a trajectory table.
create table traj(id integer, traj trajectory);

-- Insert data.
insert into traj(id, traj) 
select 1, 
    ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL) 
FROM (select array_agg(x order by id) as x,  
      array_agg(y order by id) as y,
      array_agg(t order by id) as t,
      array_agg(speed order by id) as s
  From points) a;

組み込みの ST_makeTrajectory コンストラクターが要件を満たしていない場合は、必要な属性を使用してカスタマイズできます。 たとえば、int8 型の 2 つ、float4 型の 2 つ、およびタイムスタンプ型の 1 つを含む、5 つの属性を持つ軌道オブジェクトを作成するには、以下のコンストラクターをカスタマイズします。

CREATE OR REPLACE FUNCTION ST_MakeTrajectory(type leaftype, x float8[], y float8[],  
               srid integer, timespan timestamp[],attrs_name cstring[], attr1 int8[], 
               attr2 int8[], attr3 float4[], attr4 float4[], attr5 timestamp[])
    RETURNS trajectory
    AS '$libdir/libpg-trajectory16','sqltr_traj_make_all_array'
    LANGUAGE 'c' IMMUTABLE Parallel SAFE;

ST_append コンストラクターを使用して、既存の軌道オブジェクトに軌道点を追加できます。 ST_append コンストラクターの構文は以下のとおりです。

trajectory ST_append(trajectory traj, geometry spatial, timestamp[] timespan, text str_theme_json);

trajectory ST_append(trajectory traj, trajectory tail);

以下の例は、ポイントテーブルに基づいて軌道オブジェクトに軌道点を追加する方法を示しています。

-- Create an extension.
create extension ganos_trajectory cascade;

-- Create a point table.
create table points(id integer, x float8, y float8, t timestamp, speed float8);
insert into points values(1, 128.1, 28.1, '2019-01-01 00:00:00', 100);
insert into points values(2, 128.2, 28.2, '2019-01-01 00:00:01', 101);
insert into points values(3, 128.3, 28.3, '2019-01-01 00:00:02', 102);
insert into points values(4, 128.4, 28.4, '2019-01-01 00:00:04', 103);

-- Create a trajectory table.
create table traj(id integer, traj trajectory);

-- Insert data.
insert into traj(id, traj) 
select 1, 
    ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL) 
FROM (select array_agg(x order by id) as x,  
      array_agg(y order by id) as y,
      array_agg(t order by id) as t,
      array_agg(speed order by id) as s
  From points) a;

-- Insert trajectory points.
insert into points values(5, 128.5, 28.5, '2019-01-01 00:00:05', 105);
insert into points values(6, 128.6, 28.6, '2019-01-01 00:00:06', 106);
insert into points values(7, 128.7, 28.7, '2019-01-01 00:00:07', 107);

-- Update the trajectory object based on a single trajectory point.
With point_traj as (
 select ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL) AS traj
 FROM (select array_agg(x order by id) as x,  
      array_agg(y order by id) as y,
      array_agg(t order by id) as t,
      array_agg(speed order by id) as s
      From points WHERE ID = 5) a 
)
Update traj 
set traj = ST_append(traj.traj, a.traj)
From point_traj a
WHERE traj.ID=1;

-- Use a generated SQL script to update the trajectory object.
With point_traj as (
 select ST_MakeTrajectory('STPOINT'::leaftype, ARRAY[128.5::float8], 
      ARRAY[28.5::float8], 4326, ARRAY['2019-01-01 00:00:05'::timestamp], 
      ARRAY['speed'], NULL, ARRAY[106::float8], NULL) AS traj
)
Update traj 
set traj = ST_append(traj.traj, a.traj)
From point_traj a
WHERE traj.ID =1;

-- Update the trajectory object based on multiple trajectory points.
With point_traj as (
 select ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL) AS traj
 FROM (select array_agg(x order by id) as x,  
      array_agg(y order by id) as y,
      array_agg(t order by id) as t,
      array_agg(speed order by id) as s
      From points WHERE ID > 5) a 
)
Update traj 
set traj = ST_append(traj.traj, a.traj)
From point_traj a
WHERE traj.ID =1;

LZ4 は、より高い圧縮率と実行速度を備えた高度な圧縮アルゴリズムです。 LZ4 圧縮アルゴリズムを有効にする方法は以下のとおりです。

-- Enable LZ4 compression.
Set toast_compression_use_lz4 = true; 

-- Disable LZ4 compression to use the default PostgreSQL compression algorithm.
Set toast_compression_use_lz4 = false;

データベース全体で LZ4 圧縮アルゴリズムをデフォルトで有効にする方法は以下のとおりです。

-- Enable LZ4 compression for the database.
Alter database dbname Set toast_compression_use_lz4 = true;

-- Disable LZ4 compression to use the default compression algorithm for the database.
Alter database dbname Set toast_compression_use_lz4 = false;

GUC 変数 ganos.trajectory.attr_string_length を使用して、文字列タイプの属性フィールドのデフォルト長を設定できます。 以下のように設定します。

Set ganos.trajectory.attr_string_length = 32;

属性フィールドの最大値、最小値、または平均値を計算する方法は以下のとおりです。

With traj AS (
  select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-01 11:30:00","end_time":"2010-01-01 12:30:00","spatial":"SRID=4326;LINESTRING(1 1,3 5)","timeline":["2010-01-01 11:30:00","2010-01-01 12:30:00"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":4,"nullable":true,"value":[1,100]},"speed":{"type":"float","length":8,"nullable":true,"value":[null,1.0]},"angle":{"type":"string","length":64,"nullable":true,"value":["test",null]}, "tngel2":{"type":"timestamp","length":8,"nullable":true,"value":["2010-01-01 12:30:00",null]},"bearing":{"type":"bool","length":1,"nullable":true,"value":[null,true]}}}}'::trajectory a)
select avg(v) from 
(
Select unnest(st_trajAttrsAsInteger(a, 'velocity')) as v from traj
) t;