Cara menulis titik lintasan yang dikumpulkan ke LindormTable dan melakukan kueri cepat - Lindorm

Panduan ini memandu Anda menyimpan dan mengkueri data lintasan kendaraan menggunakan Lindorm GanosBase SQL. Contoh ini memodelkan skenario Internet of Vehicles (IoV): titik lintasan GPS ditulis ke tabel lebar Lindorm, lalu dikueri berdasarkan rentang spatio-temporal.

Di akhir panduan ini, Anda akan:

Membuat tabel data spatio-temporal dan memasukkan titik lintasan
Membangun indeks spatio-temporal untuk mempercepat kueri rentang
Mengkueri titik lintasan dalam poligon spasial dan jendela waktu tertentu

Prasyarat

Sebelum memulai, pastikan Anda telah:

LindormTable diaktifkan dengan versi mesin database 2.6.5 atau lebih baru. Untuk memeriksa atau melakukan upgrade versi Anda, lihat LindormTable Version Guide dan Minor version update.
JDK 1.8 atau lebih baru terinstal di lingkungan Java Anda.
Alamat koneksi untuk SQL tabel lebar Lindorm dan daftar putih yang telah dikonfigurasi. Lihat Access an instance.

Langkah 1: Buat tabel dan tulis data lintasan

Koneksi menggunakan Lindorm-cli

Contoh ini menggunakan Lindorm-cli pada Linux. Untuk terhubung melalui JDBC, lihat Connect using Java JDBC.

Unduh Lindorm-cli dan ekstrak paketnya.

Jalankan perintah berikut untuk terhubung ke tabel lebar Lindorm:

Parameter	Nilai contoh	Cara mendapatkan
`<jdbc-url>`	`jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060`	Alamat koneksi untuk SQL tabel lebar Lindorm. Lihat Access an instance.
`<username>`	`root`	Lihat di sistem manajemen kluster Lindorm.
`<password>`	`root`	Lihat di sistem manajemen kluster Lindorm.

./lindorm-cli -url <jdbc-url> -username <username> -password <password>

Koneksi yang berhasil menghasilkan output seperti berikut:

Connected to jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060
lindorm-cli version: 1.0.15

Pilih metode penyimpanan

Setiap titik lintasan menyimpan bujur (x), lintang (y), dan waktu (t). Lindorm GanosBase mendukung dua cara menyimpan koordinat x dan y:

Metode penyimpanan	Kinerja	Kapan digunakan
`Geometry(Point)` — menyimpan x dan y dalam satu kolom spasial	Lebih tinggi	Tabel baru dan sebagian besar kasus produksi
Kolom terpisah — menyimpan x dan y sebagai kolom `double` individual	Lebih rendah (penurunan kinerja akibat dua kolom)	Saat mengintegrasikan dengan skema yang sudah memisahkan koordinat

Gunakan Geometry(Point) kecuali Anda memiliki skema yang mengharuskan kolom terpisah.

Buat tabel

Opsi 1 (direkomendasikan): Gunakan Geometry(Point)

CREATE TABLE gps_data (id int, g geometry(point), t timestamp, ship_name varchar, PRIMARY KEY(id, t));

Kolom	Tipe	Deskripsi
`g`	`geometry(point)`	Kolom spasial yang menyimpan bujur dan lintang sebagai satu titik
`t`	`timestamp`	Kolom waktu. Tipe yang didukung: `Time`, `Timestamp`, atau `Long` (Stempel waktu UNIX dalam milidetik)
`ship_name`	`varchar`	Nama kapal yang menghasilkan data lintasan
`PRIMARY KEY(id, t)`	—	Kunci primer komposit pada `id` dan `t`

Opsi 2: Gunakan kolom terpisah

CREATE TABLE gps_data_point (id int, x double, y double, t timestamp, ship_name varchar, PRIMARY KEY(id, t));

Tulis data lintasan

Masukkan baris satu per satu

Untuk gps_data (menggunakan ST_MakePoint untuk membuat setiap titik):

INSERT INTO gps_data (id, g, t, ship_name) VALUES (1, ST_MakePoint(119.073544, 25.3244),   '2021-01-01 10:00:00', 'ship001');
INSERT INTO gps_data (id, g, t, ship_name) VALUES (1, ST_MakePoint(119.073544, 25.3244),   '2021-01-01 10:05:03', 'ship001');
INSERT INTO gps_data (id, g, t, ship_name) VALUES (1, ST_MakePoint(119.073544, 25.324382), '2021-01-01 10:08:32', 'ship001');
INSERT INTO gps_data (id, g, t, ship_name) VALUES (1, ST_MakePoint(119.073536, 25.324418), '2021-01-01 10:10:22', 'ship001');
INSERT INTO gps_data (id, g, t, ship_name) VALUES (2, ST_MakePoint(19.07352,   25.34),     '2021-01-01 08:20:21', 'ship002');
INSERT INTO gps_data (id, g, t, ship_name) VALUES (2, ST_MakePoint(19.07352,   25.33),     '2021-01-01 08:22:20', 'ship002');

ST_MakePoint(longitude, latitude) membuat titik geometri dari nilai koordinat. Misalnya, ST_MakePoint(119.073544, 25.3244) menghasilkan titik pada bujur 119.073544, lintang 25.3244.

Catatan

ST_GeomFromText juga membuat titik geometri menggunakan format Well-known Text (WKT), tetapi memiliki kinerja penulisan lebih rendah dibanding ST_MakePoint. Lihat ST_GeomFromText untuk detailnya.

Untuk gps_data_point (menggunakan kolom koordinat terpisah):

INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073544, 25.3244,   '2021-01-01 10:00:00', 'ship001');
INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073544, 25.3244,   '2021-01-01 10:05:03', 'ship001');
INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073544, 25.324382, '2021-01-01 10:08:32', 'ship001');
INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073536, 25.324418, '2021-01-01 10:10:22', 'ship001');
INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (2, 19.07352,   25.34,     '2021-01-01 08:20:21', 'ship002');
INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (2, 19.07352,   25.33,     '2021-01-01 08:22:20', 'ship002');

(Opsional) Masukkan batch dengan UPSERT

Gunakan UPSERT untuk menulis beberapa baris dalam satu pernyataan:

UPSERT INTO gps_data (id, g, t, ship_name) VALUES
  (1, ST_MakePoint(119.073544, 25.3244),   '2021-01-01 10:00:00', 'ship001'),
  (1, ST_MakePoint(119.073544, 25.3244),   '2021-01-01 10:05:03', 'ship001'),
  (1, ST_MakePoint(119.073544, 25.324382), '2021-01-01 10:08:32', 'ship001'),
  (1, ST_MakePoint(119.073536, 25.324418), '2021-01-01 10:10:22', 'ship001'),
  (2, ST_MakePoint(19.07352,   25.34),     '2021-01-01 08:20:21', 'ship002'),
  (2, ST_MakePoint(19.07352,   25.33),     '2021-01-01 08:22:20', 'ship002');

UPSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES
  (1, 119.073544, 25.3244,   '2021-01-01 10:00:00', 'ship001'),
  (1, 119.073544, 25.3244,   '2021-01-01 10:05:03', 'ship001'),
  (1, 119.073544, 25.324382, '2021-01-01 10:08:32', 'ship001'),
  (1, 119.073536, 25.324418, '2021-01-01 10:10:22', 'ship001'),
  (2, 19.07352,   25.34,     '2021-01-01 08:20:21', 'ship002'),
  (2, 19.07352,   25.33,     '2021-01-01 08:22:20', 'ship002');

Verifikasi data

Kueri gps_data menggunakan ST_AsText untuk menampilkan koordinat dalam format yang mudah dibaca:

SELECT id, ST_AsText(g) AS position, ship_name FROM gps_data;

Output yang diharapkan:

+----+------------------------------+-----------+
| id |           position           | ship_name |
+----+------------------------------+-----------+
| 1  | POINT (119.073544 25.3244)   | ship001   |
| 1  | POINT (119.073544 25.3244)   | ship001   |
| 1  | POINT (119.073544 25.324382) | ship001   |
| 1  | POINT (119.073536 25.324418) | ship001   |
| 2  | POINT (19.07352 25.34)       | ship002   |
| 2  | POINT (19.07352 25.33)       | ship002   |
+----+------------------------------+-----------+

Kueri gps_data_point secara langsung:

SELECT * FROM gps_data_point;

Output yang diharapkan:

+----+-------------------------------+------------+-----------+-----------+
| id |               t               |     x      |     y     | ship_name |
+----+-------------------------------+------------+-----------+-----------+
| 1  | 2021-01-01 10:00:00 +0000 UTC | 119.073544 | 25.3244   | ship001   |
| 1  | 2021-01-01 10:05:03 +0000 UTC | 119.073544 | 25.3244   | ship001   |
| 1  | 2021-01-01 10:08:32 +0000 UTC | 119.073544 | 25.324382 | ship001   |
| 1  | 2021-01-01 10:10:22 +0000 UTC | 119.073536 | 25.324418 | ship001   |
| 2  | 2021-01-01 08:20:21 +0000 UTC | 19.07352   | 25.34     | ship002   |
| 2  | 2021-01-01 08:22:20 +0000 UTC | 19.07352   | 25.33     | ship002   |
+----+-------------------------------+------------+-----------+-----------+

Koneksi menggunakan Java JDBC

Untuk menulis data spatio-temporal melalui JDBC, gunakan kueri parameterisasi dengan PreparedStatement. Contoh berikut mengikat koordinat ke placeholder ST_MakePoint(?, ?).

// Membuat koneksi
Connection connection = DriverManager.getConnection(url, properties);
final String tableName = "testtbl";

// Membuat tabel
try (Statement stmt = conn.createStatement()) {
    stmt.execute("create table " + tableName +
        "(p1 int, c1 varchar, c2 geometry(point), constraint primary key (p1))");
}

// Pernyataan upsert parameterisasi
final String upsertSql = "upsert into " + tableName + "(p1, c1, c2) values (?, ?, ST_MakePoint(?, ?))";

// Mengikat parameter dan mengeksekusi
try (PreparedStatement preparedStatement = conn.prepareStatement(upsertSql)) {
    preparedStatement.setInt(1, 0);         // p1: ID baris
    preparedStatement.setString(2, "name"); // c1: nama
    preparedStatement.setDouble(3, 5.0);    // bujur
    preparedStatement.setDouble(4, 5.0);    // lintang
    preparedStatement.executeUpdate();
}

Untuk panduan lengkap koneksi JDBC, lihat Use the Lindorm wide table SQL Java API to connect to and use LindormTable.

Langkah 2: Percepat kueri dengan indeks spatio-temporal

Tanpa indeks, kueri rentang spatio-temporal melakukan pemindaian tabel penuh—lambat untuk set data besar. Indeks secondary spatio-temporal pada kolom spasial dan waktu memungkinkan GanosBase melewati baris yang tidak relevan dan mengembalikan hasil dalam waktu singkat.

Atur properti tabel yang diperlukan sebelum membuat indeks:
Catatan
Jika kasus penggunaan Anda memerlukan pembaruan data pada stempel waktu apa pun (bukan hanya yang terbaru), atur MUTABILITY menjadi MUTABLE_ALL sebagai gantinya:
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_ALL';
```
Untuk detail opsi mutabilitas, lihat Basic concepts.
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_LATEST';
ALTER TABLE gps_data SET 'CONSISTENCY'='strong';
```
Buat indeks secondary spatio-temporal pada kolom spasial g dan kolom waktu t:
```
CREATE INDEX idt ON gps_data (Z-ORDER(g, t));
```
Untuk informasi lebih lanjut, lihat Create a spatio-temporal index.

Langkah 3: Kueri titik lintasan berdasarkan rentang spatio-temporal

Gunakan ST_Contains untuk menemukan titik lintasan dalam poligon spasial dan jendela waktu. Kueri berikut mengembalikan semua titik di dalam POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24)) yang direkam antara pukul 08.21 dan 08.23 pada 1 Januari 2021:

SELECT id, t, ST_AsText(g), ship_name
FROM gps_data
WHERE ST_Contains(ST_GeomFromText('POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24))'), g)
  AND t > '2021-01-01 08:21:00'
  AND t < '2021-01-01 08:23:00';

Catatan

Karena indeks Z-ORDER mencakup g dan t, kueri harus menyertakan kondisi rentang pada kedua kolom agar dapat menggunakan indeks. Lihat Performance tuning for spatio-temporal queries.

Output yang diharapkan:

+----+-------------------------------+------------------------+-----------+
| id |               t               |     "ST_AsText"(g)     | ship_name |
+----+-------------------------------+------------------------+-----------+
| 2  | 2021-01-01 08:22:20 +0000 UTC | POINT (19.07352 25.33) | ship002   |
+----+-------------------------------+------------------------+-----------+

Hanya ship002 yang muncul dalam hasil. Titik ship001 berada di sekitar bujur 119°, yang berada di luar poligon kueri (rentang x 18–20). Titik ship002 berada di sekitar bujur 19°, yang berada di dalam poligon—tetapi hanya pembacaan pukul 08.22 yang berada dalam jendela waktu 08.21–08.23. Pembacaan pukul 08.20 dikecualikan karena berada sebelum pukul 08.21.

Fungsi spatio-temporal yang digunakan dalam panduan ini

Fungsi	Signature	Deskripsi
`ST_MakePoint`	`ST_MakePoint(longitude, latitude)`	Membuat `geometry(point)` dari nilai koordinat. Kinerja penulisan lebih tinggi dibanding `ST_GeomFromText`. Lihat ST_MakePoint.
`ST_GeomFromText`	`ST_GeomFromText(wkt_string)`	Membuat geometri dari string Well-known Text (WKT). Lihat ST_GeomFromText.
`ST_AsText`	`ST_AsText(geometry)`	Mengonversi nilai geometri ke string WKT yang mudah dibaca (misalnya, `POINT (119.07 25.32)`).
`ST_Contains`	`ST_Contains(geometry A, geometry B)`	Mengembalikan true jika geometri A secara spasial memuat geometri B. Digunakan untuk pemfilteran spasial berbasis poligon.

Untuk referensi fungsi lengkap, lihat Introduction to spatio-temporal functions.

Langkah selanjutnya

Create a spatio-temporal index — pelajari opsi kunci primer dan indeks spatio-temporal sekunder
Performance tuning for spatio-temporal queries — optimalkan kinerja kueri untuk beban kerja produksi
Introduction to spatio-temporal functions — jelajahi seluruh pustaka fungsi SQL GanosBase