Lindorm:集計関数

構文

double ST_Length_Rows(point column)

パラメーター

使用上の注意事項

• ST_Length_Rows を使用する際は、GROUP BY 句と組み合わせて、列（例：車両 ID）ごとにグループ化された軌道長を算出してください。

• 正しい並び順は、プライマリキーのソート順に依存します。行が正しい移動順序で格納およびスキャンされるよう、エンティティ識別子（例：車両 ID）とタイムスタンプ列を含む複合プライマリキーを定義してください。プライマリキーが正しい並び順を保証しない場合、返される軌道長は不正確になる可能性があります。

例

以下の例では、次のように作成およびデータ入力された test_len テーブルを使用します：

CREATE TABLE test_len (
  carid VARCHAR,
  collect_time TIMESTAMP,
  p GEOMETRY(POINT),
  PRIMARY KEY(carid, collect_time)
);

INSERT INTO test_len (carid, collect_time, p) VALUES
  ('car1', '2023-09-12 00:04:02', ST_GeomFromText('POINT (111.40269 35.61695)')),
  ('car1', '2023-09-12 00:09:07', ST_GeomFromText('POINT (111.40127 35.616096)')),
  ('car1', '2023-09-12 00:14:03', ST_GeomFromText('POINT (111.400604 35.616013)')),
  ('car1', '2023-09-12 00:20:13', ST_GeomFromText('POINT (111.399734 35.613983)')),
  ('car1', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)')),
  ('car2', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)'));

プライマリキー (carid, collect_time) により、各車両ごとにポイントが昇順の時間順で格納されるため、軌道が正しくアセンブルされます。

例 1：car1 の軌道について球面距離による軌道長を算出します。

SELECT ST_LENGTH_ROWS(p) FROM test_len WHERE carid = 'car1';

結果：

+--------------------+
| st_length_rows(p)  |
+--------------------+
| 805.55323541493414 |
+--------------------+

例 2：車両 ID ごとにグループ化した各車両の球面距離による軌道長を算出します。

SELECT carid, ST_LENGTH_ROWS(p) AS len
FROM test_len
WHERE carid <= 'car2'
GROUP BY carid;

結果：

+-------+--------------------+
| carid |        len         |
+-------+--------------------+
| car1  | 805.55323541493414 |
| car2  | 0                  |
+-------+--------------------+

構文

バリアント 1： ポイント列の入力。

String ST_Resample(String geomColumnName, String timeColumnName, String config)

変形 2：経度列および緯度列を個別に指定します。

String ST_Resample(String xColumnName, String yColumnName, String timeColumnName, String config)

パラメーター

ダウンサンプリングパラメーター（config）

返される値

時間順にソートされたダウンサンプリング済み軌道ポイントの JSON 配列：

[{"x": <longitude>, "y": <latitude>, "t": <time>}, ...]

使用上の注意事項

• 特定のエンティティまたは時間範囲に対応する軌道ポイントを選択するため、フィルター条件を適用してください。ST_Resample は、フルテーブルではなく、フィルター適用後の結果セットに対して動作します。

• ST_Resample は GROUP BY 句と併用できません。

例

以下の例では、次のように作成およびデータ入力された gps_points テーブルを使用します：

CREATE TABLE gps_points (
  account_id VARCHAR,
  collect_time TIMESTAMP,
  gps_point GEOMETRY(POINT),
  PRIMARY KEY(account_id, collect_time)
);

INSERT INTO gps_points (account_id, collect_time, gps_point) VALUES
  ('001', '2023-11-10 11:00:30', ST_MakePoint(113.665431, 34.773)),
  ('001', '2023-11-10 11:00:31', ST_MakePoint(113.665432, 34.773)),
  ('001', '2023-11-10 11:00:32', ST_MakePoint(113.665433, 34.773)),
  ('001', '2023-11-10 11:00:33', ST_MakePoint(113.665434, 34.774));

アカウント 001 の軌道を 24 時間のウィンドウ内でクエリし、空間しきい値 0.0001 を持つ Visvalingam-Whyatt アルゴリズムでダウンサンプリングします：

SELECT ST_Resample(
    gps_point,
    collect_time,
    '{"downsample_distance": 0.0001, "simplifier": "vw"}'
  ) AS resampled_traj
FROM gps_points
WHERE account_id = '001'
  AND collect_time >= '2023-11-10 00:00:00'
  AND collect_time <= '2023-11-11 00:00:00';

結果：

+-----------------------------------------------------------------------------------------------------+
| resampled_traj                                                                                      |
+-----------------------------------------------------------------------------------------------------+
| [{"x":113.665431,"y":34.773,"t":"2023-11-10 11:00:30.0"},{"x":113.665434,"y":34.774,"t":"2023-11-10 11:00:33.0"}] |
+-----------------------------------------------------------------------------------------------------+

構文

バリアント 1: ポイント列を入力として。

String ST_TrajectoryProfile(String geomColumnName, String timeColumnName, long thresh)

変形 2：経度列および緯度列を個別に指定します。

String ST_TrajectoryProfile(String xColumnName, String yColumnName, String timeColumnName, long thresh)

パラメーター

返される値

各キーがセグメントインデックス（0 基準）であり、各値がセグメントの境界座標およびタイムスタンプを含む JSON 文字列である JSON オブジェクト：

{
  "0": "{\"startX\": <longitude>, \"startY\": <latitude>, \"startTime\": <epoch ms>, \"endX\": <longitude>, \"endY\": <latitude>, \"endTime\": <epoch ms>}",
  "1": "...",
  ...
}

使用上の注意事項

• ST_TrajectoryProfile を使用する際は、エンティティ（例：車両 ID）ごとにポイントを軌道に集約するために、GROUP BY 句と併用する必要があります。

• セカンダリインデックスを使用する場合、クエリで参照されるすべての列（GROUP BY キー、フィルター条件列、関数パラメーター列）を、インデックスの含まれる列として追加してください。これにより、ベーステーブルのスキャンを回避できます。たとえば、クエリで carid でグループ化し、関数に p および collect_time を渡す場合、これら 3 つの列をすべてインデックスに含めてください。

例

以下の例では、次のように作成およびデータ入力された test_traj テーブルを使用します：

CREATE TABLE test_traj (
  carid VARCHAR,
  collect_time TIMESTAMP,
  p GEOMETRY(POINT),
  status VARCHAR,
  PRIMARY KEY(z-order(p), carid, collect_time)
);

INSERT INTO test_traj (carid, collect_time, p, status) VALUES
  ('car1', '2023-09-12 00:04:02', ST_GeomFromText('POINT (111.40269 35.61695)'), 'normal'),
  ('car1', '2023-09-12 00:09:07', ST_GeomFromText('POINT (111.40127 35.616096)'), 'normal'),
  ('car1', '2023-09-12 00:14:03', ST_GeomFromText('POINT (111.400604 35.616013)'), 'normal'),
  ('car1', '2023-09-12 00:20:13', ST_GeomFromText('POINT (111.399734 35.613983)'), 'normal'),
  ('car1', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)'), 'normal'),
  ('car2', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)'), 'normal');

各車両について、POINT (111.40217 35.616386) の半径 100 メートル以内で、2023-09-12 00:09:07 から 2023-09-20 00:09:07 の期間に収集されたポイントのうち、最初の進入点および最後の退出点を特定します。セグメント間のギャップしきい値には 30,000 ミリ秒（30 秒）を使用します：

SELECT
  carid,
  ST_TrajectoryProfile(p, collect_time, 30000) AS trajprofile
FROM test_traj
WHERE ST_DWithinSphere(ST_GeomFromText('POINT (111.40217 35.616386)'), p, 100.0)
  AND collect_time >= '2023-09-12 00:09:07'
  AND collect_time <= '2023-09-20 00:09:07'
GROUP BY carid;

結果：

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| carid | trajprofile                                                                                                                                                                                                                                                                                   |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| car1  | {"0":"{\"endY\":35.616096,\"endX\":111.40127,\"startY\":35.616096,\"startTime\":1694448547000,\"startX\":111.40127,\"endTime\":1694448547000}","1":"{\"endY\":35.616386,\"endX\":111.40217,\"startY\":35.616386,\"startTime\":1694449641000,\"startX\":111.40217,\"endTime\":1694449641000}"} |
| car2  | {"0":"{\"endY\":35.616386,\"endX\":111.40217,\"startY\":35.616386,\"startTime\":1694449641000,\"startX\":111.40217,\"endTime\":1694449641000}"}                                                                                                                                               |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

car1 は検索エリア内に 2 つの軌道セグメント（セグメント 0 およびセグメント 1）を持ちます。car2 はエリア内に 1 つのポイントしか持たず、そのポイントは開始座標と終了座標およびタイムスタンプが一致する単一のセグメント（セグメント 0）を形成します。