Fungsi agregat spatio-temporal yang didukung oleh LindormTable - Lindorm

Fungsi agregat spatio-temporal digunakan untuk mengurutkan dan menggabungkan titik-titik yang disimpan secara tidak berurutan. Fungsi ini umumnya diterapkan dalam skenario dengan volume besar data spatio-temporal yang memerlukan analisis lintasan, seperti Internet of Vehicles (IoV), GIS, dan Internet of Things (IoT).

Mesin dan versi yang berlaku

Fungsi agregat yang dijelaskan dalam bagian ini hanya berlaku untuk LindormTable.
Fungsi agregat ini didukung mulai versi LindormTable 2.6.5 dan seterusnya. Untuk informasi lebih lanjut tentang cara memeriksa atau meningkatkan versi LindormTable, lihat Catatan Rilis LindormTable dan Tingkatkan Versi Mesin Minor dari Instance Lindorm.

Penting

Versi SQL Lindorm harus 2.6.8 atau lebih baru. Untuk informasi lebih lanjut tentang cara memeriksa versi SQL Lindorm, lihat Versi SQL.

Fungsi

Tabel berikut mencantumkan fungsi agregat yang didukung oleh Lindorm GanosBase.

Fungsi	Deskripsi
ST_Length_Rows	Fungsi ini mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, menggabungkan titik-titik tersebut menjadi lintasan, lalu mengembalikan panjang sferis lintasan dalam meter.
ST_Resample	Fungsi ini mengurutkan titik-titik yang disimpan secara tidak berurutan berdasarkan waktu, menurunkan sampel titik-titik berdasarkan ambang temporal dan spasial yang ditentukan, dan menggabungkan data hasil penurunan sampel ke dalam lintasan.
ST_TrajectoryProfile	Fungsi ini mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, menggabungkan titik-titik tersebut menjadi lintasan, membagi lintasan menjadi segmen-segmen berdasarkan ambang temporal yang ditentukan, lalu mengembalikan koordinat dan waktu titik awal dan akhir setiap segmen.

ST_Length_Rows

Fungsi ini mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, menggabungkannya menjadi lintasan, lalu mengembalikan panjang sferis lintasan dalam meter.

Anda dapat menggunakan fungsi ST_Length_Rows bersama dengan klausa GROUP BY untuk mendapatkan panjang sferis lintasan yang digabungkan oleh titik-titik pada kolom tertentu.

Penting

Gunakan kunci utama untuk memastikan bahwa titik-titik digabungkan dalam urutan yang benar. Jika tidak, hasil panjang mungkin tidak sesuai harapan. Sebagai contoh, Anda dapat menggunakan kolom ID kendaraan dan kolom timestamp sebagai kunci utama dalam skenario IoV untuk memastikan penggabungan titik-titik dalam urutan yang tepat.

Sintaksis

double ST_Length_Rows(point column)

Parameter

Parameter	Deskripsi
kolom point	Objek geometri yang ingin Anda proses menggunakan fungsi ini. Tentukan titik-titik untuk parameter ini.

Contoh

Dalam contoh berikut, tabel bernama test_len dibuat dan diisi dengan data sampel menggunakan pernyataan-pernyataan 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)'));

Contoh 1: Hitung panjang sferis lintasan yang digabungkan oleh titik-titik yang dihasilkan oleh kendaraan car1.

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

Hasil berikut dikembalikan:

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

Contoh 2: Hitung panjang sferis lintasan yang digabungkan oleh titik-titik yang dihasilkan oleh semua kendaraan dan kelompokkan hasil berdasarkan ID kendaraan.

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

Hasil berikut dikembalikan:

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

ST_Resample

Fungsi ini mengurutkan titik-titik yang disimpan secara tidak berurutan berdasarkan waktu, menurunkan sampel titik-titik tersebut berdasarkan ambang temporal dan spasial yang ditentukan, dan menggabungkan data hasil penurunan sampel ke dalam lintasan.

Saat menggunakan fungsi ST_Resample, Anda harus menentukan kondisi filter untuk titik-titik lintasan atau lintasan tunggal. Fungsi ini tidak dapat digunakan bersama dengan klausa GROUP BY.

Sintaksis

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

Parameter

Parameter	Deskripsi
geomColumnName	Objek geometri yang ingin Anda proses menggunakan fungsi ini. Tentukan titik-titik untuk parameter ini.
xColumnName	Kolom longitude. Tentukan nilai DOUBLE untuk parameter ini. Parameter ini harus ditentukan bersama dengan parameter yColumnName.
yColumnName	Kolom latitude. Tentukan nilai DOUBLE untuk parameter ini. Parameter ini harus ditentukan bersama dengan parameter xColumnName.
timeColumnName	Kolom waktu. Titik-titik dalam tabel dibangun dalam urutan menaik kolom ini. Kolom waktu mendukung tipe data TIME, TIMESTAMP, dan LONG. Jika tipe data kolom waktu adalah LONG, unit nilai dalam kolom harus dalam milidetik.
config	Parameter penurunan sampel dalam format JSON. Tentukan nilai STRING untuk parameter penurunan sampel. Nilai parameter penurunan sampel dalam format berikut: {"Nama parameter": Nilai}

Tabel berikut menunjukkan parameter penurunan sampel yang dapat Anda tentukan dalam parameter config.

Parameter	Deskripsi
downsample_time	Ambang penurunan sampel temporal. Interval waktu antara titik-titik lintasan yang berdekatan dalam data hasil penurunan sampel lebih besar dari nilai parameter ini. Tentukan nilai INT atau LONG untuk parameter ini. Unit: milidetik. Nilai default: -1.
downsample_distance	Ambang penurunan sampel spasial. Geometri lintasan disederhanakan menggunakan algoritma yang ditentukan berdasarkan nilai parameter ini. Tentukan nilai INT, LONG, FLOAT, atau DOUBLE untuk parameter ini. Unit parameter ini sama dengan unit koordinat. Nilai default: -1.0.
simplifer	Algoritma yang digunakan dalam penurunan sampel. Tentukan nilai STRING untuk parameter ini. Nilai valid: vw: Algoritma Visvalingam-Whyatt. dp: Algoritma Douglas-Peucker. topologypreserving: Algoritma Douglas-Peucker yang mempertahankan topologi lintasan. Parameter ini hanya valid ketika parameter downsample_distance ditentukan. Nilai default: topologypreserving.

Nilai yang Dikembalikan

Fungsi ini mengembalikan urutan titik-titik lintasan hasil penurunan sampel yang diurutkan berdasarkan waktu. Urutan yang dikembalikan adalah string dalam format berikut: [{\"x\":Longitude,\"y\":Latitude,\"t\":Time}, {\"x\":Longitude,\"y\":Latitude,\"t\":Time}, ...].

Contoh

Dalam contoh berikut, tabel bernama gps_point dibuat dan diisi dengan data sampel menggunakan pernyataan-pernyataan 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 kendaraan yang ditentukan dalam rentang waktu tertentu di tabel gps_point. Lintasan yang dikembalikan dihasilkan oleh titik-titik yang telah diturunkan sampelnya menggunakan algoritma Visvalingam-Whyatt berdasarkan ambang spasial 0.0001.

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

Hasil berikut dikembalikan:

+----------------------------------------------------------------------------------------------------------------------+
|                                             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

Fungsi ini mengurutkan titik-titik yang tidak berurutan berdasarkan waktu, menggabungkannya menjadi lintasan, membagi lintasan menjadi segmen-segmen berdasarkan ambang temporal yang ditentukan, lalu mengembalikan koordinat dan waktu titik awal dan akhir setiap segmen.

Fungsi ST_TrajectoryProfile harus digunakan bersama dengan klausa GROUP BY untuk menghitung koordinat dan waktu titik awal dan akhir setiap segmen lintasan yang digabungkan oleh sekelompok titik.

Fungsi ST_TrajectoryProfile sering digunakan bersama dengan fungsi hubungan spasial, seperti ST_DWithinSphere. Dalam hal ini, Anda dapat menghitung koordinat dan waktu titik yang pertama kali masuk ke area tertentu dan titik yang terakhir meninggalkan area tertentu dalam segmen lintasan.

Sintaksis

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

Parameter

Parameter	Deskripsi
geomColumnName	Objek geometri yang ingin Anda proses menggunakan fungsi ini. Tentukan titik-titik untuk parameter ini. Catatan Jika data titik disimpan sebagai objek titik di tabel, tentukan parameter ini saat Anda menggunakan fungsi.
xColumnName	Kolom longitude. Tentukan nilai DOUBLE untuk parameter ini. Parameter ini harus ditentukan bersama dengan parameter yColumnName. Catatan Jika data titik disimpan sebagai longitude dan latitude di tabel, tentukan parameter ini saat Anda menggunakan fungsi.
yColumnName	Kolom latitude. Tentukan nilai DOUBLE untuk parameter ini. Parameter ini harus ditentukan bersama dengan parameter xColumnName. Catatan Jika data titik disimpan sebagai longitude dan latitude di tabel, tentukan parameter ini saat Anda menggunakan fungsi.
timeColumnName	Kolom waktu. Titik-titik dalam tabel dibangun dalam urutan menaik kolom ini.
thresh	Parameter ini opsional. Unit: milidetik. Setelah titik-titik diurutkan berdasarkan kolom waktu, jika interval waktu antara dua titik yang berdekatan sama dengan atau lebih kecil dari ambang batas, kedua titik tersebut dianggap berada dalam segmen lintasan yang sama. Jika interval waktu antara dua titik yang berdekatan lebih besar dari ambang batas, kedua titik tersebut dianggap berada dalam segmen lintasan yang berbeda. Nilai default: 10000.

Nilai yang Dikembalikan

Fungsi ini mengembalikan string JSON dalam format berikut:

{"Nomor segmen lintasan":"{\"endY\":Latitude titik akhir,\"endX\":Longitude titik akhir,\"startY\":Latitude titik awal,\"startTime\":Waktu titik awal,\"startX\":Longitude titik awal,\"endTime\":Waktu titik akhir}"}.

Contoh

Dalam contoh berikut, tabel bernama test_traj dibuat dan diisi dengan data sampel menggunakan pernyataan-pernyataan 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');

Dalam setiap segmen lintasan yang dihasilkan oleh data dalam tabel test_traj, kueri koordinat dan waktu titik yang pertama kali masuk ke area tertentu dan titik yang terakhir meninggalkan area tertentu dalam rentang waktu yang ditentukan.

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 indeks sekunder digunakan dalam kueri, semua kolom yang digunakan dalam kueri, termasuk kunci agregasi, kolom kondisi filter, dan kolom parameter fungsi, harus ditambahkan ke indeks sebagai kolom yang disertakan. Dengan cara ini, kueri tidak perlu memindai data di tabel dasar. Sebagai contoh, dalam pernyataan sebelumnya, kolom carid, p, dan collect_time harus ditambahkan ke indeks sekunder sebagai kolom yang disertakan.

Hasil berikut dikembalikan:

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