Fungsi agregat spatio-temporal - Lindorm - Alibaba Cloud Documentation Center

Lindorm GanosBase menyediakan fungsi agregat spatio-temporal yang mengurutkan titik-titik tidak berurutan berdasarkan waktu, menggabungkannya menjadi lintasan (trajectory), lalu menghitung metrik lintasan tersebut. Fungsi-fungsi ini digunakan untuk beban kerja spatio-temporal skala besar seperti aplikasi Internet of Vehicles (IoV), GIS, dan Internet of Things (IoT) yang memerlukan analisis lintasan.

Prasyarat

Engine: LindormTable 2.6.5 atau versi lebih baru. Untuk melihat atau melakukan upgrade versi Anda, lihat Catatan rilis LindormTable dan Upgrade versi mesin minor instans Lindorm.
Versi SQL: Lindorm SQL 2.6.8 atau versi lebih baru. Untuk memeriksa versi Anda, lihat Versi SQL.

Fungsi

Fungsi	Deskripsi
`ST_Length_Rows`	Mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, menggabungkannya menjadi lintasan, dan mengembalikan panjang sferis lintasan tersebut dalam satuan meter.
`ST_Resample`	Mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, melakukan downsampling titik-titik tersebut berdasarkan ambang batas temporal dan spasial, serta mengembalikan lintasan hasil downsampling sebagai string JSON.
`ST_TrajectoryProfile`	Mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, menggabungkannya menjadi lintasan, membagi lintasan tersebut berdasarkan ambang batas temporal, dan mengembalikan koordinat serta timestamp titik awal dan akhir setiap segmen.

ST_Length_Rows

Mengurutkan titik-titik tidak berurutan berdasarkan waktu, menggabungkannya menjadi lintasan, lalu mengembalikan panjang sferis lintasan tersebut dalam satuan meter.

Sintaksis

double ST_Length_Rows(point column)

Parameter

Parameter	Deskripsi
`point column`	Kolom geometri yang akan diproses. Harus berisi objek POINT.

Catatan penggunaan

Gunakan ST_Length_Rows bersama klausa GROUP BY untuk menghitung panjang lintasan yang dikelompokkan berdasarkan kolom tertentu (misalnya, ID kendaraan).
Pengurutan yang benar bergantung pada urutan sortir primary key. Definisikan primary key komposit yang mencakup identifier entitas (seperti ID kendaraan) dan kolom timestamp agar baris disimpan serta dipindai sesuai urutan perjalanan yang benar. Jika primary key tidak menjamin pengurutan yang tepat, panjang yang dikembalikan mungkin tidak akurat.

Contoh

Contoh berikut menggunakan tabel test_len, yang dibuat dan diisi sebagai berikut:

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)'));

Primary key (carid, collect_time) memastikan bahwa titik-titik disimpan dalam urutan waktu menaik per kendaraan, sehingga lintasan dirakit dengan benar.

Contoh 1: Hitung panjang sferis lintasan untuk car1.

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

Hasil:

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

Contoh 2: Hitung panjang sferis untuk setiap kendaraan, dikelompokkan berdasarkan ID kendaraan.

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

Hasil:

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

ST_Resample

Mengurutkan titik-titik tidak berurutan berdasarkan waktu dan melakukan downsampling berdasarkan ambang batas temporal dan spasial. Fungsi ini mengembalikan lintasan hasil downsampling sebagai string JSON.

Sintaksis

Varian 1: Kolom titik sebagai input.

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

Varian 2: Kolom bujur dan lintang terpisah sebagai input.

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

Parameter

Parameter	Deskripsi
`geomColumnName`	Kolom geometri POINT. Gunakan parameter ini ketika data titik disimpan sebagai objek geometri (Varian 1).
`xColumnName`	Kolom bujur (DOUBLE). Harus dipasangkan dengan `yColumnName`. Gunakan parameter ini ketika data titik disimpan sebagai nilai bujur dan lintang terpisah (Varian 2).
`yColumnName`	Kolom lintang (DOUBLE). Harus dipasangkan dengan `xColumnName` (Varian 2).
`timeColumnName`	Kolom waktu yang digunakan untuk mengurutkan titik secara menaik. Mendukung tipe data TIME, TIMESTAMP, dan LONG. Jika LONG, nilainya harus dalam milidetik.
`config`	Konfigurasi downsampling dalam format JSON (STRING). Format: `{"parameter": value, ...}`. Lihat tabel di bawah untuk daftar parameter yang tersedia.

Parameter downsampling (`config`)

Parameter	Tipe	Bawaan	Deskripsi
`downsample_time`	INT atau LONG	`-1` (dinonaktifkan)	Ambang batas downsampling temporal dalam milidetik. Titik-titik berdekatan dalam output dipisahkan oleh interval yang lebih besar dari nilai ini.
`downsample_distance`	INT, LONG, FLOAT, atau DOUBLE	`-1.0` (dinonaktifkan)	Ambang batas downsampling spasial. Geometri lintasan disederhanakan menggunakan algoritma yang ditentukan berdasarkan nilai parameter ini. Satuan parameter ini sama dengan satuan koordinat.
`simplifier`	STRING	`topologypreserving`	Algoritma penyederhanaan. Nilai yang valid: `vw` (Visvalingam-Whyatt), `dp` (Douglas-Peucker), `topologypreserving` (Douglas-Peucker dengan pelestarian topologi; hanya valid jika `downsample_distance` ditentukan).

Nilai yang dikembalikan

Array JSON titik lintasan hasil downsampling yang telah diurutkan berdasarkan waktu:

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

Catatan penggunaan

Terapkan kondisi filter untuk memilih titik lintasan untuk entitas atau rentang waktu tertentu. ST_Resample beroperasi pada set hasil yang telah difilter, bukan seluruh tabel.
ST_Resample tidak dapat digunakan bersama klausa GROUP BY.

Contoh

Contoh berikut menggunakan tabel gps_points, yang dibuat dan diisi sebagai berikut:

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));

Kueri lintasan akun 001 dalam jendela 24 jam dan lakukan downsampling menggunakan algoritma Visvalingam-Whyatt dengan ambang batas spasial sebesar 0.0001:

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';

Hasil:

+-----------------------------------------------------------------------------------------------------+
| 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"}] |
+-----------------------------------------------------------------------------------------------------+

ST_TrajectoryProfile

Mengurutkan titik-titik tidak berurutan berdasarkan waktu, merakitnya menjadi lintasan, membagi lintasan tersebut berdasarkan ambang batas celah temporal, lalu mengembalikan koordinat serta timestamp titik awal dan akhir setiap segmen.

ST_TrajectoryProfile biasanya dikombinasikan dengan fungsi hubungan spasial seperti ST_DWithinSphere untuk menentukan kapan suatu entitas pertama kali memasuki dan terakhir meninggalkan area tertentu dalam setiap segmen lintasan.

Sintaksis

Varian 1: Kolom titik sebagai input.

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

Varian 2: Kolom bujur dan lintang terpisah sebagai input.

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

Parameter

Parameter	Deskripsi
`geomColumnName`	Kolom geometri POINT. Gunakan parameter ini ketika data titik disimpan sebagai objek geometri (Varian 1).
`xColumnName`	Kolom bujur (DOUBLE). Harus dipasangkan dengan `yColumnName`. Gunakan parameter ini ketika data titik disimpan sebagai nilai bujur dan lintang terpisah (Varian 2).
`yColumnName`	Kolom lintang (DOUBLE). Harus dipasangkan dengan `xColumnName` (Varian 2).
`timeColumnName`	Kolom waktu. Titik-titik diurutkan dalam urutan menaik berdasarkan kolom ini.
`thresh`	(Opsional) Celah waktu maksimum dalam milidetik antara dua titik berdekatan yang masih termasuk dalam segmen lintasan yang sama. Jika celah antara dua titik melebihi ambang batas ini, keduanya ditempatkan dalam segmen terpisah. Bawaan: `10000` (10 detik).

Nilai yang dikembalikan

Objek JSON di mana setiap kunci adalah indeks segmen (berbasis 0) dan setiap nilai berupa string JSON yang berisi koordinat batas dan timestamp segmen tersebut:

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

Catatan penggunaan

ST_TrajectoryProfile harus digunakan bersama klausa GROUP BY untuk menggabungkan titik-titik menjadi lintasan berdasarkan entitas (misalnya, ID kendaraan).
Jika menggunakan secondary index, tambahkan semua kolom yang dirujuk dalam kueri—termasuk kunci GROUP BY, kolom kondisi filter, dan kolom parameter fungsi—sebagai included columns dalam indeks. Hal ini menghindari pemindaian tabel dasar. Misalnya, jika kueri dikelompokkan berdasarkan carid dan meneruskan p serta collect_time ke fungsi, sertakan ketiga kolom tersebut dalam indeks.

Contoh

Contoh berikut menggunakan tabel test_traj, yang dibuat dan diisi sebagai berikut:

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');

Untuk setiap kendaraan, temukan titik masuk pertama dan titik keluar terakhir dalam radius 100 meter dari POINT (111.40217 35.616386), untuk titik-titik yang dikumpulkan antara 2023-09-12 00:09:07 dan 2023-09-20 00:09:07. Gunakan ambang batas celah segmen sebesar 30.000 ms (30 detik):

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;

Catatan

Jika secondary index digunakan dalam kueri, tambahkan carid, p, dan collect_time sebagai included columns dalam indeks agar kueri tidak melakukan pemindaian tabel dasar.

Hasil:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 memiliki dua segmen lintasan dalam area pencarian (segmen 0 dan segmen 1). car2 memiliki satu titik dalam area tersebut, yang membentuk satu segmen (segmen 0) di mana koordinat dan timestamp awal serta akhirnya identik.