如何將採集的軌跡點寫入Lindorm寬表中並進行快速查詢 - Lindorm

本文以車連網軌跡查詢情境為例，通過Lindorm GanosBase SQL將採集的軌跡點寫入Lindorm寬表中並根據時空範圍進行快速查詢。

前提條件

已安裝Java環境，要求安裝JDK 1.8及以上版本。
已擷取Lindorm寬表SQL的串連地址並配置白名單，具體操作，請參見訪問執行個體。

操作流程

從建立時空資料表到根據時空範圍查詢軌跡，您需要完成以下操作：

建立時空資料表並寫入資料
建立時空索引提高查詢效率
查詢指定時空範圍的軌跡點資料

建立時空資料表並寫入資料

通過Lindorm-cli串連寬表引擎並寫入資料

串連Lindorm寬表引擎。以通過Lindorm-cli串連寬表引擎為例（用戶端部署在Linux）。

如果您需要使用JDBC串連方式，請參見通過Lindorm寬表SQL Java API串連並使用寬表引擎。

下載Lindorm-cli。
解壓Lindorm-cli壓縮包。

擷取串連資訊並執行下述命令串連Lindorm寬表。

./lindorm-cli -url <jdbc url> -username <使用者名稱> -password <密碼>

參數	樣本值	擷取方法
jdbc url	jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060	Lindorm寬表SQL的串連地址，具體擷取方法請參見訪問執行個體。
使用者名稱	root	您可以通過Lindorm叢集管理系統查看使用者名稱，如果您忘記對應的密碼，可以在叢集管理系統中修改密碼，具體操作請參見修改使用者密碼。
密碼	root

返回如下結果：

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

通過以下兩種方式建立時空資料表。

時空資料表是用來儲存軌跡點的。軌跡點包括經度x、緯度y和時間t三類資訊，其中x和y資料在Lindorm GanosBase中有兩種儲存方式。儲存方式和特點如下：

儲存方式	特點
使用空間資料類型`Geometry(Point)`將x和y資料打包儲存在一列。	高效能。
使用普通點座標的方式將x和y資料分別儲存在兩列。	歷史資料以x、y兩列分開儲存，但效能會有損失。

使用空間資料類型Geometry(Point)將x和y資料打包儲存在一列。建立時空資料表語句如下：

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

參數	說明
g	空間列，空間資料類型為`Geometry(Point)`。
t	時間列，資料類型支援Time、Timestamp或者Long。如果使用Long類型，時間表示方式為Unix時間戳記（毫秒）。
ship_name	名稱列，例如產生不同時空資料點的船名稱。
PRIMARY KEY(id, t)	PRIMARY KEY由`id`和`t`組成。

使用普通點座標的方式將x、y和t資料分別儲存在三列。建立時空資料表語句如下：

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

通過以下兩種方式單次寫入軌跡點資料。

使用時空函數ST_MakePoint來構造軌跡點資料，例如ST_MakePoint(119.073544,25.3244)表示經度為119.073544，緯度為25.3244的軌跡點。

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，請參見ST_MakePoint。
您也可以使用時空函數ST_GeomFromText來構造軌跡點資料，軌跡點的格式使用WKT（Well-known Text）標準格式。但是通過ST_GeomFromText函數構造資料的寫入效能比ST_MakePoint函數低。有關時空函數ST_GeomFromText，請參見ST_GeomFromText。

使用普通點座標的方式。

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

可選：通過以下兩種方式批量寫入軌跡點資料。

使用時空函數ST_MakePoint的方式。

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

通過SELECT語句查詢已寫入的資料。

使用時空函數ST_AsText將時空資料轉換為使用者可讀的文本描述格式。

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

查詢結果如下：

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

使用普通點座標的方式。

SELECT * FROM gps_data_point;

查詢結果如下：

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

通過Java JDBC串連寬表引擎並寫入資料

與Lindorm SQL寫入其他資料類型一樣，寫入時空類型的資料時同樣支援通過參數化查詢的方式進行綁參寫入。下面是一個以Java JDBC通過PreapredStatement介面的參數化查詢方式寫入時空資料的Java程式碼範例。

// 建立串連
Connection connection = DriverManager.getConnection(url, properties);
final String tableName = "testtbl"
// 建表
try (Statement stmt = conn.createStatement()) {
    stmt.execute("create table " + tableName +
        "(p1 int, c1 varchar, c2 geometry(point), constraint primary key (p1))");
}

// 寫入的參數化查詢語句
final String upsertSql = "upsert into " + tableName + "(p1,c1,c2) values (?,?,ST_MakePoint(?,?))";

// 準備語句
try (PreparedStatement preparedStatement = conn.prepareStatement(upsertSql)) {
  // 與參數預留位置一一對應地綁定參數
  preparedStatement.setInt(1, 0);
  preparedStatement.setString(2, "name");
  preparedStatement.setDouble(3, 5.0);
  preparedStatement.setDouble(4, 5.0);
  // 執行寫入
  preparedStatement.executeUpdate();
}

建立時空索引提高查詢效率

當查詢語句的WHERE查詢條件中包含時空範圍時，需要建立時空索引來加速查詢。時空索引分為時空主鍵索引和時空二級索引，分別對應Lindorm的主鍵索引和二級索引，具體請參見建立時空索引。

設定時空資料表的屬性。
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_LATEST';
ALTER TABLE gps_data SET 'CONSISTENCY'='strong';
```
說明
如果使用自訂時間戳記（可以在任意時間戳記進行資料更新）的索引，需要將MUTABILITY屬性設定為MUTABLE_ALL，請執行ALTER TABLE gps_data SET 'MUTABILITY' = 'MUTABLE_ALL';。有關Mutability的分類請參見基本概念。
建立時空二級索引。本樣本已經建立了時空資料表，所以只能通過建立時空二級索引來加速資料查詢。以下樣本是對空間列和時間列進行索引。
```
CREATE INDEX idt ON gps_data (Z-ORDER(g,t));
```

查詢指定時空範圍的軌跡點資料

使用時空函數ST_Contains查詢空間範圍為POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24))且時間範圍為2021年01月01號08點21分~2021年01月01號08點23分的軌跡點資料。

說明

由於時空二級索引中的Z-ORDER函數包含了g列和t列，所以查詢條件中必須同時包含g列和t列的範圍。關於最佳化時空查詢的方法請參見時空查詢的效能調優。

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

查詢結果如下：

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

說明

更多時空函數請參見函數概覽。